Trace flag 2390 can cause large compile time when dealing with filtered indexes. - by Michael J. Swart

Status : 

 


5
0
Sign in
to vote
ID 2528743 Comments
Status Active Workarounds
Type Bug Repros 2
Opened 3/29/2016 12:49:54 PM
Access Restriction Public

Description

When a table has a filtered index whose statistics are out of date, and trace flag 2390 is enabled, then SQL Server will scan the index during compile time on certain queries.

This seems just like the problem described in this fix: https://support.microsoft.com/en-us/kb/922063
However, we're seeing this behavior in SQL Server 2012 (and 2014 with 110 compatibility mode)
Sign in to post a comment.
Posted by Lonny Niederstadt on 1/3/2017 at 9:44 AM
It looks to me like the long plan compile potentially caused by quickstats queries against filtered indexes is resolved by kb3189645.
http://sql-sasquatch.blogspot.com/2016/12/sql-server-trace-flags-238923904139.html

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

This resolves the long compile by exempting filtered indexes from quickstats updates. This fix does mean potential query plan quality issues if a plan depends on auto-stats updates and histogram amendments for a filtered index. But I believe there are additional reasons to consider putting filtered index stats at the head of the line when its time to do manual stats updates.
Posted by Lonny Niederstadt on 12/23/2016 at 9:04 PM
-- I'm not sure when the behavior change was introduced, but below is the testing code we've used to see in SQL Server 2016 SP1
-- Filtered Index stats are exempted from quickstats queries even in cases where nonfiltered index stats get quickstats queries.
/* DROP TABLE [X_TBL_QUICKSTATS]
    DROP PROCEDURE X_SP_TBL_FILTERED_INDEX
    DROP PROCEDURE X_SP_TBL_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
----------------------------------------------------------------------
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 NOT NULL,
     [NUM2] int NULL,
     [NUM3] int NULL,
     [NUM4] int NULL);

-- create filtered index and nonfiltered index sibling
CREATE INDEX X_FILTERED_INDEX ON [X_TBL_QUICKSTATS] (NUM1, NUM2)
WHERE NUM1 > 999900 AND NUM2 < 10000000;

CREATE INDEX X_INDEX ON [X_TBL_QUICKSTATS] (NUM3, NUM4);
GO

CREATE PROCEDURE X_SP_TBL_FILTERED_INDEX
AS
BEGIN
     SELECT COUNT(NUM1), COUNT(NUM2) FROM [X_TBL_QUICKSTATS]
     WHERE NUM1 > 1000400 AND NUM1 < 1000600 AND NUM2 < 10000000
     OPTION (RECOMPILE);
END

GO

CREATE PROCEDURE X_SP_TBL_INDEX
AS
BEGIN
     SELECT COUNT(NUM3), COUNT(NUM4) FROM [X_TBL_QUICKSTATS]
     WHERE NUM3 > 1000400 AND NUM3 < 1000600 AND NUM4 < 10000000
     OPTION (RECOMPILE);
END
GO

/* insert 1000000 rows to start */
INSERT INTO [X_TBL_QUICKSTATS] WITH (TABLOCK)
SELECT NUMS.n AS NUM1, NUMS.n AS NUM2, NUMS.n AS NUM3, NUMS.n AS NUM4
FROM dbo.GetNums(1,1000000) NUMS;

SELECT object_name(ss.object_id) AS table_name,
     ss.name AS stats_name, sc.name AS [LeadingColumnName], ss.filter_definition,
     sp.last_updated, sp.[rows], sp.rows_sampled, sp.unfiltered_rows, sp.modification_counter
FROM sys.stats ss
JOIN sys.stats_columns ssc ON ss.object_id = ssc.object_id
     AND ss.stats_id = ssc.stats_id AND ssc.stats_column_id = 1
JOIN sys.columns sc ON sc.object_id = ss.object_id and sc.column_id = ssc.column_id
CROSS APPLY sys.dm_db_stats_properties(ss.object_id,ss.stats_id) sp
WHERE ss.object_id = object_id ('X_TBL_QUICKSTATS');

/*
table_name    stats_name    LeadingColumnName    filter_definition    last_updated    rows    rows_sampled    unfiltered_rows    modification_counter
X_TBL_QUICKSTATS    X_FILTERED_INDEX    NUM1    ([NUM1]>(999900) AND [NUM2]<(10000000))    NULL    NULL    NULL    NULL    NULL
X_TBL_QUICKSTATS    X_INDEX    NUM3    NULL    NULL    NULL    NULL    NULL    NULL
*/

