Monday, June 24, 2019

SQL Server 2019 CTP 3.0 plus P startup option (for experimentation only)

*****
I'll stress that I use the -P<n> startup option for brief, targeted experimentation and observation only, on my laptop or in test instances.  I've never run a perf/scale workload test against an instance with scheduler count modified this way, and I don't plan to do so.  I'll plead with you instead to never let this startup option get anywhere near a production SQL Server instance, or an instance with workloads of any import. My stance and strategy is currently that its better to limit the "instruction entrance liability" of any one physical core and instead increase instruction intensity by tuning the workload and queries.
***** 
 
The undocumented SQL Server -P<n> startup option (/P<n> if used in NET START command) instructs SQL Server to create <n> schedulers.  Until I tried it and started poking around, I thought the SQLOS schedulers would just be stacked on top of otherwise-existing SQLOS cpu, node, memory node, and processor group structures.  I expected that on my laptop I'd be restricted to a maximum of 64 schedulers since at the Windows OS level my laptop has a single processor group.  My main goals with these tests were to probe autosoftNUMA behavior at different scheduler counts-per-node and transaction log writer assignment at different node counts.

I learned a few things along the way. 😂😂😂

First, I created two tables in the [master] database.


USE MASTER;
GO

CREATE TABLE system_details
(sqlserver_start_time        DATETIME,
 scheduler_count             INT,
 cpu_count                   INT,
 socket_count                INT,
 numa_node_count             INT,
 softnuma_configuration_desc NVARCHAR(50),
 lgwr_count                  INT);
GO

CREATE TABLE node_details
(sqlserver_start_time   DATETIME,
 node_id                INT,
 node_state_desc        NVARCHAR(50),
 memory_node_id         INT,
 processor_group        INT,
 online_scheduler_count INT);
GO

Next, a stored procedure in the [master] database.

USE MASTER;
GO

CREATE OR ALTER PROCEDURE startup_sys_details
AS

;WITH txwr AS (SELECT lgwr_count = COUNT(*)  
               FROM sys.dm_exec_requests req  
               WHERE req.command = 'LOG WRITER')
INSERT INTO system_details
SELECT osi.sqlserver_start_time, osi.scheduler_count, osi.cpu_count,
osi.socket_count, osi.numa_node_count, osi.softnuma_configuration_desc,
txwr.lgwr_count 
FROM sys.dm_os_sys_info osi
CROSS JOIN txwr;

INSERT INTO node_details
SELECT osi.sqlserver_start_time, osn.node_id, osn.node_state_desc, 
osn.memory_node_id, osn.processor_group, osn.online_scheduler_count
FROM sys.dm_os_nodes osn
CROSS JOIN sys.dm_os_sys_info osi
WHERE osn.node_id < 64;
GO

Then, let's assign that stored procedure as an auto-run startup stored procedure.

USE MASTER;
GO
EXEC sp_procoption 'startup_sys_details', 'STARTUP', 'ON';
GO

Add a .bat script with NET STOP and NET START commands for the SQL Server service, plus a sqlcmd command to turn the auto-start stored procedure back off after the desired tests, and all the pieces are available.


NET STOP MSSQL$CTP_3_0__2019
NET START MSSQL$CTP_3_0__2019
NET STOP MSSQL$CTP_3_0__2019
NET START MSSQL$CTP_3_0__2019 /P8
NET STOP MSSQL$CTP_3_0__2019
NET START MSSQL$CTP_3_0__2019 /P16
NET STOP MSSQL$CTP_3_0__2019
NET START MSSQL$CTP_3_0__2019 /P24
NET STOP MSSQL$CTP_3_0__2019
NET START MSSQL$CTP_3_0__2019 /P28
NET STOP MSSQL$CTP_3_0__2019
NET START MSSQL$CTP_3_0__2019 /P32
NET STOP MSSQL$CTP_3_0__2019
NET START MSSQL$CTP_3_0__2019 /P36
NET STOP MSSQL$CTP_3_0__2019
NET START MSSQL$CTP_3_0__2019 /P40
NET STOP MSSQL$CTP_3_0__2019
NET START MSSQL$CTP_3_0__2019 /P44
NET STOP MSSQL$CTP_3_0__2019
NET START MSSQL$CTP_3_0__2019 /P48
NET STOP MSSQL$CTP_3_0__2019
NET START MSSQL$CTP_3_0__2019 /P56
NET STOP MSSQL$CTP_3_0__2019
NET START MSSQL$CTP_3_0__2019 /P62
NET STOP MSSQL$CTP_3_0__2019
NET START MSSQL$CTP_3_0__2019 /P64
NET STOP MSSQL$CTP_3_0__2019
NET START MSSQL$CTP_3_0__2019 /P72
NET STOP MSSQL$CTP_3_0__2019
NET START MSSQL$CTP_3_0__2019 /P80
sqlcmd -S localhost\CTP_3_0__2019 -d master -Q "EXEC sp_procoption 'startup_sys_details', 'STARTUP', 'OFF';"
NET STOP MSSQL$CTP_3_0__2019
NET START MSSQL$CTP_3_0__2019


