Tuesday, December 10, 2019

Renouncing the Symbol: 1940 Resolution by Tribes to Reject the Swastika or Fylfot

Each of the 10 articles or captioned photographs linked below refer to the same event - the signing of a resolution by members of Navajo, Papago, Apache, and Hopi tribes in early 1940.

I share links to these these articles and photographs to illustrate that over time content, art, and craft creators have been very serious about the current as well as historic associations of the symbols they use.

The text of the resolution as displayed by the photographs and articles below:
"Because the above ornament which has been a sign of friendship among our forefathers for many centuries has been desecrated recently by another nation of peoples,
Therefore it is resolved that henceforth from this date on and forever more our tribes renounce the use of the emblem commonly known today as the swastika or fylfot on our blankets, baskets, art objects, sandpaintings and clothing."

An additional note about two of the stories below: numbers 6 and 10 in the list seem to be in denial of these tribes - and the individuals themselves - to act of themselves in accordance with their principles.  I'm especially troubled by the tone of the article in number 6, and hope to write a follow-up post on that particular article in the future. 

1.
Indians Denounce Nazis, Forego Use of Swastika
St. Joseph Gazette, St. Joseph, Missouri
Monday, 26 February 1940
https://www.newspapers.com/clip/36417593/indians_denounce_swastika/

2.
Indian Sign on Swastika
The Akron Beacon Journal, Akron, Ohio
Monday, 4 March 1940
https://www.newspapers.com/clip/36417663/indians_denounce_swastika_2/

3.
The Evening Review, East Liverpool, Ohio
Thursday, 29 February 1940
https://www.newspapers.com/clip/36417770/indians_denounce_swastika_3/

4.
Star-Gazette, Elmira, New York
Thursday, 29 February 1940 
https://www.newspapers.com/clip/36417960/indians_denounce_swastika_4/

5.
Indians Bar Swastika Design as Protest Against Nazis
The Los Angeles Times, Los Angeles, California
Monday, 26 February 1940
https://www.newspapers.com/clip/36418025/indians_denounce_swastika_5/

6.
Arizona Indians Bow to Hitler
The Twin Falls News, Twin Falls, Idaho
8 March 1940 
https://www.newspapers.com/clip/36418064/indians_denounce_swastika_6/

7.
Four Indian Tribes Unite in Order Barring Swastika as Their Symbol
The San Bernardino County Sun, San Bernardino, California
Tuesday, 27 February 1940
https://www.newspapers.com/clip/36418096/indians_denounce_swastika_7/

8.
Indians Denounce Swastika Emblem
Wednesday, 28 February 1940
https://www.newspapers.com/clip/36418167/indians_denounce_swastika_8/

9.
Indians Ban Swastika
The Town Talk, Alexandria, Louisiana
Monday, 1 April 1940
https://www.newspapers.com/clip/36418274/indians_denounce_swastika_9/

10.
Put Indian Sign on Swastika
Times Colonist, Victoria, British Columbia, Canada
Saturday, 6 April 1940 
https://www.newspapers.com/clip/36418535/indians_denounce_swastika_10/

For proper placement in history:
7 December 1941 - United States enters World War II.


In addition to the links above, one may find duplicates appearing in other papers.

This article is a near duplicate of "Indians Denounce Nazis, Forego Use of Swastika" from above.

Indians Renounce Swastika Symbol
Albuquerque Journal, Albuquerque, New Mexico
Monday 26 February 1940
https://www.newspapers.com/clip/13127176/albuquerque_journal/

Tuesday, November 19, 2019

SQL Server 2019 Scalar UDF inlining - OOM in some cases

Here's a little something I stumbled across.  A caution about scalar UDF inlining.

Well, ok, maybe Joe Obbish stumbled across it first :-)

Today's adventure is on my laptop.  Because no lie this ugly UDF combined with current UDF inlining memory consumption will take down your server no matter *how* big it is.

Here's my laptop SQL Server version and some important database details.


OK, let's create a scalar UDF with a few logic branches.  The function is nonsense, I'm sorry.  But if you try this... you can try making it as sensible as you'd like. :-)