/* 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 */
DBCC TRACEON(2389, 2390, 4139, 9481, 9204, 3604) with NO_INFOMSGS;
EXEC X_SP_TBL_FILTERED_INDEX -- first stats update, auto-create of column stats
EXEC X_SP_TBL_INDEX         -- first stats update, auto-create of column stats
DBCC TRACEOFF(2389, 2390, 4139, 9481, 9204, 3604) with NO_INFOMSGS;

/*
Stats loaded: DbName: lnieders2, ObjName: X_TBL_QUICKSTATS, IndexId: 4, ColumnName: NUM2, EmptyTable: FALSE
Stats loaded: DbName: lnieders2, ObjName: X_TBL_QUICKSTATS, IndexId: 5, ColumnName: NUM1, EmptyTable: FALSE
Filtered stats loaded: DbName: lnieders2, ObjName: X_TBL_QUICKSTATS, IndexId: 2, ColumnName: NUM1, Expr: ([NUM1]>(999900) AND [NUM2]<(10000000)), EmptyTable: FALSE

Stats loaded: DbName: lnieders2, ObjName: X_TBL_QUICKSTATS, IndexId: 3, ColumnName: NUM3, EmptyTable: FALSE
Stats loaded: DbName: lnieders2, ObjName: X_TBL_QUICKSTATS, IndexId: 6, ColumnName: NUM4, EmptyTable: FALSE
*/


SELECT object_name(ss.object_id) AS table_name,
     ss.name AS stats_name, sc.name AS [LeadingColumnName], ss.filter_definition,
     sp.last_updated, sp.[rows], sp.rows_sampled, sp.unfiltered_rows, sp.modification_counter
FROM sys.stats ss
JOIN sys.stats_columns ssc ON ss.object_id = ssc.object_id
     AND ss.stats_id = ssc.stats_id AND ssc.stats_column_id = 1
JOIN sys.columns sc ON sc.object_id = ss.object_id and sc.column_id = ssc.column_id
CROSS APPLY sys.dm_db_stats_properties(ss.object_id,ss.stats_id) sp
WHERE ss.object_id = object_id ('X_TBL_QUICKSTATS');

/*
table_name    stats_name    LeadingColumnName    filter_definition
X_TBL_FILTERED_STAT_ISSUE    X_FILTERED_INDEX    NUM1    ([NUM1]>(999900) AND [NUM2]<(10000000))
X_TBL_FILTERED_STAT_ISSUE    X_INDEX    NUM3    NULL
X_TBL_FILTERED_STAT_ISSUE    _WA_Sys_00000002_5C37ACAD    NUM2    NULL
X_TBL_FILTERED_STAT_ISSUE    _WA_Sys_00000001_5C37ACAD    NUM1    NULL
X_TBL_FILTERED_STAT_ISSUE    _WA_Sys_00000004_5C37ACAD    NUM4    NULL
*/

-- 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, NUMS.n AS NUM3, NUMS.n AS NUM4
FROM dbo.GetNums(1000001,1000100) NUMS
ORDER BY NUMS.n;
UPDATE STATISTICS [X_TBL_FILTERED_STAT_ISSUE] X_INDEX WITH FULLSCAN;
UPDATE STATISTICS [X_TBL_FILTERED_STAT_ISSUE] X_FILTERED_INDEX WITH FULLSCAN;

INSERT INTO [X_TBL_QUICKSTATS] WITH (TABLOCK)
SELECT TOP (100) NUMS.n AS NUM1, NUMS.n AS NUM2, NUMS.n AS NUM3, NUMS.n AS NUM4
FROM dbo.GetNums(1000101,1000200) NUMS
ORDER BY NUMS.n;
UPDATE STATISTICS [X_TBL_QUICKSTATS] X_INDEX WITH FULLSCAN;
UPDATE STATISTICS [X_TBL_QUICKSTATS] X_FILTERED_INDEX WITH FULLSCAN;

INSERT INTO [X_TBL_QUICKSTATS] WITH (TABLOCK)
SELECT TOP (100) NUMS.n AS NUM1, NUMS.n AS NUM2, NUMS.n AS NUM3, NUMS.n AS NUM4
FROM dbo.GetNums(1000201,1000300) NUMS
ORDER BY NUMS.n;
UPDATE STATISTICS [X_TBL_QUICKSTATS] X_INDEX WITH FULLSCAN;
UPDATE STATISTICS [X_TBL_QUICKSTATS] X_FILTERED_INDEX WITH FULLSCAN;

-- now add a little more data but don't update stats
INSERT INTO [X_TBL_QUICKSTATS] WITH (TABLOCK)
SELECT TOP (100) NUMS.n AS NUM1, NUMS.n AS NUM2, NUMS.n AS NUM3, NUMS.n AS NUM4
FROM dbo.GetNums(1000401,1000500) NUMS
ORDER BY NUMS.n;