So, execute my batch script.  Then let's see the results.


SELECT start_number = ROW_NUMBER() OVER (ORDER BY sd.sqlserver_start_time), sd.* 
FROM system_details sd
ORDER BY sd.sqlserver_start_time;


I've highlighted some minor surprises above in red.  First, at 16 schedulers I expected auto-softNUMA to be active, but it wasn't.  The other two red boxes highlight scenarios with fewer than the 8 transaction log writers I expected.  I don't plan to investigate these percularities at this time.


;WITH grp AS
(SELECT start_number = ROW_NUMBER() OVER (ORDER BY nd.sqlserver_start_time), nd.sqlserver_start_time
FROM node_details nd
GROUP BY nd.sqlserver_start_time)
SELECT grp.start_number, grp.sqlserver_start_time, nd.processor_group, nd.node_state_desc,
       node_count = COUNT(DISTINCT nd.node_id), nd.online_scheduler_count
FROM node_details nd
JOIN grp ON nd.sqlserver_start_time = grp.sqlserver_start_time
GROUP BY grp.start_number, grp.sqlserver_start_time, 
         nd.processor_group, nd.node_state_desc, nd.online_scheduler_count
ORDER BY grp.start_number, grp.sqlserver_start_time,
         nd.processor_group, nd.node_state_desc;



Not as much surprise in the node details in this testing.  It may be surprising that in order to accomodate the request for more than 64 schedulers, SQLOS created another processor group structure to hold the schedulers even though the OS still has a single processor group.  Perhaps its also surprising that while the Windows OS now tries to balance logical CPU count in processor groups, SQLOS is content to keep processor groups "node-aligned" but uneven.

Because my intent in these tests was to probe transaction log writer count and auto softNUMA behavior, that's as far as I need to go with this.  I don't expect to ever increase the scheduler count this way, so in my configurations processor groups should always be following the OS processor groups (unless I decide to do some future experiements similar to these.)

*If* I see a VM or physical server with SQL Server 2019 that has fewer transaction log writers than twice the soft\NUMA node count (up to 8), I'll investigate at that time.

That's all for now!

Ciao!!

Q

Saturday, June 22, 2019

SQL Server 2019 CTP 3.0 - max number of transaction log writers increased

In SQL Server 2016, transaction log writing was enhanced to support multiple transaction log writers.  If the instance had more than one non-DAC node in [sys].[dm_os_nodes], there would be one transaction log writer per node, to a maximum of 4.

In SQL Server 2019, it seems the maximum number of transaction log writers has been increased.  The system below with 4 vNUMA nodes (and autosoftNUMA disabled) has eight transaction log writer sessions, each on their own hidden online scheduler, all on parent_node_id = 3/memory_node_id = 3 on processor group 1.


;with kgroups AS
(SELECT kgroup_count = COUNT(DISTINCT processor_group) 
 FROM sys.dm_os_nodes osn) 
SELECT SQLServer_version = SERVERPROPERTY('ProductVersion'), sinfo.scheduler_count, 
       sinfo.cpu_count, sinfo.softnuma_configuration_desc, sinfo.socket_count, 
    sinfo.numa_node_count, kgroups.kgroup_count 
FROM sys.dm_os_sys_info sinfo
CROSS JOIN kgroups;

SELECT req.session_id, req.command, sch.scheduler_id, sched_status = sch.[status], 
       sch.cpu_id, sch.parent_node_id, osn.memory_node_id, osn.processor_group
FROM sys.dm_exec_requests req
JOIN sys.dm_os_schedulers sch ON req.scheduler_id = sch.scheduler_id
JOIN sys.dm_os_nodes osn ON sch.parent_node_id = osn.node_id
WHERE req.command = 'LOG WRITER';



Will this difference matter?  I suspect there is a workload type that can benefit from this increase, otherwise I'd be surprised at the change.  If the horsepower of 8 transaction log writers is needed, then you might also want to know which schedulers share CPU with the transaction log writer hidden schedulers, in order to avoid them with busiest/most important queries.  That can be done, for example, by using affinity at the Resource Governor Resource Pool level.

That's all for now!

Ciao!!

SQL Server Query Store testing tools - USE PLAN Hint derived from plan_id