In the function below there is one IF, 25 ELSE IFs, and 1 ELSE.


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER  FUNCTION dbo.test__inline_udf
(
  @in_param nvarchar(250)
)
RETURNS nvarchar(250)
AS
BEGIN
  DECLARE @retValue nvarchar(250) = @in_param
  IF @in_param = N'A'
  BEGIN
    IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)  
   BEGIN SET @retValue = N'1' END
  END
  ELSE IF @in_param = N'B'
  BEGIN
    IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17) 
    BEGIN SET @retValue = N'2' END
  END
  ELSE IF @in_param = N'C'
  BEGIN
    IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
       BEGIN SET @retValue = N'3' END
  END
  ELSE IF @in_param = N'D'
  BEGIN
    IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
    BEGIN SET @retValue = N'4' END
  END 
  ELSE IF @in_param = N'E'
  BEGIN
    IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
       BEGIN SET @retValue = N'5' END
  END
  ELSE IF @in_param = N'F'
  BEGIN
    IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
       BEGIN SET @retValue = N'6' END
  END
  ELSE IF @in_param = N'G'
  BEGIN
    IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
       BEGIN SET @retValue = N'7' END
  END
  ELSE IF @in_param = N'H'
  BEGIN
    IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
       BEGIN SET @retValue = N'8' END
  END
  ELSE IF @in_param = N'I'
  BEGIN
    IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
       BEGIN SET @retValue = N'9' END
  END
  ELSE IF @in_param = N'J'
  BEGIN
    IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
       BEGIN SET @retValue = N'10' END
  END
  ELSE IF @in_param = N'K'
  BEGIN
    IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
       BEGIN SET @retValue = N'11' END
  END
  ELSE IF @in_param = N'L'
  BEGIN
    IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
       BEGIN SET @retValue = N'12' END
  END
  ELSE IF @in_param = N'M'
  BEGIN
    IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
       BEGIN SET @retValue = N'13' END
  END
  ELSE IF @in_param = N'N'
  BEGIN
    IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
       BEGIN SET @retValue = N'14' END
  END
  ELSE IF @in_param = N'O'
  BEGIN
    IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
       BEGIN SET @retValue = N'15' END
  END
  ELSE IF @in_param = N'P'
  BEGIN
    IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
       BEGIN SET @retValue = N'16' END
  END
  ELSE IF @in_param = N'Q'
  BEGIN
    IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
       BEGIN SET @retValue = N'17' END
  END
  ELSE IF @in_param = N'R'
  BEGIN
    IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
       BEGIN SET @retValue = N'18' END
  END
  ELSE IF @in_param = N'S'
  BEGIN
    IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
       BEGIN SET @retValue = N'19' END
  END
  ELSE IF @in_param = N'T'
  BEGIN
    IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
       BEGIN SET @retValue = N'20' END
  END
  ELSE IF @in_param = N'U'
  BEGIN
    IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
       BEGIN SET @retValue = N'21' END
  END 
  ELSE IF @in_param = N'V'
  BEGIN
    IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
       BEGIN SET @retValue = N'22' END
  END
  ELSE IF @in_param = N'W'
  BEGIN
    IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
       BEGIN SET @retValue = N'23' END
  END
  ELSE IF @in_param = N'X'
  BEGIN
    IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
       BEGIN SET @retValue = N'24' END
  END
  ELSE IF @in_param = N'Y'
  BEGIN
    IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
       BEGIN SET @retValue = N'25' END
  END
  ELSE IF @in_param = N'Z'
  BEGIN
    IF @retValue NOT IN ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
       BEGIN SET @retValue = N'26' END
  END
  ELSE
  BEGIN SET @retValue = N'00' END
  RETURN @retValue
END


On my laptop, the following result took from 8 to 10 seconds repeatedly.



I'll tell you what.  I ran this same function on a VM with 930 GB vRAM, with Max Server Memory set to 690GB.  It ran for over 15 minutes before crashing.  Not just crashing itself.  Crashing any other query on the instance that was trying to allocate memory (eg stealing against a query memory grant).  It had amassed over 500 GB of optimizer memory at that point, like this...


Wow.  Now you'll notice that the big ol' server is a slightly different version.  No matter.  As far as I know, this behavior will be found on every version of  SQL Server 2019 up to date (today is 2019 November 19).

Once the instance reached the total limit for "stealable" memory, the query crashed.  Same thing if an estimated plan was requested - so its in compilation rather than execution that the aggressive memory consumption occurs.  Once the OOM occurs, the large amount of optimizer memory is freed within a few seconds and the instance is back to normal for all other purposes.

Now, if I disable UDF inlining... the following result comes in well under 1 second.


