Here's a Connect item from Joe Sack related to auto-update of stats on filtered indexes. It includes code for repro that's much cleaner than mine :-)
Suggesting change to filtered statistics updates
https://connect.microsoft.com/SQLServer/feedback/details/509638
And here's a blog post from Kimberly Tripp discussing the risk of seriously out-of-date stats on filtered indexes. Gail Shaw brings up in the comments a case with a million row table and a 10000 row filtered index that required over 200000 modifications to the filtered index before updating.
Filtered indexes and filtered stats might become seriously out-of-date
http://www.sqlskills.com/blogs/kimberly/filtered-indexes-and-filtered-stats-might-become-seriously-out-of-date/
As I've been researching an issue with long query compiles when filtered indexes are involved, I've come up with more questions than answers about the handling of filtered index statistics.
For example, here's an interesting change from 'Stationary' to 'Unknown' for the leading column on a filtered index.
SQL Server Ascending Key Stuff: UPDATE STATS when nothing has changed can lead to a surprising brand...
http://sql-sasquatch.blogspot.com/2016/12/sql-server-ascending-key-stuff-change.html
But lets talk a little about auto-update of filtered index stats. In the tests I walk through below auto create stats and auto update stats are both enabled. Auto update stats async is disabled.
Default stats auto-update threshold calculation (pre-SQL Server 2016) is widely known. When statistics for a table of more than 500 rows are updated, the next auto-update threshold is set: except for filtered indexes/stats, its 500 + 20% of the rows in the table at that time. When the threshold is reached, the stats are invalidated. The next time the query optimizer asks to load those statistics, if auto-update statistics (without async) is enabled, the stats will be auto-updated before the query optimizer continues with its merry work. If async is enabled, although the query optimizer won't wait for the update, the stats auto-update is queued.
Wrinkle number 1 is trace flag 2371 before SQL Server 2016 - or the default threshold calculation as of SQL Server 2016. For tables below 25,000 rows, update thresholds are the same as ever. Above 25,000 rows the threshold is calculated as sqrt(1000 * table rows). This allows very large tables to qualify for auto-update much more often than the previous default calculation.
Filtered indexes are another significant wrinkle. Please bear with me as I introduce the tools I've been using for testing.
This is a stored procedure to drop (if it prexists) and create the test table.
CREATE PROCEDURE [dbo].[T4139_Fact__drop_create]
AS
IF OBJECT_ID('[dbo].[T4139_Fact]') IS NOT NULL DROP TABLE [dbo].[T4139_Fact]
CREATE TABLE [dbo].[T4139_Fact](
[Bigint_Primary_Key] [bigint] NOT NULL,
[Numeric_18_0__1] [numeric](18, 0) NULL,
[Bigint_Alternate_Key] [bigint] NOT NULL,
[Nvarchar_50__1] [nvarchar](50) NOT NULL,
[Bit__1] [bit] NOT NULL,
[Nvarchar_data__2] [nvarchar](50) NOT NULL,
[numeric_18_0__2] [numeric](18, 0) NULL,
[datetime__1] [datetime] NULL,
[datetime__2] [datetime] NULL,
CONSTRAINT [PK__T4139_Fact] PRIMARY KEY CLUSTERED
( [Bigint_Primary_Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [Nci_Bit__1] ON [dbo].[T4139_Fact]
( [Bigint_Alternate_Key] ASC,
[Bit__1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
I create the filtered index in question separately because I've been fiddling with a number of different indexes. Here's the relevant index for today's post.
Note [Bit__1]=(1) in the filtered index predicate. Someday in the future it'll be really important that Bit__1 is in the predicate without being in the keys or include list. But I've got to work up to that :-) [Bit__1]=(1) is also important when looking at the stored procedure that I use to populate the table. Its got 10 rows out of every 100 with [Bit__1]=(1).
CREATE PROCEDURE [dbo].[nci_filtered_Nvarchar_50__1__drop_create]
AS
IF EXISTS (SELECT 1 FROM sys.indexes si WHERE si.object_id = OBJECT_ID('T4139_FACT') AND si.name = 'nci_filtered_Nvarchar_50__1')
DROP INDEX [T4139_Fact].[T4139_Fact];
CREATE NONCLUSTERED INDEX [nci_filtered_Nvarchar_50__1] ON [dbo].[T4139_Fact]
( [Numeric_18_0__1] ASC,
[Nvarchar_50__1] ASC,
[datetime__1] ASC,
[datetime__2] ASC
)
INCLUDE
( [Nvarchar_data__2],
[numeric_18_0__2]
)
WHERE ([Bit__1]=(1) AND [Nvarchar_50__1] IS NOT NULL)
Now that the table and filtered index are in place, let's have a stored procedure to populate some data. This one handles 100 rows at a time for me. As I mentioned before 10 rows out of 100 have [Bit__1]=(1). I used that ratio just to make tracking the numbers a bit easier.
CREATE PROCEDURE [dbo].[stuff__T4139_fact] @START int, @END INT, @BASE_INT INT, @BASE_DT DATETIME
AS
DECLARE @ITER INT, @BATCH_INT INT;
SET @ITER = @START;
WHILE @ITER < @END + 1
BEGIN
SET @BATCH_INT = @BASE_INT * @ITER
INSERT INTO T4139_fact
VALUES (@BATCH_INT + 100, @BATCH_INT - 199, @BATCH_INT + 201, N'AAA', 0, N'1A1', @BATCH_INT + 100, DATEADD(MINUTE, @BATCH_INT - 199, @BASE_DT), DATEADD(MINUTE,101 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 101, @BATCH_INT - 101, @BATCH_INT - 202, N'ABB', 0, N'1A2', @BATCH_INT + 101, DATEADD(MINUTE, @BATCH_INT - 198, @BASE_DT), DATEADD(MINUTE,102 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 102, @BATCH_INT - 198, @BATCH_INT + 203, N'ABC', 0, N'1A3', @BATCH_INT + 199, DATEADD(MINUTE, @BATCH_INT - 197, @BASE_DT), DATEADD(MINUTE,103 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 103, @BATCH_INT - 102, @BATCH_INT - 204, N'D2A', 0, N'1A4', @BATCH_INT + 198, DATEADD(MINUTE, @BATCH_INT - 196, @BASE_DT), DATEADD(MINUTE,104 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 104, @BATCH_INT - 197, @BATCH_INT + 205, N'E2A', 0, N'105', @BATCH_INT + 102, DATEADD(MINUTE, @BATCH_INT - 195, @BASE_DT), DATEADD(MINUTE,105 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 105, @BATCH_INT - 103, @BATCH_INT - 206, N'F2A', 0, N'1A5', @BATCH_INT + 103, DATEADD(MINUTE, @BATCH_INT - 194, @BASE_DT), DATEADD(MINUTE,106 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 106, @BATCH_INT - 196, @BATCH_INT + 207, N'G2A', 0, N'01A', @BATCH_INT + 197, DATEADD(MINUTE, @BATCH_INT - 193, @BASE_DT), DATEADD(MINUTE,107 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 107, @BATCH_INT - 104, @BATCH_INT - 208, N'H2A', 0, N'A10', @BATCH_INT + 196, DATEADD(MINUTE, @BATCH_INT - 192, @BASE_DT), DATEADD(MINUTE,108 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 108, @BATCH_INT - 195, @BATCH_INT + 209, N'I2A', 0, N'1BA', @BATCH_INT + 104, DATEADD(MINUTE, @BATCH_INT - 191, @BASE_DT), DATEADD(MINUTE,109 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 109, @BATCH_INT - 105, @BATCH_INT - 210, N'J2A', 0, N'1CA', @BATCH_INT + 105, DATEADD(MINUTE, @BATCH_INT - 190, @BASE_DT), DATEADD(MINUTE,110 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 110, @BATCH_INT - 194, @BATCH_INT + 211, N'K2A', 0, N'10A', @BATCH_INT + 195, DATEADD(MINUTE, @BATCH_INT - 189, @BASE_DT), DATEADD(MINUTE,111 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 111, @BATCH_INT - 106, @BATCH_INT - 212, N'L3A', 0, N'11A', @BATCH_INT + 194, DATEADD(MINUTE, @BATCH_INT - 188, @BASE_DT), DATEADD(MINUTE,112 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 112, @BATCH_INT - 193, @BATCH_INT + 213, N'M4A', 0, N'12A', @BATCH_INT + 106, DATEADD(MINUTE, @BATCH_INT - 187, @BASE_DT), DATEADD(MINUTE,113 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 113, @BATCH_INT - 107, @BATCH_INT - 214, N'N5A', 0, N'13A', @BATCH_INT + 107, DATEADD(MINUTE, @BATCH_INT - 186, @BASE_DT), DATEADD(MINUTE,114 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 114, @BATCH_INT - 192, @BATCH_INT + 215, N'O6A', 0, N'14A', @BATCH_INT + 193, DATEADD(MINUTE, @BATCH_INT - 185, @BASE_DT), DATEADD(MINUTE,115 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 115, @BATCH_INT - 108, @BATCH_INT - 216, N'P7A', 0, N'15A', @BATCH_INT + 192, DATEADD(MINUTE, @BATCH_INT - 184, @BASE_DT), DATEADD(MINUTE,116 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 116, @BATCH_INT - 191, @BATCH_INT + 217, N'Q8A', 0, N'16A', @BATCH_INT + 108, DATEADD(MINUTE, @BATCH_INT - 183, @BASE_DT), DATEADD(MINUTE,117 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 117, @BATCH_INT - 109, @BATCH_INT - 218, N'R9A', 0, N'17A', @BATCH_INT + 109, DATEADD(MINUTE, @BATCH_INT - 182, @BASE_DT), DATEADD(MINUTE,118 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 118, @BATCH_INT - 190, @BATCH_INT + 219, N'S7A', 0, N'18A', @BATCH_INT + 192, DATEADD(MINUTE, @BATCH_INT - 181, @BASE_DT), DATEADD(MINUTE,119 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 119, @BATCH_INT - 110, @BATCH_INT - 220, N'T8A', 1, N'19A', @BATCH_INT + 191, DATEADD(MINUTE, @BATCH_INT - 180, @BASE_DT), DATEADD(MINUTE,120 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 120, @BATCH_INT - 189, @BATCH_INT + 221, N'U2A', 0, N'10A', @BATCH_INT + 110, DATEADD(MINUTE, @BATCH_INT - 179, @BASE_DT), DATEADD(MINUTE,121 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 121, @BATCH_INT - 111, @BATCH_INT - 222, N'V3A', 0, N'11A', @BATCH_INT + 111, DATEADD(MINUTE, @BATCH_INT - 178, @BASE_DT), DATEADD(MINUTE,122 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 122, @BATCH_INT - 188, @BATCH_INT + 223, N'W4A', 0, N'12A', @BATCH_INT + 190, DATEADD(MINUTE, @BATCH_INT - 177, @BASE_DT), DATEADD(MINUTE,123 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 123, @BATCH_INT - 112, @BATCH_INT - 224, N'X5A', 0, N'13A', @BATCH_INT + 189, DATEADD(MINUTE, @BATCH_INT - 176, @BASE_DT), DATEADD(MINUTE,124 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 124, @BATCH_INT - 187, @BATCH_INT + 225, N'Y6A', 0, N'14A', @BATCH_INT + 112, DATEADD(MINUTE, @BATCH_INT - 175, @BASE_DT), DATEADD(MINUTE,125 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 125, @BATCH_INT - 113, @BATCH_INT - 226, N'Z7A', 0, N'15A', @BATCH_INT + 113, DATEADD(MINUTE, @BATCH_INT - 174, @BASE_DT), DATEADD(MINUTE,126 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 126, @BATCH_INT - 186, @BATCH_INT + 227, N'8AA', 0, N'16A', @BATCH_INT + 188, DATEADD(MINUTE, @BATCH_INT - 173, @BASE_DT), DATEADD(MINUTE,127 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 127, @BATCH_INT - 114, @BATCH_INT - 228, N'9AB', 0, N'17A', @BATCH_INT + 187, DATEADD(MINUTE, @BATCH_INT - 172, @BASE_DT), DATEADD(MINUTE,128 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 128, @BATCH_INT - 185, @BATCH_INT + 229, N'7AC', 1, N'18A', @BATCH_INT + 114, DATEADD(MINUTE, @BATCH_INT - 171, @BASE_DT), DATEADD(MINUTE,129 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 129, @BATCH_INT - 115, @BATCH_INT - 230, N'8AD', 0, N'19A', @BATCH_INT + 115, DATEADD(MINUTE, @BATCH_INT - 170, @BASE_DT), DATEADD(MINUTE,130 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 130, @BATCH_INT - 184, @BATCH_INT + 231, N'2AE', 0, N'10A', @BATCH_INT + 186, DATEADD(MINUTE, @BATCH_INT - 169, @BASE_DT), DATEADD(MINUTE,131 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 131, @BATCH_INT - 116, @BATCH_INT - 232, N'3AF', 0, N'11A', @BATCH_INT + 185, DATEADD(MINUTE, @BATCH_INT - 168, @BASE_DT), DATEADD(MINUTE,132 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 132, @BATCH_INT - 183, @BATCH_INT + 233, N'4AG', 0, N'12A', @BATCH_INT + 116, DATEADD(MINUTE, @BATCH_INT - 167, @BASE_DT), DATEADD(MINUTE,133 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 133, @BATCH_INT - 117, @BATCH_INT - 234, N'5AH', 1, N'13A', @BATCH_INT + 117, DATEADD(MINUTE, @BATCH_INT - 166, @BASE_DT), DATEADD(MINUTE,134 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 134, @BATCH_INT - 182, @BATCH_INT + 235, N'6AI', 0, N'14A', @BATCH_INT + 184, DATEADD(MINUTE, @BATCH_INT - 165, @BASE_DT), DATEADD(MINUTE,135 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 135, @BATCH_INT - 118, @BATCH_INT - 236, N'7AJ', 0, N'15A', @BATCH_INT + 183, DATEADD(MINUTE, @BATCH_INT - 164, @BASE_DT), DATEADD(MINUTE,136 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 136, @BATCH_INT - 181, @BATCH_INT + 237, N'8AK', 0, N'16A', @BATCH_INT + 118, DATEADD(MINUTE, @BATCH_INT - 163, @BASE_DT), DATEADD(MINUTE,137 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 137, @BATCH_INT - 119, @BATCH_INT - 238, N'9AL', 0, N'17A', @BATCH_INT + 119, DATEADD(MINUTE, @BATCH_INT - 162, @BASE_DT), DATEADD(MINUTE,138 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 138, @BATCH_INT - 180, @BATCH_INT + 239, N'7AM', 0, N'18A', @BATCH_INT + 182, DATEADD(MINUTE, @BATCH_INT - 161, @BASE_DT), DATEADD(MINUTE,139 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 139, @BATCH_INT - 120, @BATCH_INT - 240, N'8AN', 0, N'19A', @BATCH_INT + 181, DATEADD(MINUTE, @BATCH_INT - 160, @BASE_DT), DATEADD(MINUTE,140 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 140, @BATCH_INT - 179, @BATCH_INT + 241, N'2AO', 0, N'10A', @BATCH_INT + 120, DATEADD(MINUTE, @BATCH_INT - 159, @BASE_DT), DATEADD(MINUTE,141 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 141, @BATCH_INT - 121, @BATCH_INT - 242, N'3AP', 0, N'11A', @BATCH_INT + 121, DATEADD(MINUTE, @BATCH_INT - 158, @BASE_DT), DATEADD(MINUTE,142 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 142, @BATCH_INT - 178, @BATCH_INT + 243, N'4AQ', 0, N'12A', @BATCH_INT + 180, DATEADD(MINUTE, @BATCH_INT - 157, @BASE_DT), DATEADD(MINUTE,143 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 143, @BATCH_INT - 122, @BATCH_INT - 244, N'5AR', 0, N'13A', @BATCH_INT + 179, DATEADD(MINUTE, @BATCH_INT - 156, @BASE_DT), DATEADD(MINUTE,144 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 144, @BATCH_INT - 177, @BATCH_INT + 245, N'6AS', 0, N'14A', @BATCH_INT + 122, DATEADD(MINUTE, @BATCH_INT - 157, @BASE_DT), DATEADD(MINUTE,145 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 145, @BATCH_INT - 123, @BATCH_INT - 246, N'7AT', 0, N'15A', @BATCH_INT + 123, DATEADD(MINUTE, @BATCH_INT - 158, @BASE_DT), DATEADD(MINUTE,146 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 146, @BATCH_INT - 176, @BATCH_INT + 247, N'8AU', 0, N'16A', @BATCH_INT + 178, DATEADD(MINUTE, @BATCH_INT - 159, @BASE_DT), DATEADD(MINUTE,147 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 147, @BATCH_INT - 124, @BATCH_INT - 248, N'9AV', 0, N'17A', @BATCH_INT + 177, DATEADD(MINUTE, @BATCH_INT - 160, @BASE_DT), DATEADD(MINUTE,148 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 148, @BATCH_INT - 175, @BATCH_INT + 249, N'7Aw', 0, N'18A', @BATCH_INT + 124, DATEADD(MINUTE, @BATCH_INT - 161, @BASE_DT), DATEADD(MINUTE,149 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 149, @BATCH_INT - 125, @BATCH_INT - 250, N'8AX', 0, N'19A', @BATCH_INT + 125, DATEADD(MINUTE, @BATCH_INT - 162, @BASE_DT), DATEADD(MINUTE,150 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 150, @BATCH_INT - 174, @BATCH_INT + 251, N'2AY', 1, N'10A', @BATCH_INT + 176, DATEADD(MINUTE, @BATCH_INT - 163, @BASE_DT), DATEADD(MINUTE,151 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 151, @BATCH_INT - 126, @BATCH_INT - 252, N'3AZ', 0, N'11A', @BATCH_INT + 175, DATEADD(MINUTE, @BATCH_INT - 164, @BASE_DT), DATEADD(MINUTE,151 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 152, @BATCH_INT - 173, @BATCH_INT + 253, N'4AB', 1, N'12A', @BATCH_INT + 126, DATEADD(MINUTE, @BATCH_INT - 165, @BASE_DT), DATEADD(MINUTE,152 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 153, @BATCH_INT - 127, @BATCH_INT - 254, N'5AC', 0, N'13A', @BATCH_INT + 127, DATEADD(MINUTE, @BATCH_INT - 166, @BASE_DT), DATEADD(MINUTE,153 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 154, @BATCH_INT - 172, @BATCH_INT + 255, N'6AD', 0, N'14A', @BATCH_INT + 174, DATEADD(MINUTE, @BATCH_INT - 167, @BASE_DT), DATEADD(MINUTE,154 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 155, @BATCH_INT - 128, @BATCH_INT - 256, N'7AE', 0, N'15A', @BATCH_INT + 173, DATEADD(MINUTE, @BATCH_INT - 168, @BASE_DT), DATEADD(MINUTE,155 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 156, @BATCH_INT - 171, @BATCH_INT + 257, N'8AF', 0, N'16A', @BATCH_INT + 128, DATEADD(MINUTE, @BATCH_INT - 143, @BASE_DT), DATEADD(MINUTE,156 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 157, @BATCH_INT - 129, @BATCH_INT - 258, N'9AG', 0, N'17A', @BATCH_INT + 129, DATEADD(MINUTE, @BATCH_INT - 142, @BASE_DT), DATEADD(MINUTE,157 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 158, @BATCH_INT - 170, @BATCH_INT + 259, N'7AH', 0, N'18A', @BATCH_INT + 172, DATEADD(MINUTE, @BATCH_INT - 141, @BASE_DT), DATEADD(MINUTE,158 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 159, @BATCH_INT - 130, @BATCH_INT - 260, N'8AI', 0, N'19A', @BATCH_INT + 171, DATEADD(MINUTE, @BATCH_INT - 140, @BASE_DT), DATEADD(MINUTE,159 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 160, @BATCH_INT - 169, @BATCH_INT + 261, N'2AJ', 0, N'10A', @BATCH_INT + 130, DATEADD(MINUTE, @BATCH_INT - 139, @BASE_DT), DATEADD(MINUTE,160 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 161, @BATCH_INT - 131, @BATCH_INT - 262, N'3AK', 0, N'11A', @BATCH_INT + 131, DATEADD(MINUTE, @BATCH_INT - 138, @BASE_DT), DATEADD(MINUTE,161 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 162, @BATCH_INT - 168, @BATCH_INT + 263, N'4AL', 0, N'12A', @BATCH_INT + 170, DATEADD(MINUTE, @BATCH_INT - 137, @BASE_DT), DATEADD(MINUTE,162 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 163, @BATCH_INT - 132, @BATCH_INT - 264, N'5AM', 0, N'13A', @BATCH_INT + 169, DATEADD(MINUTE, @BATCH_INT - 136, @BASE_DT), DATEADD(MINUTE,163 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 164, @BATCH_INT - 167, @BATCH_INT + 265, N'6AN', 0, N'14A', @BATCH_INT + 132, DATEADD(MINUTE, @BATCH_INT - 135, @BASE_DT), DATEADD(MINUTE,164 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 165, @BATCH_INT - 133, @BATCH_INT - 266, N'7AO', 0, N'15A', @BATCH_INT + 133, DATEADD(MINUTE, @BATCH_INT - 134, @BASE_DT), DATEADD(MINUTE,165 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 166, @BATCH_INT - 166, @BATCH_INT + 267, N'8AP', 0, N'16A', @BATCH_INT + 168, DATEADD(MINUTE, @BATCH_INT - 133, @BASE_DT), DATEADD(MINUTE,166 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 167, @BATCH_INT - 134, @BATCH_INT - 268, N'9AQ', 0, N'17A', @BATCH_INT + 167, DATEADD(MINUTE, @BATCH_INT - 132, @BASE_DT), DATEADD(MINUTE,167 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 168, @BATCH_INT - 165, @BATCH_INT + 269, N'7AR', 1, N'18A', @BATCH_INT + 134, DATEADD(MINUTE, @BATCH_INT - 131, @BASE_DT), DATEADD(MINUTE,168 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 169, @BATCH_INT - 135, @BATCH_INT - 270, N'8AS', 0, N'19A', @BATCH_INT + 135, DATEADD(MINUTE, @BATCH_INT - 130, @BASE_DT), DATEADD(MINUTE,169 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 170, @BATCH_INT - 164, @BATCH_INT + 271, N'2AT', 0, N'10A', @BATCH_INT + 166, DATEADD(MINUTE, @BATCH_INT - 129, @BASE_DT), DATEADD(MINUTE,170 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 171, @BATCH_INT - 136, @BATCH_INT - 272, N'3AU', 0, N'11A', @BATCH_INT + 165, DATEADD(MINUTE, @BATCH_INT - 128, @BASE_DT), DATEADD(MINUTE,171 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 172, @BATCH_INT - 163, @BATCH_INT + 273, N'4AV', 0, N'12A', @BATCH_INT + 136, DATEADD(MINUTE, @BATCH_INT - 127, @BASE_DT), DATEADD(MINUTE,172 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 173, @BATCH_INT - 137, @BATCH_INT - 274, N'5AW', 0, N'13A', @BATCH_INT + 137, DATEADD(MINUTE, @BATCH_INT - 126, @BASE_DT), DATEADD(MINUTE,173 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 174, @BATCH_INT - 199, @BATCH_INT + 275, N'6AX', 0, N'14A', @BATCH_INT + 164, DATEADD(MINUTE, @BATCH_INT - 125, @BASE_DT), DATEADD(MINUTE,174 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 175, @BATCH_INT - 138, @BATCH_INT - 276, N'7AW', 0, N'15A', @BATCH_INT + 163, DATEADD(MINUTE, @BATCH_INT - 124, @BASE_DT), DATEADD(MINUTE,175 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 176, @BATCH_INT - 162, @BATCH_INT + 277, N'8AV', 0, N'16A', @BATCH_INT + 138, DATEADD(MINUTE, @BATCH_INT - 123, @BASE_DT), DATEADD(MINUTE,176 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 177, @BATCH_INT - 139, @BATCH_INT - 278, N'9AW', 0, N'17A', @BATCH_INT + 139, DATEADD(MINUTE, @BATCH_INT - 122, @BASE_DT), DATEADD(MINUTE,177 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 178, @BATCH_INT - 161, @BATCH_INT + 279, N'7AY', 1, N'18A', @BATCH_INT + 162, DATEADD(MINUTE, @BATCH_INT - 121, @BASE_DT), DATEADD(MINUTE,178 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 179, @BATCH_INT - 140, @BATCH_INT - 280, N'8AZ', 1, N'19A', @BATCH_INT + 161, DATEADD(MINUTE, @BATCH_INT - 120, @BASE_DT), DATEADD(MINUTE,179 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 180, @BATCH_INT - 160, @BATCH_INT + 281, N'2AZ', 0, N'10A', @BATCH_INT + 140, DATEADD(MINUTE, @BATCH_INT - 119, @BASE_DT), DATEADD(MINUTE,180 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 181, @BATCH_INT - 141, @BATCH_INT - 282, N'3AY', 0, N'11A', @BATCH_INT + 141, DATEADD(MINUTE, @BATCH_INT - 118, @BASE_DT), DATEADD(MINUTE,181 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 182, @BATCH_INT - 159, @BATCH_INT + 283, N'4AX', 0, N'12A', @BATCH_INT + 160, DATEADD(MINUTE, @BATCH_INT - 117, @BASE_DT), DATEADD(MINUTE,182 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 183, @BATCH_INT - 142, @BATCH_INT - 284, N'5AY', 0, N'13A', @BATCH_INT + 159, DATEADD(MINUTE, @BATCH_INT - 116, @BASE_DT), DATEADD(MINUTE,183 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 184, @BATCH_INT - 158, @BATCH_INT + 285, N'6AV', 0, N'14A', @BATCH_INT + 142, DATEADD(MINUTE, @BATCH_INT - 115, @BASE_DT), DATEADD(MINUTE,184 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 185, @BATCH_INT - 143, @BATCH_INT - 286, N'7AU', 0, N'15A', @BATCH_INT + 143, DATEADD(MINUTE, @BATCH_INT - 114, @BASE_DT), DATEADD(MINUTE,185 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 186, @BATCH_INT - 157, @BATCH_INT + 287, N'8AT', 0, N'16A', @BATCH_INT + 158, DATEADD(MINUTE, @BATCH_INT - 113, @BASE_DT), DATEADD(MINUTE,186 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 187, @BATCH_INT - 144, @BATCH_INT - 288, N'9AS', 0, N'17A', @BATCH_INT + 157, DATEADD(MINUTE, @BATCH_INT - 112, @BASE_DT), DATEADD(MINUTE,187 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 188, @BATCH_INT - 156, @BATCH_INT + 289, N'7AR', 0, N'18A', @BATCH_INT + 144, DATEADD(MINUTE, @BATCH_INT - 111, @BASE_DT), DATEADD(MINUTE,188 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 189, @BATCH_INT - 145, @BATCH_INT - 290, N'8AQ', 0, N'19A', @BATCH_INT + 145, DATEADD(MINUTE, @BATCH_INT - 110, @BASE_DT), DATEADD(MINUTE,189 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 190, @BATCH_INT - 155, @BATCH_INT + 291, N'2AP', 0, N'10A', @BATCH_INT + 156, DATEADD(MINUTE, @BATCH_INT - 109, @BASE_DT), DATEADD(MINUTE,190 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 191, @BATCH_INT - 146, @BATCH_INT - 292, N'3AO', 0, N'11A', @BATCH_INT + 155, DATEADD(MINUTE, @BATCH_INT - 108, @BASE_DT), DATEADD(MINUTE,191 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 192, @BATCH_INT - 154, @BATCH_INT + 293, N'4AN', 0, N'12A', @BATCH_INT + 146, DATEADD(MINUTE, @BATCH_INT - 107, @BASE_DT), DATEADD(MINUTE,192 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 193, @BATCH_INT - 147, @BATCH_INT - 294, N'5AM', 0, N'13A', @BATCH_INT + 147, DATEADD(MINUTE, @BATCH_INT - 106, @BASE_DT), DATEADD(MINUTE,193 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 194, @BATCH_INT - 153, @BATCH_INT + 295, N'6AL', 0, N'14A', @BATCH_INT + 154, DATEADD(MINUTE, @BATCH_INT - 105, @BASE_DT), DATEADD(MINUTE,194 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 195, @BATCH_INT - 148, @BATCH_INT - 296, N'7AK', 0, N'15A', @BATCH_INT + 153, DATEADD(MINUTE, @BATCH_INT - 104, @BASE_DT), DATEADD(MINUTE,195 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 196, @BATCH_INT - 152, @BATCH_INT + 297, N'8AJ', 0, N'16A', @BATCH_INT + 148, DATEADD(MINUTE, @BATCH_INT - 103, @BASE_DT), DATEADD(MINUTE,196 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 197, @BATCH_INT - 149, @BATCH_INT - 298, N'9AI', 0, N'17A', @BATCH_INT + 149, DATEADD(MINUTE, @BATCH_INT - 102, @BASE_DT), DATEADD(MINUTE,197 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 198, @BATCH_INT - 151, @BATCH_INT + 299, N'7AH', 1, N'18A', @BATCH_INT + 152, DATEADD(MINUTE, @BATCH_INT - 101, @BASE_DT), DATEADD(MINUTE,198 - @BATCH_INT, @BASE_DT)),
(@BATCH_INT + 199, @BATCH_INT - 150, @BATCH_INT - 300, N'8AG', 1, N'19A', @BATCH_INT + 151, DATEADD(MINUTE, @BATCH_INT - 100, @BASE_DT), DATEADD(MINUTE,199 - @BATCH_INT, @BASE_DT))
SET @ITER = @ITER + 1
END
For this testing, the query itself is quite important. I wasted hours of testing with queries whose plans included the filtered index after loading only the stats header and NOT loading the stats. Turns out that happens pretty frequently when filtered indexes are involved - and it will NOT trigger an auto-update of the filtered index stats even if the threshold is passed. Hopefully I'll blog about that sometime in the future, too. Here's a query I've been using in my testing once I confirmed that it loads the filtered index stats and triggers an auto-stats update as expected. I wrapped it in a stored procedure to make my test scenarios easier to manipulate.
CREATE PROCEDURE [dbo].[T4139_testQuery]
AS
SELECT datetime__1, Nvarchar_50__1
INTO #tempresults
FROM T4139_Fact
WHERE 1=1
AND Bit__1 = 1
AND Nvarchar_50__1 IS NOT NULL
AND Numeric_18_0__1 > 2000
AND Nvarchar_50__1 > N'4AB'
OPTION (RECOMPILE);
DROP TABLE #tempresults
Here's a final stored procedure. This proc I use to display some stats update history using trace flag 2388.
CREATE PROCEDURE [dbo].[T4139_Fact__T2388_showstats]
AS
CREATE TABLE #one_stat_2388 (
[Updated] NVARCHAR(256),
[Table Cardinality] BIGINT,
[Snapshot Ctr] BIGINT,
[Steps] INT,
[Density] FLOAT(53),
[Rows Above] FLOAT(53),
[Rows Below] FLOAT(53),
[Squared Variance Error] FLOAT(53),
[Inserts Since Last Update] FLOAT(53),
[Deletes Since Last Update] FLOAT(53),
[Leading column Type] NVARCHAR(256));
CREATE TABLE #stats_display (
[stats_name] NVARCHAR(256),
[Updated] NVARCHAR(256),
[Table Cardinality] BIGINT,
[Snapshot Ctr] BIGINT,
[Steps] INT,
[Density] FLOAT(53),
[Rows Above] FLOAT(53),
[Rows Below] FLOAT(53),
[Squared Variance Error] FLOAT(53),
[Inserts Since Last Update] FLOAT(53),
[Deletes Since Last Update] FLOAT(53),
[Leading column Type] NVARCHAR(256));
DECLARE @stats_name NVARCHAR(256), @sqlText NVARCHAR(MAX)
SELECT name,CONVERT(BIT,0) AS done
INTO #tempList
FROM sys.stats ss
WHERE ss.object_id = OBJECT_ID('T4139_Fact');
DBCC TRACEON(2388);
WHILE EXISTS (SELECT 1 FROM #tempList WHERE done = 0)
BEGIN
SELECT @stats_name = [name]
FROM #templist
WHERE done = 0
ORDER BY [name];
SET @sqlText = N'DBCC SHOW_STATISTICS(T4139_Fact, ' + @stats_name + N') WITH NO_INFOMSGS';
INSERT INTO #one_stat_2388
EXEC (@sqlText);
INSERT INTO #stats_display
SELECT @stats_name, oneStat.*
FROM #one_stat_2388 oneStat
WHERE [Updated] IS NOT NULL;
TRUNCATE TABLE #one_stat_2388;
UPDATE #templist
SET [done] = 1
WHERE [name] = @stats_name;
END
DBCC TRACEOFF(2388);
SELECT * FROM #stats_display
ORDER BY [stats_name], [Updated], [Table Cardinality];
DROP TABLE #stats_display;
DROP TABLE #one_stat_2388;
So let's create the table and filtered index, and populate it with 5100 table rows.
SET NOCOUNT ON;
EXEC [dbo].[T4139_Fact__drop_create];
EXEC [dbo].[nci_filtered_Nvarchar_50__1__drop_create];
/* Insert 5100 table rows ~ 510 filtered index rows */
EXEC stuff__T4139_fact @START = 1, @END = 51, @BASE_INT = 10000, @BASE_DT = '20010101 00:00:00';
/* 5100 table rows ~ 510 filtered index rows */
SELECT
(SELECT COUNT(*) FROM T4139_Fact) AS table_rows,
(SELECT COUNT(*) FROM T4139_Fact WHERE ([Bit__1]=(1) AND [Nvarchar_50__1] IS NOT NULL)) AS filtered_idx_rows;
Stats won't get their initial update until the query optimizer loads them while working with a query. So let's make sure that happens.
I'm enabling trace flag 8666 at the session level here so that stats info is included in the 'actual plan' when I retrieve it.
/* stored proc enables T8666 at session level;
"ullThreshold" in plan XML = auto update thresholds
auto-created column stats: 1520; filtered index: 602 */
DBCC TRACEON(8666) WITH NO_INFOMSGS;
EXEC T4139_testQuery;
DBCC TRACEOFF(8666) WITH NO_INFOMSGS;
EXEC [dbo].[T4139_Fact__T2388_showstats];
That looks as expected - three auto created stats and the filtered index stats all with their first update in response to the test query.
Let's take a look at the plan xml from T4139_testQuery.
The threshold for the column stats is 1520. That's (20% of 5100) + 500.
The threshold for the filtered stat is displayed as 602. That's (20% of 510) + 500.
Let's add 1520 table rows - the threshold for the column stats.
/* Insert 1520 table rows, part 1 */
/* Insert 20 table rows ~ 20 filtered index rows */
DECLARE @ITER INT = 1
WHILE @ITER < 21
BEGIN
INSERT INTO T4139_fact
SELECT 100000000 + @ITER, 100000000, 100000000, N'ZZZ', 1, N'ZZZ', 100000000, GETDATE(), GETDATE();
SET @ITER = @ITER + 1;
END
/* 5100 + 1500 + 20 = 6620 table rows; 510 + 150 + 20 = 680 filtered index rows */
SELECT
(SELECT COUNT(*) FROM T4139_Fact) AS table_rows,
(SELECT COUNT(*) FROM T4139_Fact WHERE ([Bit__1]=(1) AND [Nvarchar_50__1] IS NOT NULL)) AS filtered_idx_rows;
Since we've added 1520 rows to the table, will the test query lead to an auto stats update?
/* Auto-update for filtered index stats yet? Nope.
But all of the column stats have updated now. */
EXEC T4139_testQuery;
EXEC [dbo].[T4139_Fact__T2388_showstats];
Each of the auto-created column stats relevant to the query have now auto-updated. That makes sense, because 1520 table rows were added and 1520 was the threshold for the column stats. The filtered index has changed in a more limited manner - it increased from 510 rows to 680 rows - a net gain of 170 rows. But the threshold of 602 appeared in the plan XML.
Let's add more rows - but use [Bit__1]=(1) in all of the new rows - making them all qualify for the filtered index.
/* Insert 4499 table rows ~ 4499 filtered index rows */
DECLARE @ITER2 INT = 1
WHILE @ITER2 < 4500
BEGIN
INSERT INTO T4139_fact
SELECT 200000000 + @ITER2, 200000000, 200000000, N'ZZZ', 1, N'ZZZ', 200000000, GETDATE(), GETDATE();
SET @ITER2 = @ITER2 + 1;
END
/* 6620 + 4399 = 11019 table rows; 680 + 4499 = 5179 filtered index rows */
SELECT
(SELECT COUNT(*) FROM T4139_Fact) AS table_rows,
(SELECT COUNT(*) FROM T4139_Fact WHERE ([Bit__1]=(1) AND [Nvarchar_50__1] IS NOT NULL)) AS filtered_idx_rows;
So we've added 4499 rows to the table and the filtered index. That's a lot more than the 602 specified as the threshold. Think the filtered index stats have been updated yet?
/* Auto-update for filtered index stats yet? Nope.
But all the column stats have updated _again_ */
EXEC T4139_testQuery;
EXEC [dbo].[T4139_Fact__T2388_showstats];
Well... let's add just one more row.
/* Let's add just one more row */
INSERT INTO T4139_fact
SELECT 300000000 + 5120, 300000000, 300000000, N'ZZZ', 1, N'ZZZ', 300000000, GETDATE(), GETDATE();
/* 11119 + 1 = 11120 table rows; 5179 + 1 = 5180 FILTERED INDEX ROWS */
EXEC T4139_testQuery;
EXEC [dbo].[T4139_Fact__T2388_showstats];
Whew! Finally! The filtered index stats finally updated after 4670 inserts into the filtered index. That's a lot more than the 602 prescribed by 20% + 500 - which also appeared in the trace flag 8666 information in the actual plan XML.
I was hoping to uncover a pattern to make filtered index stats auto-update more predictable. But I'm not close to finding the pattern yet. At this point, its not close to as predictable as auto-update of column stats. But, when prioritizing stats for manual update, it looks to me like filtered index stats should be high on the list compared to column stats which may more easily benefit from auto updates.
No comments:
Post a Comment