I use this stored procedure to generate a 'USE PLAN' hint style query, based on the Query Store plan_id supplied as an input parameter.



CREATE PROCEDURE useplan_query_from_planid @plan_id BIGINT
AS
DECLARE @amp NVARCHAR(MAX)
SELECT @amp = qsqt.query_sql_text + N' OPTION (
USE PLAN N''' + qsp.query_plan + ''')'
FROM sys.query_store_plan qsp
JOIN sys.query_store_query qsq ON qsp.query_id = qsq.query_id
JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
WHERE qsp.plan_id = @plan_id
IF @amp IS NOT NULL EXEC (@amp)
/* 
   20190622
   input parameter: 

   @plan_id
   - make query text from Query Store query_sql_text for @plan_id
   - add planXML in use plan hint
   - execute the query

   assumptions:
   query_sql_text does not already contain OPTION() hints

   expected outcomes:
   the relevant query will use a Query Store plan_id
      other than the one specified by the plan_id input parameter.
      it must do so because the query text has changed
   resulting in a different query_text_id 
      and a different query_id
*/



Q

Monday, June 17, 2019

SQL Server Query Store: Usability Soft Spots -- Part 3

Others in this series...

SQL Server Query Store: Usability Soft Spots -- Part 1
https://sql-sasquatch.blogspot.com/2019/06/sql-server-query-store-usability-soft.html

 SQL Server Query Store: Usability Soft Spots -- Part 2
https://sql-sasquatch.blogspot.com/2019/06/sql-server-query-store-usability-soft_16.html

Summary of this post: comb through the surprise plans, finding an example that looks like the description of "morally equivalent plans"... and one that kinda doesn't.

~~~~~

A reminder of where this set of tests is currently.
In Part 2, the repro code was tweaked a bit, in order to no longer use a tempdb table in a CTAS statement ("SELECT... INTO", Create Table As SELECT) and to TRUNCATE results table between iterations rather than DROP. After 1600 iterations of the test query with some filler queries also executed with each iteration, 19 plan_id values were displayed in Query Store "Tracked Queries".

Attempting to force each one of those plan_id values from Query Store resulted in 2 successful forces and 17 uses of surprise plans!

A grid of all 34 plan_id values is below.  Two interesting things are highlighted.  In the blue box a single row is highlighted, where plan_id 195 produced plan_id 292 when forced.  That's unusual because it's the only example in the list where one of the original 17 plans produced another one of the 17 originals as the "Surprise Plan". The other box highlights two consecutive rows.  Plan_id values 814 and 899 both produced "Surprise Plan" plan_id 3250.



OK, let's see if any of those "surprise plans" resulted in a last_force_failure_reason_desc.


SELECT plan_id, query_id, engine_version, compatibility_level,  
       is_forced_plan, last_force_failure_reason_desc
FROM sys.query_store_plan 
WHERE query_id = 2
AND plan_id < 3220 
AND plan_id NOT IN (292, 501)
ORDER BY plan_id ASC;


Nope, not a single one is listed as a "force failure", even though the plan forced at the time was not used.

Let's compare plan_id 195 and 292.

That's pretty much what we'd expect from a "morally equivalent plan".  Both plans have the same shape.  All joins and plan operators are in the same location of the two graphical plans.




After seeing that plan_id 195 and plan_id 292 have all the same operators in all the same places, why did new estimates appear for several operators, resulting in plan_id 292 being used when plan_id 195 was forced?

Was it due to a stats update?
Because the new CE is used, optimizer stats info is included in plan XML.  Here's OptimizerStatsUsage for plan_id 195.

<OptimizerStatsUsage>
  <StatisticsInfo LastUpdate="2019-06-16T17:14:24.22" ModificationCount="193" SamplingPercent="100" Statistics="[plan_persist_plan_cidx]" Table="[plan_persist_plan]" Schema="[sys]" Database="[surprise_plan]">
  </StatisticsInfo>
  <StatisticsInfo LastUpdate="2019-06-16T17:14:24.23" ModificationCount="189" SamplingPercent="100" Statistics="[plan_persist_query_text_cidx]" Table="[plan_persist_query_text]" Schema="[sys]" Database="[surprise_plan]">
  </StatisticsInfo>
  <StatisticsInfo LastUpdate="2019-06-16T17:14:24.23" ModificationCount="189" SamplingPercent="100" Statistics="[_WA_Sys_00000002_09DE7BCC]" Table="[plan_persist_query_text]" Schema="[sys]" Database="[surprise_plan]">
  </StatisticsInfo>
  <StatisticsInfo LastUpdate="2019-06-16T17:14:24.23" ModificationCount="193" SamplingPercent="100" Statistics="[plan_persist_plan_idx1]" Table="[plan_persist_plan]" Schema="[sys]" Database="[surprise_plan]">
  </StatisticsInfo>
  <StatisticsInfo LastUpdate="2019-06-16T17:14:24.23" ModificationCount="189" SamplingPercent="100" Statistics="[plan_persist_query_idx1]" Table="[plan_persist_query]" Schema="[sys]" Database="[surprise_plan]">
  </StatisticsInfo>
  <StatisticsInfo LastUpdate="2019-06-16T17:14:24.22" ModificationCount="189" SamplingPercent="100" Statistics="[plan_persist_query_cidx]" Table="[plan_persist_query]" Schema="[sys]" Database="[surprise_plan]">
  </StatisticsInfo>