Here's the final thing I can say about this for now...
If you generate an estimated plan for a query that tries to inline that UDF, it'll also crash due to excessive optimizer memory*.

I'll update this blog post in the future when a fix is available.


* well, I speculate that there is some amount of memory which may be sufficient to allow this to complete with generating an OOM.  But once it's more than 500 GB does it really matter?


~~~~~

The scalar UDF used above is really, really bad :-)

So here's a nicer one.


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER  FUNCTION dbo.test__inline_udf_2
(
  @in_param INT
)
RETURNS INT
AS
BEGIN
  DECLARE @retValue INT = @in_param
  IF @in_param = 1
  BEGIN
    IF @retValue IN 
  ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16,
   17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32)
  SET @retValue = 1
  END
  ELSE IF @in_param = 2
  BEGIN
    IF @retValue IN 
  ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16,
   17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32)
   SET @retValue = 2
   END
   ELSE IF @in_param = 3
  BEGIN
    IF @retValue IN 
  ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16,
   17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32)
   SET @retValue = 3
   END
   ELSE IF @in_param = 4
   BEGIN
     IF @retValue IN 
  ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16,
   17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32)
   SET @retValue = 4
   END
   ELSE IF @in_param = 5
   BEGIN
     IF @retValue IN 
  ( 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16,
   17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32)
   SET @retValue = 5
  END
  ELSE SET @retValue = 0
  RETURN @retValue
END

No more implicit conversions.  Took out some unnecessary BEGIN-END pairs.  Its only one IF, four ELSE IFs, and an ELSE.  Changed the NOT IN clauses to IN clauses.

And when i run it on my monster VM, it also generates an error.


That looks even more severe that the previous error.  But its the same underlying condition: working on the inlining of the scalar UDF during plan compile kept gobbling optimizer memory until something gave way.

Msg 701, Level 17, State 123, Line 6
There is insufficient system memory in resource pool 'default' to run this query.
Location:
Expression: false
SPID: 61
Process ID: 4916
Msg 3624, Level 20, State 1, Line 1
A system assertion check has failed.
Msg 596, Level 21, State 1, Line 0
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.
Froid MEMORY_CLERK_SQLOPTIMIZER

Monday, September 30, 2019

Observing the [DBCC CHECKDB] parallel object check

This is a query i use to keep tabs on all of the workers when i run [dbcc checkdb] at high DOP :-)

Note: this query won't catch the initial portions of checkdb that always run at DOP 1 - the checkalloc, checkcatalog, etc.


;WITH owt AS
(SELECT exec_context_id, wait_type, 
        MAX(wait_duration_ms) wait_duration_ms
 FROM sys.dm_os_waiting_tasks owt 
 GROUP BY exec_context_id, wait_type)
SELECT capture_tm = getdate(), owt.wait_type, count(*) waiters, 
       MIN(owt.wait_duration_ms) min_wait_ms, 
       MAX(owt.wait_duration_ms) max_wait_ms
FROM sys.dm_exec_requests req
JOIN sys.dm_os_tasks ot ON req.session_id = ot.session_id
LEFT OUTER JOIN owt
     ON ot.session_id = owt.session_id AND ot.exec_context_id = owt.exec_context_id
WHERE req.command IN ('dbcc table check') 
GROUP BY owt.wait_type
ORDER BY waiters DESC;

q

Thursday, September 19, 2019

SQL Server Unequal NUMA CPU engagement

With physical servers using NUMA configuration - or vms using vNUMA - a number of factors can lead to uneven CPU utilization among the NUMA nodes.

Consider a 2x8 vm, with SQL Server maxdop set at 8. Assume a single maxdop 8 parallel query running on that system. The parallel workers will most likely be sitting on 8 user schedulers. If that parallel query has multiple zones, the parallel workers will still most likely be on 8 user schedulers, with as many parallel workers for the query stacked on each of those schedulers as there are zones in the plan.

All of the schedulers hosting parallel workers for that query may be co-resident with each other in vNUMA node 0. Or 1. Or they may be spread across them in some manner.

The "single query with maxdop less than scheduler count" scenario gives a good starting point for understanding how CPU engagement of the NUMA nodes may be different at any given time.

(Location of context ID 0 for the session adds another detail to this scenario. It may be co-resident on a scheduler with some number of parallel workers for the query, or it may not. For example, if all parallel workers are in vNUMA node 0 and context ID 0 is also in vNUMA node 0 in this example, context ID 0 is co-resident on a scheduler with at least one parallel worker for the query.)