-- verify Leading columns are ascending but no histogram step range hi values above 1000400
SELECT object_name(ss.object_id) AS table_name,
     ss.name AS stats_name, sc.name AS [LeadingColumnName], ss.filter_definition,
     sp.last_updated, sp.[rows], sp.rows_sampled, sp.unfiltered_rows, sp.modification_counter
FROM sys.stats ss
JOIN sys.stats_columns ssc ON ss.object_id = ssc.object_id
     AND ss.stats_id = ssc.stats_id AND ssc.stats_column_id = 1
JOIN sys.columns sc ON sc.object_id = ss.object_id and sc.column_id = ssc.column_id
CROSS APPLY sys.dm_db_stats_properties(ss.object_id,ss.stats_id) sp
WHERE ss.object_id = object_id ('X_TBL_QUICKSTATS');

/*
table_name    stats_name    LeadingColumnName    filter_definition    last_updated    rows    rows_sampled    unfiltered_rows    modification_counter
X_TBL_QUICKSTATS    X_FILTERED_INDEX    NUM1    ([NUM1]>(999900) AND [NUM2]<(10000000))    2016-12-23 22:13:37.2866667    400    400    1000300    100
X_TBL_QUICKSTATS    X_INDEX    NUM3    NULL    2016-12-23 22:13:37.2833333    1000300    1000300    1000300    100
X_TBL_QUICKSTATS    _WA_Sys_00000002_6C190EBB    NUM2    NULL    2016-12-23 22:10:43.5366667    1000000    361429    1000000    400
X_TBL_QUICKSTATS    _WA_Sys_00000001_6C190EBB    NUM1    NULL    2016-12-23 22:10:43.5766667    1000000    361429    1000000    400
X_TBL_QUICKSTATS    _WA_Sys_00000004_6C190EBB    NUM4    NULL    2016-12-23 22:10:43.6800000    1000000    360272    1000000    400
*/

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;
dbcc show_statistics ([X_TBL_QUICKSTATS], X_INDEX) WITH no_infomsgs, HISTOGRAM;
dbcc show_statistics ([X_TBL_QUICKSTATS], X_FILTERED_INDEX) WITH no_infomsgs, HISTOGRAM;

DECLARE @sqlText3 NVARCHAR(1024) =
N'ALTER EVENT SESSION track_sql_text_spid_' + CONVERT(NVARCHAR(256),@@SPID) +' ON SERVER
STATE = START;';
EXEC (@sqlText3);

DBCC TRACEON(2389, 2390, 4139, 9481, 9204, 3604) with NO_INFOMSGS;
EXEC X_SP_TBL_FILTERED_INDEX;
EXEC X_SP_TBL_INDEX;
DBCC TRACEOFF(2389, 2390, 4139, 9481, 9204, 3604) with NO_INFOMSGS;
/* SQL Server 2016 SP1
1st query has estimate of 1 row, no ascending key adjustment
2nd query has amended estimate of 50 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);
/*
In SQL Server 2016 SP1
Checking extended events capture, there was a quickstats query for the nonfiltered index, but no quickstats query for the filtered index.
sql_text
SELECT StatMan([SC0]) FROM (SELECT TOP 1 [NUM3] AS [SC0] FROM [dbo].[X_TBL_FILTERED_STAT_ISSUE] WITH (READUNCOMMITTED) ORDER BY [SC0] DESC) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 1)

On earlier versions in such cases, the filtered index would also get a quickstats query similar to this
SELECT StatMan([SC0]) FROM (SELECT TOP 1 [NUM1] AS [SC0] FROM [dbo].[X_TBL_FILTERED_STAT_ISSUE] WITH (READUNCOMMITTED) ORDER BY [SC0] DESC) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 1)
Without the filter that commpanies the filtered index, and without another index on NUM2 a fullscan of the table was required to satisfy the quickstats query.
*/

DECLARE @sqlText6 NVARCHAR(1024) =
N'ALTER EVENT SESSION track_sql_text_spid_' + CONVERT(NVARCHAR(256),@@SPID) +' ON SERVER
STATE = STOP;'
EXEC (@sqlText6);
Posted by Lonny Niederstadt on 12/23/2016 at 3:08 PM
Working with similar test conditions in SQL Server 2016 SP1 (with trace flag 9481 to force Legacy Cardinality Estimater), it seems quickstats queries are no longer issued for filtered indexes even under conditions that see similar nonfiltered nonclustered indexes get quickstats queries for corrected estimates.