</OptimizerStatsUsage>


And here's OptimizerStatsUsage for plan 292.

<OptimizerStatsUsage>
  <StatisticsInfo LastUpdate="2019-06-16T17:14:24.22" ModificationCount="290" SamplingPercent="100" Statistics="[plan_persist_plan_cidx]" Table="[plan_persist_plan]" Schema="[sys]" Database="[surprise_plan]">
  </StatisticsInfo>
  <StatisticsInfo LastUpdate="2019-06-16T17:14:24.23" ModificationCount="285" SamplingPercent="100" Statistics="[plan_persist_query_text_cidx]" Table="[plan_persist_query_text]" Schema="[sys]" Database="[surprise_plan]">
  </StatisticsInfo>
  <StatisticsInfo LastUpdate="2019-06-16T17:14:24.23" ModificationCount="285" SamplingPercent="100" Statistics="[_WA_Sys_00000002_09DE7BCC]" Table="[plan_persist_query_text]" Schema="[sys]" Database="[surprise_plan]">
  </StatisticsInfo>
  <StatisticsInfo LastUpdate="2019-06-16T17:14:24.23" ModificationCount="290" SamplingPercent="100" Statistics="[plan_persist_plan_idx1]" Table="[plan_persist_plan]" Schema="[sys]" Database="[surprise_plan]">
  </StatisticsInfo>
  <StatisticsInfo LastUpdate="2019-06-16T17:14:24.23" ModificationCount="285" SamplingPercent="100" Statistics="[plan_persist_query_idx1]" Table="[plan_persist_query]" Schema="[sys]" Database="[surprise_plan]">
  </StatisticsInfo>
  <StatisticsInfo LastUpdate="2019-06-16T17:14:24.22" ModificationCount="285" SamplingPercent="100" Statistics="[plan_persist_query_cidx]" Table="[plan_persist_query]" Schema="[sys]" Database="[surprise_plan]">
  </StatisticsInfo>
</OptimizerStatsUsage>


Notice that LastUpdate for each of the six optimizer stats is the same in plan_id 195 and plan_id 292.
Its not a stats update that results in different estimates.  ModificationCount shows a difference, though, with each of the six ModificationCount values higher in plan_id 292 than plan_id 195.

Here's a relevant passage from a blog post by Itzik Ben Gan.


Seek and You Shall Scan Part II: Ascending Keys
https://www.itprotoday.com/sql-server/seek-and-you-shall-scan-part-ii-ascending-keys
  • "The new cardinality estimator identifies that the query filter exceeds the maximum value in the histogram. It has access to the column modification counter (call it in short modctr), so it knows how many changes took place since the last refresh. When the column is unique and of an integer type or numeric with a scale of zero, SQL Server assumes it’s ascending. So it interpolates the estimate based on the distribution of the values within the existing histogram and the number of changes that took place since the last refresh."

*This* definitely seems like the description I've heard of a "morally equivalent plan".  As estimates for various operators changed along with modctr, eventually the previous plan no longer "fit" even though the newer plan still had the same plan shape, with the same plan operators in the same positions.

Let's compare plan_id 814 to plan_id 3250.


All operator in the same spots in the two plans, the difference between them being the estimates.  Its the same when comparing plan_id 899 to plan_id 3250.

But when I look at the planXML for plan_id 3250, I see something extraordinary.  The QueryHash value is the same as the QueryPlanHash value.


QueryHash="0xE91EDF2B4F8EA8B4" QueryPlanHash="0xE91EDF2B4F8EA8B4"

Well.  Let's see how widespread that phenomenon is. First we';ll check the original 19 plan_id values.


;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
planXML AS
(SELECT plan_id, planXML = CONVERT(XML, query_plan)
 FROM sys.query_store_plan WHERE query_id = 2 AND plan_id < 3220)
SELECT plan_id, xml_query_hash = RelOp.pln.value(N'@QueryHash', N'varchar(50)'), 
       xml_plan_hash = RelOp.pln.value(N'@QueryPlanHash', N'varchar(50)'),
       TerminationReason = RelOp.pln.value(N'@StatementOptmEarlyAbortReason', N'varchar(50)')
