OK, its fully-baked :-)
Yep, kb3189645 changes the behavior of the QO with respect to filtered indexes and quickstats.
When trace flags 2389, 2390, or 4139 are enabled on a system with kb3189645 filtered index stats are exempt from quickstats queries - even under the conditions which would lead to a quickstats query for a nonfiltered index.
So it's an unadvertised fix for Connect Item 2528743 😊 Look for the update within the blog post interior in purple to explain when Connect Item 2528743 can lead to such llllooonnngggg query plan compiles.
Trace flag 2390 can cause large compile time when dealing with filtered indexes. (Active)
https://connect.microsoft.com/SQLServer/feedback/details/2528743/
OK... where does that leave me for stuff to blog about later?
- Show an example of a query with a filtered index that has a risk for poor plan quality with quickstats. (Such a query is at risk for poor plan quality after kb3189645.)
- Test the 'use hint' for histogram amendment introduced in SQL Server 2016 SP1. Is it for legacy CE only? Does it behave the same as trace flag 2389/2390/4139?
'k. 's enuff for now.
Ciao!
*****
The complete text of the test is at the bottom of this post. I won't consider this a fully-baked post until kb3189645 is confirmed responsible for the change I've seen in behavior related to quickstats queries & filtered indexes.
FIX: Access violation when you run a query that uses clustered columnstore index with trace flag 2389, 2390, or 4139
https://support.microsoft.com/en-us/kb/3189645
Here's my theory:
1. before kb3189645, clustered columnstore indexes were not exempt from quickstats queries to amend histograms. If trace flag 2389, 2390, or 4139 lead to the optimizer issuing a quickstats query for a CCI, an access violation could result. (I've seen these occur. The AVs would prevent plan compile; an AV would even result from grabbing the estimated plan in SSMS.)
2. Kb3189645 resolved this by exempting CCIs from quickstats queries.
3. Kb3189645 also exempted filtered indexes from quickstats queries, whereas previous to kb3189645 quickstats queries could be issued against filtered indexes.
The kb in question is included in SQL Server 2014 SP1 CU9(build 12.00.4474) and SP2 CU2(build 12.00.5532).
Microsoft SQL Server Version List
https://sqlserverbuilds.blogspot.com/
The instances on which I compared behavior are SQL Server 2014 SP1 CU0 (build 12.0.4100) and SQL Server 2014 SP1-CU9-GDR (build 12.0.4487).
To begin, we borrow a function from Itzik Ben-Gan to efficiently create tables of consecutive integers. Then create an Extended Events session specific to the spid used in the experiments, using the sp_statement_completed event. The EE session is to capture the SQL query issued when quickstats are used to amend the histogram. The EE session is created in a STOP state, state will change to START once a little closer to the specific behavior we want to observe. (I imagine there's a way to capture the quickstats queries outside of the context of a stored procedure and the sp_statement_completed event but I wasn't successful in finding it.) A table with integer columns NUM1 and NUM2, and an NVARCHAR column String1 is also created. There's a filtered nonclustered index on (NUM1, NUM2), and a nonfiltered nonclustered index on (NUM2, NUM1).
SET NOCOUNT ON; GO CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE AS RETURN WITH L0 AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)), L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B), L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B), L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B), L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B), L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B), Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum FROM L5) SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n FROM Nums ORDER BY rownum; ---------------------------------------------------------------------- -- © Itzik Ben-Gan ---------------------------------------------------------------------- /* http://tsql.solidq.com/SourceCodes/GetNums.txt */ GO DECLARE @sqlText2 NVARCHAR(1024) = N'CREATE EVENT SESSION track_sql_text_spid_' + CONVERT(NVARCHAR(256),@@SPID) +' ON SERVER ADD EVENT sqlserver.sp_statement_completed( ACTION(sqlserver.sql_text) WHERE ([sqlserver].[session_id]=(' + CONVERT(NVARCHAR(256),@@SPID) +'))) ADD TARGET package0.ring_buffer WITH (MAX_DISPATCH_LATENCY = 1 SECONDS)'; EXEC (@sqlText2); GO CREATE TABLE [dbo].[X_TBL_QUICKSTATS]( [NUM1] INT, [NUM2] INT, [String1] NVARCHAR(1024)); /* create filtered index and nonfiltered index sibling */ CREATE INDEX X_FILTERED_INDEX ON [X_TBL_QUICKSTATS] (NUM1, NUM2) WHERE NUM1 > 1000000 AND NUM2 > 0; CREATE INDEX X_INDEX ON [X_TBL_QUICKSTATS] (NUM2, NUM1); GO
Then let's create the stored procedure with a query that will load the stats for the filtered index and the nonfiltered index. Someday I'll blog about queries which in past versions loaded filtered index stats but in SQL Server 2014 are able to get by with the filtered index stats header only. I had expected to see that with the new cardinality estimater; my tests showed that even with the legacy CE there were some changes. With enough rows in the table, the following query will do nicely.
/* create stored procedure with query to load filtered and nonfiltered index stats */ CREATE PROCEDURE X_SP_TBL_FILTERED_INDEX AS SELECT (SELECT COUNT(NUM1) FROM [X_TBL_QUICKSTATS] WHERE NUM1 > 1000400 AND NUM1 < 1000501 AND NUM2 > 0) AS n1, (SELECT COUNT(NUM2) FROM [X_TBL_QUICKSTATS] WHERE NUM2 > 1000400 AND NUM2 < 1000501 AND NUM1 > 0) AS n2 OPTION (RECOMPILE); GO
Now its time to populate the table with rows, and update stats enough times to get the lead index column branded ascending or stationary.
/* insert 1000000 rows to start */ INSERT INTO [X_TBL_QUICKSTATS] WITH (TABLOCK) SELECT NUMS.n AS NUM1, NUMS.n AS NUM2, CONVERT(NVARCHAR(1024),NULL) AS String1 FROM dbo.GetNums(1,1000000) NUMS; /* auto-update & sampled updates change when a leading column is branded ascending, use FULLSCAN to make predictable */ UPDATE STATISTICS [X_TBL_QUICKSTATS] WITH FULLSCAN; -- insert some data and update stats so T2388 shows leading columns as Ascending INSERT INTO [X_TBL_QUICKSTATS] WITH (TABLOCK) SELECT TOP (100) NUMS.n AS NUM1, NUMS.n AS NUM2, CONVERT(NVARCHAR(1024),NULL) AS String1 FROM dbo.GetNums(1000001,1000100) NUMS ORDER BY NUMS.n; UPDATE STATISTICS [X_TBL_QUICKSTATS] WITH FULLSCAN; INSERT INTO [X_TBL_QUICKSTATS] WITH (TABLOCK) SELECT TOP (100) NUMS.n AS NUM1, NUMS.n AS NUM2, CONVERT(NVARCHAR(1024),NULL) AS String1 FROM dbo.GetNums(1000101,1000200) NUMS ORDER BY NUMS.n; UPDATE STATISTICS [X_TBL_QUICKSTATS] WITH FULLSCAN; INSERT INTO [X_TBL_QUICKSTATS] WITH (TABLOCK) SELECT TOP (100) NUMS.n AS NUM1, NUMS.n AS NUM2, CONVERT(NVARCHAR(1024),NULL) AS String1 FROM dbo.GetNums(1000201,1000300) NUMS ORDER BY NUMS.n; UPDATE STATISTICS [X_TBL_QUICKSTATS] WITH FULLSCAN; INSERT INTO [X_TBL_QUICKSTATS] WITH (TABLOCK) SELECT TOP (100) NUMS.n AS NUM1, NUMS.n AS NUM2, CONVERT(NVARCHAR(1024),NULL) AS String1 FROM dbo.GetNums(1000301,1000400) NUMS ORDER BY NUMS.n; UPDATE STATISTICS [X_TBL_QUICKSTATS] WITH FULLSCAN;
Immediately after the initial insert of 1000000 rows and four subsequent inserts of 100 rows, FULLSCAN stats updates were performed. Now let's add 100 more rows, outside of the range of the current histogram. And let's NOT update stats this time.
/* 100 more rows but no stats update */ INSERT INTO [X_TBL_QUICKSTATS] WITH (TABLOCK) SELECT TOP (100) NUMS.n AS NUM1, NUMS.n AS NUM2, CONVERT(NVARCHAR(1024),NULL) AS String1 FROM dbo.GetNums(1000401,1000500) NUMS ORDER BY NUMS.n;
Let's check the setup.
/* verify Leading columns are ascending and cardinality does not reflect most recent 100 rows */ dbcc traceon(2388) WITH no_infomsgs; dbcc show_statistics ([X_TBL_QUICKSTATS], X_INDEX) WITH no_infomsgs; dbcc show_statistics ([X_TBL_QUICKSTATS], X_FILTERED_INDEX) WITH no_infomsgs; dbcc traceoff(2388) WITH no_infomsgs;
That's what we expect to see. 1000400 as "Table Cardinality" for the nonfiltered nonclustered index - even though there are now 1000500 rows in this index and the table. 400 as "Table Cardinality" for the filtered index, when there are 500 rows in the filtered index. Both the nonfiltered and filtered index branded as ascending.
Before we start the EE session, let's execute the stored procedure once. This will auto-create column stats if necessary, and keep them out of the EE session.
EXEC X_SP_TBL_FILTERED_INDEX;
All right. Let's change the EE session state to START.
DECLARE @sqlText3 NVARCHAR(1024) = N'ALTER EVENT SESSION track_sql_text_spid_' + CONVERT(NVARCHAR(256),@@SPID) +' ON SERVER STATE = START;'; EXEC (@sqlText3);
Now execute the stored proc.
/* 2389 - quickstats for Ascending 2390 - quickstats for Unknown 4139 - quickstats for Ascending, Unknown, Stationary 9481 - force old CE, not valid before SQL Server 2014 9204 + 3604 - which stats are loaded For both of the counts below an unadjusted estimate will be 1 row */ DBCC TRACEON( 2389, 4139, 9481, 9204, 3604) with NO_INFOMSGS; EXEC X_SP_TBL_FILTERED_INDEX; DBCC TRACEOFF(2389, 4139, 9481, 9204, 3604) with NO_INFOMSGS; /* SQL Server 2008 build 10.0.4000.0 1st count has amended estimate of 200502 rows 2nd count has amended estimate of 100 rows */
Dropping the event from the session "freezes" what we have in the ring buffer.
DECLARE @sqlText4 NVARCHAR(1024) = N'ALTER EVENT SESSION track_sql_text_spid_' + CONVERT(NVARCHAR(256),@@SPID) +' ON SERVER DROP EVENT sqlserver.sp_statement_completed;' EXEC (@sqlText4);
What did we catch in our net?
DECLARE @sqlText5 NVARCHAR(1024) = N'SELECT sql_text FROM ( SELECT tab.event.value(''(event/data[@name="statement"]/value)[1]'', ''nvarchar(max)'') as [sql_text] FROM ( SELECT n.query(''.'') as event FROM ( SELECT CAST(target_data AS XML) AS target_xml FROM sys.dm_xe_sessions AS s JOIN sys.dm_xe_session_targets AS t ON s.address = t.event_session_address WHERE s.name = ''track_sql_text_spid_' + CONVERT(NVARCHAR(256),@@SPID) +''' AND t.target_name = ''ring_buffer'' ) AS sub CROSS APPLY target_xml.nodes(''RingBufferTarget/event'') AS q(n) ) AS tab ) tab2 WHERE tab2.sql_text LIKE ''%StatMan%'';'; EXEC (@sqlText5);
*****UPDATE 20170103*****
Here's why the behavior in this test case is potentially a BIG problem as Michael J Swart describes in Connect Item 2528743. The quickstats query for the filtered index looks remarkably similar to the quickstats query for the nonfiltered index. The quickstats query has no filter! (Like me at a holiday party 😜.) Without a WHERE clause that agrees with the filter of the filtered index, that query *can't* be satisfied by a single row read from the filtered index! *If* there's another, unfiltered NCI on NUM1 it could be satisfied by a single row read from *that* index. If *not* - full table scan required.
*****
Now let's take a look at the results on SQL Server 2014 SP1-CU9-GDR. Still two queries for NUM2 - lead column for the nonfiltered index. But no StatsMan query for NUM1 - no quickstats query for the filtered index.
*****
As I mentioned above at the top of the post, here is the entirety of the code to reproduce this test.
/* DROP TABLE [X_TBL_QUICKSTATS] DROP PROCEDURE X_SP_TBL_FILTERED_INDEX DECLARE @sqlText1 NVARCHAR(1024) = N'DROP EVENT SESSION track_sql_text_spid_' + CONVERT(NVARCHAR(256),@@SPID) +' ON SERVER'; EXEC (@sqlText1); DROP FUNCTION dbo.GetNums */ SET NOCOUNT ON; GO CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE AS RETURN WITH L0 AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)), L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B), L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B), L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B), L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B), L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B), Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum FROM L5) SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n FROM Nums ORDER BY rownum; ---------------------------------------------------------------------- -- © Itzik Ben-Gan ---------------------------------------------------------------------- /* http://tsql.solidq.com/SourceCodes/GetNums.txt */ GO DECLARE @sqlText2 NVARCHAR(1024) = N'CREATE EVENT SESSION track_sql_text_spid_' + CONVERT(NVARCHAR(256),@@SPID) +' ON SERVER ADD EVENT sqlserver.sp_statement_completed( ACTION(sqlserver.sql_text) WHERE ([sqlserver].[session_id]=(' + CONVERT(NVARCHAR(256),@@SPID) +'))) ADD TARGET package0.ring_buffer WITH (MAX_DISPATCH_LATENCY = 1 SECONDS)'; EXEC (@sqlText2); GO CREATE TABLE [dbo].[X_TBL_QUICKSTATS]( [NUM1] INT, [NUM2] INT, [String1] NVARCHAR(1024)); /* create filtered index and nonfiltered index sibling */ CREATE INDEX X_FILTERED_INDEX ON [X_TBL_QUICKSTATS] (NUM1, NUM2) WHERE NUM1 > 1000000 AND NUM2 > 0; CREATE INDEX X_INDEX ON [X_TBL_QUICKSTATS] (NUM2, NUM1); GO /* create stored procedure with query to load filtered and nonfiltered index stats */ CREATE PROCEDURE X_SP_TBL_FILTERED_INDEX AS SELECT (SELECT COUNT(NUM1) FROM [X_TBL_QUICKSTATS] WHERE NUM1 > 1000400 AND NUM1 < 1000501 AND NUM2 > 0) AS n1, (SELECT COUNT(NUM2) FROM [X_TBL_QUICKSTATS] WHERE NUM2 > 1000400 AND NUM2 < 1000501 AND NUM1 > 0) AS n2 OPTION (RECOMPILE); GO /* insert 1000000 rows to start */ INSERT INTO [X_TBL_QUICKSTATS] WITH (TABLOCK) SELECT NUMS.n AS NUM1, NUMS.n AS NUM2, CONVERT(NVARCHAR(1024),NULL) AS String1 FROM dbo.GetNums(1,1000000) NUMS; /* auto-update & sampled updates change when a leading column is branded ascending, use FULLSCAN to make predictable */ UPDATE STATISTICS [X_TBL_QUICKSTATS] WITH FULLSCAN; -- insert some data and update stats so T2388 shows leading columns as Ascending INSERT INTO [X_TBL_QUICKSTATS] WITH (TABLOCK) SELECT TOP (100) NUMS.n AS NUM1, NUMS.n AS NUM2, CONVERT(NVARCHAR(1024),NULL) AS String1 FROM dbo.GetNums(1000001,1000100) NUMS ORDER BY NUMS.n; UPDATE STATISTICS [X_TBL_QUICKSTATS] WITH FULLSCAN; INSERT INTO [X_TBL_QUICKSTATS] WITH (TABLOCK) SELECT TOP (100) NUMS.n AS NUM1, NUMS.n AS NUM2, CONVERT(NVARCHAR(1024),NULL) AS String1 FROM dbo.GetNums(1000101,1000200) NUMS ORDER BY NUMS.n; UPDATE STATISTICS [X_TBL_QUICKSTATS] WITH FULLSCAN; INSERT INTO [X_TBL_QUICKSTATS] WITH (TABLOCK) SELECT TOP (100) NUMS.n AS NUM1, NUMS.n AS NUM2, CONVERT(NVARCHAR(1024),NULL) AS String1 FROM dbo.GetNums(1000201,1000300) NUMS ORDER BY NUMS.n; UPDATE STATISTICS [X_TBL_QUICKSTATS] WITH FULLSCAN; INSERT INTO [X_TBL_QUICKSTATS] WITH (TABLOCK) SELECT TOP (100) NUMS.n AS NUM1, NUMS.n AS NUM2, CONVERT(NVARCHAR(1024),NULL) AS String1 FROM dbo.GetNums(1000301,1000400) NUMS ORDER BY NUMS.n; UPDATE STATISTICS [X_TBL_QUICKSTATS] WITH FULLSCAN; /* 100 more rows but no stats update */ INSERT INTO [X_TBL_QUICKSTATS] WITH (TABLOCK) SELECT TOP (100) NUMS.n AS NUM1, NUMS.n AS NUM2, CONVERT(NVARCHAR(1024),NULL) AS String1 FROM dbo.GetNums(1000401,1000500) NUMS ORDER BY NUMS.n; /* verify Leading columns are ascending and cardinality does not reflect most recent 100 rows */ dbcc traceon(2388) WITH no_infomsgs; dbcc show_statistics ([X_TBL_QUICKSTATS], X_INDEX) WITH no_infomsgs; dbcc show_statistics ([X_TBL_QUICKSTATS], X_FILTERED_INDEX) WITH no_infomsgs; dbcc traceoff(2388) WITH no_infomsgs; /* Calling the sp here handles autocreate of column stats, keeps out of extended events observation Actual plan should show estimate of 1 row for both counts if no trace flag 2389, 2390, 4139 active */ EXEC X_SP_TBL_FILTERED_INDEX; DECLARE @sqlText3 NVARCHAR(1024) = N'ALTER EVENT SESSION track_sql_text_spid_' + CONVERT(NVARCHAR(256),@@SPID) +' ON SERVER STATE = START;'; EXEC (@sqlText3); /* 2389 - quickstats for Ascending 2390 - quickstats for Unknown 4139 - quickstats for Ascending, Unknown, Stationary 9481 - force old CE, not valid before SQL Server 2014 9204 + 3604 - which stats are loaded For both of the counts below an unadjusted estimate will be 1 row */ DBCC TRACEON( 2389, 4139, 9481, 9204, 3604) with NO_INFOMSGS; EXEC X_SP_TBL_FILTERED_INDEX; DBCC TRACEOFF(2389, 4139, 9481, 9204, 3604) with NO_INFOMSGS; /* SQL Server 2008 build 10.0.4000.0 1st count has amended estimate of 200502 rows 2nd count has amended estimate of 100 rows */ DECLARE @sqlText4 NVARCHAR(1024) = N'ALTER EVENT SESSION track_sql_text_spid_' + CONVERT(NVARCHAR(256),@@SPID) +' ON SERVER DROP EVENT sqlserver.sp_statement_completed;' EXEC (@sqlText4); DECLARE @sqlText5 NVARCHAR(1024) = N'SELECT sql_text FROM ( SELECT tab.event.value(''(event/data[@name="statement"]/value)[1]'', ''nvarchar(max)'') as [sql_text] FROM ( SELECT n.query(''.'') as event FROM ( SELECT CAST(target_data AS XML) AS target_xml FROM sys.dm_xe_sessions AS s JOIN sys.dm_xe_session_targets AS t ON s.address = t.event_session_address WHERE s.name = ''track_sql_text_spid_' + CONVERT(NVARCHAR(256),@@SPID) +''' AND t.target_name = ''ring_buffer'' ) AS sub CROSS APPLY target_xml.nodes(''RingBufferTarget/event'') AS q(n) ) AS tab ) tab2 WHERE tab2.sql_text LIKE ''%StatMan%'';'; EXEC (@sqlText5); SELECT @@version /* On version Microsoft SQL Server 2014 (SP1-CU9-GDR) (KB3194722) filtered indexes appear to be exempted. sql_text SELECT StatMan([SC0]) FROM (SELECT TOP 1 [NUM2] AS [SC0] FROM [dbo].[X_TBL_QUICKSTATS] WITH (READUNCOMMITTED) ORDER BY [SC0] DESC) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 1) */ DECLARE @sqlText6 NVARCHAR(1024) = N'ALTER EVENT SESSION track_sql_text_spid_' + CONVERT(NVARCHAR(256),@@SPID) +' ON SERVER STATE = STOP;' EXEC (@sqlText6);
No comments:
Post a Comment