Imbalances can even arise if all queries are maxdop 1. SQL Server distributed incoming connections to an end-point among all nodes available to that end-point with a round-robin scheme. But! Due to use of ADO.NET connection pooling and a pre-existing pool, two successive connections from a client - in the same pool - may very well end up on the same node. The fairly fast ramp up of pools to their maximum size (by default 100) and fairly slow ramp down (documentation says an idle pool connection will be disposed after 4 to 8 minutes of idle time, down to minimum pool size) can lead to some unexpected distributions of connections.

Now, its even possible that an equal number of active workers are on each scheduler of each node, but the vNUMA nodes *still* show unequal CPU engagement. I'll just mention 1 query-based possibility, and one system state-based possibility.

What if the queries on node 1 are optimized CCI-based queries, while through some stroke of luck open to the author of hypothetical situations the queries on node 0 are heavily read-dependent rowstore queries?

The distribution of pageio_latch waits would lean toward Node 0 due to the operation pattern there(especially if readahead for some reason or other isn't engaged or is still so slow it produces waits). And those waits could suppress CPU engagement on Node 0, while the same level of effect would not impede engagement on node 1.

Now let's talk how system state could result in uneven CPU engagement across the vNUMA nodes, even if work is evenly distributed.
Various memory states can result in that pattern.
What if another application is running on the same VM? If its memory primarily comes from NUMA node 0, its memory management can come into conflict with SQL Server, which will try to balance its memory utilization across the NUMA nodes.

Perfmon counters [\NUMA Node Memory(*)\Free & Zero Page List MBytes] can be good to peruse if this is suspected. If only one of 2 nodes is flirting with bottoming out Free and Zero Page List memory, it can disproportionately suffer from memory stalls and memory-related SQL Server waits, as well as potentially suffering from content send to and retrieve from pagefile.sys.

OK, enough speculation from me. :-)

Here's a stored procedure that, if run in a fairly short interval like every 10 seconds, can give insight into uneven CPU utilization on a NUMA server based on information from within SQL Server.


CREATE PROCEDURE dbo.SASQ_GRAB
AS
IF object_id ('dbo.SASQ_SCHEDULER_TASKS') IS NULL
BEGIN
    CREATE TABLE dbo.SASQ_SCHEDULER_TASKS (
     capture_time DATETIME NOT NULL,
     scheduler_id INT NOT NULL,
     session_id SMALLINT NULL,
     wait_type NVARCHAR(60) NULL,
     task_count SMALLINT NULL
    );

    CREATE CLUSTERED INDEX CI ON dbo.SASQ_SCHEDULER_TASKS (capture_time) 
    WITH (data_compression = row);
END

IF object_id ('dbo.SASQ_NODE_CONNS') IS NULL
BEGIN
    CREATE TABLE dbo.SASQ_NODE_CONNS (
     capture_time DATETIME NOT NULL,
     node_affinity TINYINT NOT NULL,
     client_net_address VARCHAR(50) NULL,
     conn_count SMALLINT NULL
    );

    CREATE CLUSTERED INDEX CI ON dbo.SASQ_NODE_CONNS (capture_time)
    WITH (data_compression = row);
END

IF object_id ('dbo.SASQ_WAIT_STATS') IS NULL
BEGIN
    CREATE TABLE dbo.SASQ_WAIT_STATS (
     capture_time        DATETIME NOT NULL,
     wait_type           NVARCHAR(60) NOT NULL,
     waiting_tasks_count BIGINT NOT NULL,
     wait_time_ms        BIGINT NOT NULL,
     max_wait_time_ms    BIGINT NOT NULL,
     signal_wait_time_ms BIGINT NOT NULL
    );

    CREATE CLUSTERED INDEX CI ON dbo.SASQ_WAIT_STATS (capture_time)
    WITH (data_compression=row);
END

DECLARE @ct DATETIME;
SET @ct = GETDATE();

INSERT INTO dbo.SASQ_SCHEDULER_TASKS WITH (TABLOCK)
SELECT @ct, ot.scheduler_id, ot.session_id, owt.wait_type, task_count = count(*)
FROM sys.dm_os_tasks ot with (nolock)
LEFT OUTER JOIN sys.dm_os_waiting_tasks owt with (nolock) ON ot.task_address = owt.waiting_task_address
WHERE scheduler_ID < 1048576
GROUP BY ot.scheduler_id, ot.session_id, owt.wait_type
ORDER BY ot.scheduler_id ASC
OPTION (MAXDOP 1);