FROM planXML
CROSS APPLY planXML.planXML.nodes(N'//StmtSimple') RelOp (pln);

Nope, none of them have QueryHash = QueryPlanHash in the plan XML.


What about the 15 surprise plans that only showed up once we started forcing other plans?

;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
planXML AS
(SELECT plan_id, planXML = CONVERT(XML, query_plan)
 FROM sys.query_store_plan WHERE query_id = 2 AND plan_id > 3219)
SELECT plan_id, xml_query_hash = RelOp.pln.value(N'@QueryHash', N'varchar(50)'), 
       xml_plan_hash = RelOp.pln.value(N'@QueryPlanHash', N'varchar(50)'),
       TerminationReason = RelOp.pln.value(N'@StatementOptmEarlyAbortReason', N'varchar(50)')
FROM planXML
CROSS APPLY planXML.planXML.nodes(N'//StmtSimple') RelOp (pln);

Wow.  15 "surprise plans" first showed up after other plans were forced for query_id 2.  Every single one of them has QueryHash equal to QueryPlanHash in the plan XML.


That is... hm.  So even if the forced query plan doesn't fit the estimates, or there's some other "near-miss" reason for avoiding the forced query plan... the QueryHash value in the plan XML should always be the same as [sys].[query_store_query].[query_hash].  Its not in this case.  And strangely QueryHash = QueryPlanHash.

(Microsoft hasn't yet confirmed this kind of QueryHash behavior is a bug... but I honestly can't imagine a design that anticipates or intends this behavior.)


Q

Sunday, June 16, 2019

SQL Server Query Store: Usability Soft Spots -- Part 2

Part 2 Summary: the stored procedure was modified to no longer use "SELECT... INTO" with a table from tempdb, and to truncate the results table with each iteration rather than drop the table.  Of the initial 19 plan_id values for the test query, only 2 would successfully force from Query Store.  After trying to force all of the plans (including the surprise plans which first showed up and were used while another plan was forced), from the 34 total plan_id values, the tally stood at 17 plans which would successfully force, and 17 plans which would not.

Others in the series:
SQL Server Query Store: Usability Soft Spots -- Part 1
https://sql-sasquatch.blogspot.com/2019/06/sql-server-query-store-usability-soft.html

~~~~~

Yesterday I started a blog post series.

SQL Server Query Store: Usability Soft Spots -- Part 1 
https://sql-sasquatch.blogspot.com/2019/06/sql-server-query-store-usability-soft.html

One of my goals for problem repros is to eliminate as many extraneous vulnerable points as possible.  For example, when reproducing a problem with Query Store plan forcing, I want to remove as many known or suspected plan forcing issues as possible, other than the issue the repro is meant to draw attention to.

Erin Stellato pointed out that temp tables are associated with some atypical plan forcing behavior.  Dropping of tables, indexes, or statistics can also result in atypical plan forcing behavior.

For these reasons I've reworked the repro which I'll be building on in these blog posts.

In my initial blog post, the test query used "SELECT... INTO" with a temp table.

The new version of the repro contains a CREATE TABLE statement before the test query, and no longer uses a temp table.

I'm still creating the test database the same way.


USE [master]
GO
CREATE DATABASE [surprise_plan]
 ON  PRIMARY 
( NAME = N'surprise_plan', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.CTP_3_0__2019\MSSQL\DATA\surprise_plan.mdf' , SIZE = 139264KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'surprise_plan_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.CTP_3_0__2019\MSSQL\DATA\surprise_plan_log.ldf' , SIZE = 270336KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO
ALTER DATABASE [surprise_plan] SET  MULTI_USER 
GO
ALTER DATABASE [surprise_plan] SET QUERY_STORE = ON
GO
ALTER DATABASE [surprise_plan] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), DATA_FLUSH_INTERVAL_SECONDS = 60, INTERVAL_LENGTH_MINUTES = 1, MAX_STORAGE_SIZE_MB = 1000, QUERY_CAPTURE_MODE = ALL, SIZE_BASED_CLEANUP_MODE = AUTO)
GO
ALTER DATABASE [surprise_plan] SET  READ_WRITE 
GO

The stored procedure follows the same type of strategy.  Note there are now two parameters. @testQuery_reps indicates how many instances of the test query will be executed.  In addition to the test query itself, the stored procedure executes some additional queries to increase the rate of change in the Query Store structures.  @filler_reps indicates how times the 'filler query' section of code will execute.

USE [surprise_plan]
GO
CREATE OR ALTER PROCEDURE [dbo].[surprise_plan2test] @testQuery_reps INT, @filler_reps INT = 2
AS
SET NOCOUNT ON
DROP TABLE IF EXISTS sq__surprise_plan_tests;
CREATE TABLE sq__surprise_plan_tests(
     query_id BIGINT,
     query_hash BINARY(8),
     plan_id BIGINT,
     query_plan_hash BINARY(8),
     runtime_stats_id BIGINT,
     count_executions BIGINT);
 
DECLARE @ij INT = 0, @query_count INT, @sqlcmd2 NVARCHAR(MAX), @sqlcmd NVARCHAR(MAX) =
'INSERT INTO sq__surprise_plan_tests
 SELECT /*surprise2test*/ qsq.query_id, qsq.query_hash, qsp.plan_id,
        qsp.query_plan_hash, qsrs.runtime_stats_id, qsrs.count_executions
 FROM sys.query_store_runtime_stats qsrs
 JOIN sys.query_store_plan qsp ON qsrs.plan_id = qsp.plan_id
 JOIN sys.query_store_query qsq ON qsp.query_id = qsq.query_id
 JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
 WHERE qsqt.query_sql_text LIKE ''%surprise2test%''
 ORDER BY qsrs.runtime_stats_id DESC;' 
 
WHILE @testQuery_reps > 0
BEGIN
     TRUNCATE TABLE sq__surprise_plan_tests;
     DBCC FREEPROCCACHE WITH no_infomsgs;
     EXEC (@sqlcmd);
     SET @ij = 0;
     WHILE @ij < @filler_reps
     BEGIN
          SELECT TOP 1 
@sqlcmd2 = 'SELECT TOP (1) plan_id INTO #tmp 
                             FROM SYS.QUERY_STORE_RUNTIME_STATS 
                             WHERE runtime_stats_interval_id = '
                             + CONVERT(NVARCHAR(10), ((query_id - 1) * @testQuery_reps) + @ij) + ';
                             DROP TABLE IF EXISTS #tmp;'
          FROM sys.query_store_query
          WHERE @testQuery_reps = @testQuery_reps AND @ij = @ij
          ORDER BY query_id DESC;
 
EXEC (@sqlcmd2);
          SET @ij = @ij + 1;
     END 
     SELECT @testQuery_reps = @testQuery_reps - 1;
END
  
SELECT * FROM sq__surprise_plan_tests
ORDER BY runtime_stats_id DESC;

Ok.  Let's execute the test query 1600 times, with each iteration of the test query submission accompanied by two iterations of the filler query section of code.


EXEC [dbo].[surprise_plan2test] @testQuery_reps = 1600, @filler_reps = 2;

The result... 19 unique plan_id values for the query from 1600 executions.



So let's take a look in the "Tracked Queries" Query Store activity.


Interesting that the order of plan_id values in the right-hand legend is immediately sensible.  Let's see what happens when I force plan_id 2...



I execute the test query again twice, with 1 time 'round for the filler queries. I could execute the test query a single time but I like it when the new plans show up in the query results the test query returns... due to timing issues that is reliable when the test query is executed twice but not when the test query is executed a single time only.


EXEC [dbo].[surprise_plan2test] @testQuery_reps = 2, @filler_reps = 1;

Well look at that! At the top of the results list is plan 3220 now.


Let's refresh the "Tracked Queries" activity.

Ohhhh.  I forced plan_id 2 (in the purple box below) but what showed up was plan_id 3220 (in the yellow box below).


Ok, that's weird.  Did a last_force_failure_reason_desc show up for plan_id 2?


SELECT plan_id, query_id, engine_version, compatibility_level,  
       is_forced_plan, last_force_failure_reason_desc
FROM sys.query_store_plan WHERE plan_id = 2;



Hm.  So I forced plan_id 2, but plan_id 3220 showed up instead.  And there is no last_force_failure_reason_desc for plan_id 2.

Kendra Little uses the term “morally equivalent plan” in some excellent blog posts, mentioning that the term likely came from Conor Cunningham via Erland Sommarskog.
I've listed those blog posts below, as well as a related Azure Feedback suggestion.

Forced Plan Confusion: Is_Forced vs Use Plan = True

https://sqlworkbooks.com/2018/03/forced-plan-confusion-is_forced-vs-use-plan-true/

What is a “Morally Equivalent Execution Plan,” and Why Is It Good?
https://sqlworkbooks.com/2018/03/what-is-a-morally-equivalent-execution-plan-and-why-is-it-good/

Indicate “morally equivalent” forced plan where use_plan = true but is_forced = 0
https://feedback.azure.com/forums/908035-sql-server/suggestions/33562136-indicate-morally-equivalent-forced-plan-where-us