INSERT INTO dbo.SASQ_NODE_CONNS with (tablock)
SELECT @ct, node_affinity, client_net_address, conn_count = count(*) 
FROM SYS.dm_exec_connections with (nolock)
GROUP BY node_affinity, client_net_address
OPTION (MAXDOP 1);


INSERT INTO dbo.SASQ_WAIT_STATS with (tablock)
SELECT capture_time = GETDATE(), wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats ws WITH (NOLOCK)
WHERE waiting_tasks_count <> 0
OPTION (MAXDOP 1);

/* 20190919
   grab some info from DMOs in iterations of eg 10s to understand
   discrepancy of cpu engagement between NUMA nodes
*/

Friday, September 13, 2019

SQL Server NUMA Memory

.

;WITH tgt AS (SELECT instance_name, cntr_value
              FROM SYS.DM_OS_PERFORMANCE_COUNTERS
              WHERE COUNTER_NAME = 'Target Node Memory (KB)'
     UNION ALL
     SELECT 'TOTAL', cntr_value
              FROM SYS.DM_OS_PERFORMANCE_COUNTERS
              WHERE COUNTER_NAME = 'Target Server Memory (KB)'),
tot AS (SELECT instance_name, cntr_value
              FROM SYS.DM_OS_PERFORMANCE_COUNTERS
              WHERE COUNTER_NAME = 'Total Node Memory (KB)'
     UNION ALL
     SELECT 'TOTAL', cntr_value
              FROM SYS.DM_OS_PERFORMANCE_COUNTERS
              WHERE COUNTER_NAME = 'Total Server Memory (KB)'),
dbc AS (SELECT instance_name, cntr_value
              FROM SYS.DM_OS_PERFORMANCE_COUNTERS
              WHERE COUNTER_NAME = 'Database Node Memory (KB)'
     UNION ALL
     SELECT 'TOTAL', cntr_value
              FROM SYS.DM_OS_PERFORMANCE_COUNTERS
              WHERE COUNTER_NAME = 'Database Cache Memory (KB)'),
stl AS (SELECT instance_name, cntr_value
              FROM SYS.DM_OS_PERFORMANCE_COUNTERS
              WHERE COUNTER_NAME = 'Stolen Node Memory (KB)'
     UNION ALL
     SELECT 'TOTAL', cntr_value
              FROM SYS.DM_OS_PERFORMANCE_COUNTERS
              WHERE COUNTER_NAME = 'Stolen Server Memory (KB)'),
fre AS (SELECT instance_name, cntr_value
              FROM SYS.DM_OS_PERFORMANCE_COUNTERS
              WHERE COUNTER_NAME = 'Free Node Memory (KB)'
     UNION ALL
     SELECT 'TOTAL', cntr_value
              FROM SYS.DM_OS_PERFORMANCE_COUNTERS
              WHERE COUNTER_NAME = 'Free Memory (KB)'),
frn AS (SELECT instance_name, cntr_value
              FROM SYS.DM_OS_PERFORMANCE_COUNTERS
              WHERE COUNTER_NAME = 'Foreign Node Memory (KB)'
     UNION ALL
     SELECT 'TOTAL', cntr_value = CONVERT(INT, NULL))
SELECT tgt.instance_name, target_kb =  tgt.cntr_value,
       total_kb = tot.cntr_value, dbCache_kb = dbc.cntr_value,
    stolen_kb = stl.cntr_value, free_kb = fre.cntr_value,
       foreign_kb = frn.cntr_value
FROM tgt 
JOIN tot ON tgt.instance_name = tot.instance_name
JOIN frn ON tgt.instance_name = frn.instance_name
JOIN dbc ON tgt.instance_name = dbc.instance_name
JOIN stl ON tgt.instance_name = stl.instance_name
JOIN fre ON tgt.instance_name = fre.instance_name;


q

Tuesday, September 10, 2019

T-SQL Tuesday 118; My Fantasy SQL Server Feature

T-SQL Tuesday #118

This month the T-SQL Tuesday is hosted by Kevin Chant (b | t).
The topic: My Fantasy SQL Server Feature.

Here's my wish: meta-data only merges for populated CCI partitions when the partition scheme fully aligns.