So... something funky happened when I tried to force plan_id 2.  What if I try forcing the other plans for query_id 2?

Here's a grid of the plan_id values selected for execution after forcing each originally available plan_id for query_id 2.




Of 19 query plans available from the first 1600 executions of the test query, only two plan_id values were able to be successfully forced: 292 and 501.  Seventeen of the original plan_id values were not selected by the optimizer after they were forced from Query Store. That seems like the cards are stacked against me.

But as the plan_id values were forced, new plans appeared.  Plenty of them LoL.  If I add in the new plans and attempt to force them as well...



So, with all 34 of the plans in scope, it's a 50-50 chance.  17 of the original plans wouldn't force, 2 of them would.  Add in 15 surprise plans, and there are 17 plans that won't force and 17 plans that do force successfully. And the "Tracked Queries" activity now looks like...


 
Ok.  So with this example I hope I've shown that, at least as through SQL Server 2019 CTP 3.0, encountering a “morally equivalent plan” choice after Query Store forcing a plan might not be as rare as commonly held.

If you're still with me, I've still got a little ways to go with this one... next installment won't be at least until tomorrow, though.

Thanks for reading!

Ciao!!

Q

Saturday, June 15, 2019

SQL Server Query Store: Usability Soft Spots -- Part 1

Part 1 Summary: pretty easy to get lots of Query Store plan_id values per query_id, and sometimes the sort of plan_id values in "Tracked Queries" is downright unhelpful.

SQL Server Query Store: Usability Soft Spots -- Part 2
https://sql-sasquatch.blogspot.com/2019/06/sql-server-query-store-usability-soft_16.html
~~~~

For now, I'm working with a Developer Edition SQL Server 2019 CTP 3.0 installation, on my laptop.

No global trace flags enabled.  Pretty vanilla.

 
OK, let's create a database just for this series of blog posts. No tricks up my sleeve, enable Query Store out the gate, change QUERY_CAPTURE_MODE from default AUTO to ALL, DATA_FLUSH_INTERVAL_SECONDS = 60 and INTERVAL_LENGTH_MINUTES = 1 because these will be pretty quick tests.

USE [master]
GO
CREATE DATABASE [surprise_plan]
 ON  PRIMARY 
( NAME = N'surprise_plan', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.CTP_3_0__2019\MSSQL\DATA\surprise_plan.mdf' , SIZE = 139264KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'surprise_plan_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.CTP_3_0__2019\MSSQL\DATA\surprise_plan_log.ldf' , SIZE = 270336KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO
ALTER DATABASE [surprise_plan] SET  MULTI_USER 
GO
ALTER DATABASE [surprise_plan] SET QUERY_STORE = ON
GO
ALTER DATABASE [surprise_plan] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), DATA_FLUSH_INTERVAL_SECONDS = 60, INTERVAL_LENGTH_MINUTES = 1, MAX_STORAGE_SIZE_MB = 1000, QUERY_CAPTURE_MODE = ALL, SIZE_BASED_CLEANUP_MODE = AUTO)
GO
ALTER DATABASE [surprise_plan] SET  READ_WRITE 
GO

Now let's create stored procedure [surprise_plan1test] for some fun.  Nothing too clever here.  A query is executed against query store as dynamic SQL.  Do it as dynamic SQL so the offset is predictable and it always matches to the same Query Store query_id as long as context_settings_id stays the same.


USE [surprise_plan]
GO
CREATE OR ALTER   PROCEDURE [dbo].[surprise_plan1test] @max_reps INT
AS
SET NOCOUNT ON
DECLARE @ij INT = 0, @query_count INT, @sqlcmd2 NVARCHAR(MAX), @sqlcmd NVARCHAR(MAX) =
'SELECT /*surprise1test*/ qsq.query_id, qsq.query_hash, qsp.plan_id, qsp.query_plan_hash, qsrs.runtime_stats_id,
                      r_last_execution_time = MAX(qsrs.last_execution_time), r_count_executions = SUM(qsrs.count_executions)
 INTO ##results
 FROM sys.query_store_runtime_stats qsrs
 JOIN sys.query_store_plan qsp ON qsrs.plan_id = qsp.plan_id
 JOIN sys.query_store_query qsq ON qsp.query_id = qsq.query_id
 JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
 WHERE qsqt.query_sql_text LIKE ''%surprise1test%''
 GROUP BY qsq.query_id, qsq.query_hash, qsp.plan_id, qsp.query_plan_hash, qsrs.runtime_stats_id
 ORDER BY qsrs.runtime_stats_id DESC, r_last_execution_time DESC;'