Imagine a table - CCI only with no nonclustered indexes, no PK - into which millions of rows are inserted every day.  The partition column is a datetime corresponding to the day the row was inserted.

It may make good sense to partition daily.  But as the data ages, there's likely to be a time when that partition size may as well be monthly. And after 2 years, might as well have yearly partitions.

Rather than physically moving data into newly defined larger partitions, I'd rather a meta-data operation to merge the 28-31 daily partitions for an old month into a monthly partition.  And a metadata only operation to merge 12 older monthly partitions into an yearly partition.

The older the data gets, the more time there's been to prune the rowgroups.  A meta-data only operation to combine the daily rowgroup partitions into a monthly partition reduces the number of partitions *and* keeps partition elimination on the datetime partitioning column at an equivalent level to what partition elimination was before.

Whew.  My shortest blog post in quite a while, I think. 😀 


Monday, September 9, 2019

Decoding sys.dm_os_waiting_tasks.resource_description column info for lock waiters

I've been looking at some locking and blocking scenarios lately, and wanted to do some real-time investigation as well as some logging over time.

Have you seen Erland Sommarskog's beta_lockinfo?  It's pretty nice.  But it didn't quite fit my needs.
http://www.sommarskog.se/sqlutil/beta_lockinfo.html

I went with this instead.  With each iteration, grab just the lock waiters from dm_os_waiting_tasks - and just those with waits longer than 1000 ms.  Convert resource_description to XML, and decode the attributes so I can potentially manipulate it.

The REPLACE occurrences below are to correct for formatting issues in the resource_description text which keep it from converting to conforming XML.  Hope I got them all.


;WITH lock_waiters AS
(SELECT waiting_task_address, session_id, exec_context_id, wait_duration_ms, wait_type, 
        resource_address, blocking_task_address, blocking_session_id, 
        blocking_exec_context_id, resource_description
 FROM sys.dm_os_waiting_tasks owt 
 WHERE owt.wait_type LIKE 'LCK%' AND wait_duration_ms > 1000)
SELECT track.session_id, track.blocking_session_id, track.wait_duration_ms,
       x1.[type], x1.mode, x1.associatedObjectId, x1.[dbid], x1.subresource, x1.lockPartition, 
       x1.fileid, x1.pageid, x1.hobtid, x1.[hash], x1.databasePrincipalId, x1.classid, x1.[objid], x1.id
FROM lock_waiters AS track
CROSS APPLY (SELECT info = CONVERT(XML, '<lockinfo type="' + REPLACE(REPLACE(REPLACE(REPLACE(resource_description,', ',','), ' = ','|'), ' ', '" '), '=', '="') + '"/>')) XML_table
CROSS APPLY
     (SELECT [type] = XML_table.info.value(N'(/lockinfo/@type)[1]', N'varchar(50)'),
             mode = XML_table.info.value(N'(/lockinfo/@mode)[1]', N'varchar(20)'),
             associatedObjectId = XML_table.info.value(N'(/lockinfo/@associatedObjectId)[1]', N'varchar(20)'),
             [dbid] = XML_table.info.value(N'(/lockinfo/@dbid)[1]', N'varchar(20)'),
             subresource = XML_table.info.value(N'(/lockinfo/@subresource)[1]', N'varchar(20)'),
             lockpartition = XML_table.info.value(N'(/lockinfo/@lockPartition)[1]', N'varchar(20)'),
             fileid = XML_table.info.value(N'(/lockinfo/@fileid)[1]', N'varchar(20)'),
             pageid = XML_table.info.value(N'(/lockinfo/@pageid)[1]', N'varchar(20)'),
             hobtid = XML_table.info.value(N'(/lockinfo/@hobtid)[1]', N'varchar(20)'),
             [hash] = XML_table.info.value(N'(/lockinfo/@hash)[1]', N'varchar(20)'),
             databasePrincipalId = XML_table.info.value(N'(/lockinfo/@databasePrincipalId)[1]', N'varchar(20)'),
             classid = XML_table.info.value(N'(/lockinfo/@classid)[1]', N'varchar(20)'),
             id = XML_table.info.value(N'(/lockinfo/@id)[1]', N'varchar(20)'),
             [objid] = XML_table.info.value(N'(/lockinfo/@objid)[1]', N'varchar(20)')) x1;

Here's the BOL link for SQL Server 2017  sys.dm_os_waiting_tasks.
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-waiting-tasks-transact-sql?view=sql-server-2017