SELECT @query_count = COUNT(*) FROM sys.query_store_query;
WHILE @ij < @max_reps
BEGIN
DROP TABLE IF EXISTS ##results;
DBCC FREEPROCCACHE WITH no_infomsgs;
EXEC (@sqlcmd);
SET @sqlcmd2 = 'SELECT TOP (1) * 
INTO #tmp 
FROM SYS.QUERY_STORE_RUNTIME_STATS 
WHERE runtime_stats_interval_id = ' + CONVERT(NVARCHAR(10), @query_count + @ij) + ';
DROP TABLE IF EXISTS #tmp;'
EXEC (@sqlcmd2);
EXEC (@sqlcmd2);
SET @ij = @ij +1;
END
SELECT * FROM ##results
ORDER BY runtime_stats_id DESC;



EXEC [dbo].[surprise_plan1test] @max_reps = 1600


All right.  Let's head over to the Query Store activities. Since I know I'm interested in query_id 4, that's the one I'll pull up.


Yeah. That's a lotta plans in the right-hand legend.  22 of them.  In a not very helpful order.  In fact... though I've tried to figure it out, I don't know what type of order is used for that right-hand legend.  It's not chronological.  It's not based on duration which is the metric displayed by the graph. I dunno.

Usually with a few more executions of query_id 4 and a few refreshes of this activity, the right-legend becomes sorted in descending plan_id from top-to-bottom. And that's pretty durable.  But sometimes it switches back to this nonsensical (to me) ordering.

Got plenty more to show but I wanted to keep this blog post short as an introduction to what I'd be working with.

In the field, I see this quite often - a query_id with over 20 plans that show up in the Tracked Queries activity.  And the unhelpful ordering of plans in the right-hand legend, while a minor and fleeting inconvenience, is usability soft spot number 1. 

For an update to the repro code and the next step in looking at Query Store Usability Soft Spots continue at...
SQL Server Query Store: Usability Soft Spots -- Part 2
https://sql-sasquatch.blogspot.com/2019/06/sql-server-query-store-usability-soft_16.html

Thanks for reading!

Ciao!!

Q

Tuesday, May 28, 2019

SQL Server memory node memory footprints

Day-in, day-out i use perfmon to monitor SQL Server resource utilization and behavior, because its observer overhead is so low.

But its useful to have a few TSQL queries in the stable, too, for adhoc use or if a given memory state needs to result in some action taken within SQL Server.

i'm sure there's a clever wayt to use 'pivot', 'unpivot' and/or 'cross apply' to make this query more elegant. But i just wanted to get this here somewhere. if i figure out a more elegant way to get the resultset i'll update this blog post.
~~~~~

;WITH dbc AS (SELECT node=instance_name, dbc = cntr_value
     FROM sys.dm_os_performance_counters 
     WHERE counter_name = 'Database Node Memory (KB)'),
stl AS (SELECT [node]=instance_name, stl = cntr_value
     FROM sys.dm_os_performance_counters
     WHERE counter_name = 'Stolen Node Memory (KB)'),
fre AS (SELECT [node]=instance_name, fre = cntr_value
     FROM sys.dm_os_performance_counters
     WHERE counter_name = 'Free Node Memory (KB)'),
tot AS (SELECT [node]=instance_name, tot = cntr_value
     FROM sys.dm_os_performance_counters
     WHERE counter_name = 'Total Node Memory (KB)'),
tgt AS (SELECT [node]=instance_name, tgt = cntr_value
     FROM sys.dm_os_performance_counters
     WHERE counter_name = 'Target Node Memory (KB)'),
fgn AS (SELECT [node]=instance_name, fgn = cntr_value
     FROM sys.dm_os_performance_counters
     WHERE counter_name = 'Foreign Node Memory (KB)')
SELECT [memNode] = dbc.[node], 
       [dbCache] = dbc.dbc, 
       [stolen]  = stl.stl, 
       [free]    = fre.fre,
       [total]   = tot.tot, 
       [target]  = tgt.tgt, 
       [foreign] = fgn.fgn
FROM dbc
JOIN stl ON dbc.[node] = stl.[node]
JOIN fre ON dbc.[node] = fre.[node]
JOIN tot ON dbc.[node] = tot.[node]
JOIN tgt ON dbc.[node] = tgt.[node]
JOIN fgn ON dbc.[node] = fgn.[node];


These results - in the order of their capture - are much more interesting.





Let's look at the results below a bit.
The target for the instance is 4 * 180, 991, 992 kb =  723, 967, 968 kb = 707, 000 mb.
Total for the instance is 883, 498, 216 = 862, 791 mb. 155, 791 mb is a pretty big overage.



Total foreign memory is 207, 386, 832 kb = 202, 506 mb. That's a lot of foreign memory.







Q.