SQL Server Trace flags 2389/2390/4139 ~ An Unadvertised Fix ~ Quickstats Queries for Filtered Indexes?

*****Update 20170103*****
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);

For SQL Server 2014 SP1, we see two different quickstats queries.  One query is issued twice - the query for NUM2, which is the first key of the nonfiltered index.  The query for NUM1 - which the filtered index has as leading column - occurs one time.

*****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);

So when *does* a SQL Server filtered index get its stats auto-updated?

*** I thought I'd find a formula or pattern for auto-update of filtered index stats.  Not yet. ***

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.

About that SQL Server quickstats update on a filtered include index...

*** Update 23 December 2016 ***
I mention Michael J Swart's Connect item below.  What I didn't notice earlier was the 'Details' link in the Connect item - behind which was his code for reproducing the issue.  Also there in his details is the *big clue* .  When a quickstats query is issued for filtered index stats... it has no filter!  That forces a full table scan unless a nonfiltered index with the same first column happens to be around to help that quickstats query out.
Today we learned that in SQL Server 2016 sp1 filtered indexes are exempted from quickstats queries.  I put some code in Connect item 2528743 comments that can be used to explore the behavior.
******


Still working to recreate the "obnoxiously long plan compile" in a lab.
A problem I first blogged about here.
Three Minutes to compile a very simple #SQLServer Query plan?!?
http://sql-sasquatch.blogspot.com/2016/11/three-minutes-to-compile-very-simple.html

I remembered exchanging tweets with Michael J Swart (@MJSwart) about something similar earlier this year.  Here is the Connect item he filed.

Trace flag 2390 can cause large compile time when dealing with filtered indexes. (Active)
https://connect.microsoft.com/SQLServer/feedback/details/2528743/


I made some interesting observations here.
SQL Server trace flag 2388 shows negative numbers of deletes/inserts for a filtered index since previous update?
http://sql-sasquatch.blogspot.com/2016/12/sql-server-trace-flag-2388-shows.html

And I started to formulate a suspected model for what was going wrong here.
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  

One of the remaining pieces of work is to show that there is a problem with the quickstats update.
I now think that one or all of the Connect items listed below are involved.  So the theory is that with trace flag 4139 and/or 2390 enabled, and a filtered index branded "unknown", a quickstats update is triggered.  And the quickstats update ends up using a horrible, horrible plan because of an issue like those detailed in the three Connect items below.  If the optimizer happens to load such a filtered stat multiple times, the quickstat update is performed multiple times - amplifying the time added to the query compile.

Filtered index not used and key lookup with no output (Closed as won't fix)
https://connect.microsoft.com/SQLServer/feedback/details/454744/

Filtered Index execution plan is not optimized(Closed as won't fix)
https://connect.microsoft.com/SQLServer/feedback/details/643850/

Covering index produce a Lookup when it should not (Active)
https://connect.microsoft.com/SQLServer/Feedback/Details/1419924/

Aha! Miloš Radivojević (@MilosSQL) to the rescue!  In this blog post, Miloš uses an extended event session with event sp_statement_completed added to retrieve the statement for the quickstats update.

Beyond Statistics Histogram – Part 2 (TF 2390)
https://milossql.wordpress.com/2014/11/24/beyond-statistics-histogram-part-2-tf-2390/

When the optimizer handles the stats for his nonclustered index without a filter, the SQL statement retrieved is of the following form.

 SELECT StatMan([SC0]) FROM (  
   SELECT TOP 1 [custId] AS [SC0] FROM [dbo].[Orders] WITH (READUNCOMMITTED) ORDER BY [SC0] DESC)  
 AS _MS_UPDSTATS_TBL OPTION (MAXDOP 1)  

But what if the index is filtered?

SQL Server Ascending Key Stuff: UPDATE STATS when nothing has changed can lead to a surprising brand...

*****Update 20170103*****

I think that the "surprise" of index stats lead column from one brand to another is in part due to sampled updates rather than FULLSCAN updates.

When I get a chance to revisit testing, I'll be comparing FULLSCAN to sampled updates, with out-of-range inserts and when the underlying data is unchanged.

*****


Here's some background on trace flags 2389 & 2390 and how they address the 'ascending key' problem.

Ascending Keys and Auto Quick Corrected Statistics
https://blogs.msdn.microsoft.com/ianjo/2006/04/24/ascending-keys-and-auto-quick-corrected-statistics/

Seek and You Shall Scan Part II: Ascending Keys
http://sqlmag.com/sql-server/seek-and-you-shall-scan-part-ii-ascending-keys

Note that trace flags 2389 & 2390 only influence the optimizer for indexed columns. Should be a seek to get to the maximum value, rather than the full table scan needed to ensure max value for a non-indexed column is known.

But way back when there was a compile time issue with trace flags 2389 & 2390.
FIX: You may notice a large increase in compile time when you enable trace flags 2389 and 2390 in SQL Server 2005 Service Pack 1
https://support.microsoft.com/en-us/kb/922063

Check out the cause specified in kb922063:
"This problem occurs because SQL Server performs a scan of the appropriate column. This scan takes extra compile time."

I've been chasing down an issue for a few weeks where query compile time for some queries has unexpectedly ballooned to several minutes.

I first mentioned the problem here...
http://sql-sasquatch.blogspot.com/2016/11/three-minutes-to-compile-very-simple.html

Started some useful investigation here...
http://sql-sasquatch.blogspot.com/2016/12/sql-server-trace-flag-2388-shows.html

Now I'm circling around diagnosis and remedy.

Let's look at trace flag 2388 show_statistics information as a nonclustered index grows - first without a filter, then with a filter.

 IF object_id('[dbo].[test2388]') IS NOT NULL DROP TABLE test2388  
 CREATE TABLE test2388 (col1 INT)  
 CREATE NONCLUSTERED INDEX nci_col1 ON test2388(col1)  
 DECLARE @int INT = 12  
 WHILE @int > 0  
    BEGIN   
      INSERT INTO test2388  
      SELECT @int;  
      SET @INT = @INT - 1;  
      IF @INT % 3 = 0   
           BEGIN   
              UPDATE STATISTICS test2388(nci_col1)  
              DBCC TRACEON(2388);  
              DBCC SHOW_STATISTICS('test2388','nci_col1')  
              DBCC TRACEOFF(2388);  
           END  
    END  

So 12 rows were inserted - and after each 3 inserts, stats were updated.  End result was the leading index key (in this case the only key) was branded 'stationary'.  Stands to reason, since the key value kept decreasing as the WHILE loop iterated and INSERT commands continued.

But what happens if we do one more UPDATE STATS without changing the data in the table?

 UPDATE STATISTICS test2388(nci_col1)  
 DBCC TRACEON(2388);  
 DBCC SHOW_STATISTICS('test2388','nci_col1')  
 DBCC TRACEOFF(2388);  

Huh.  The brand switched from stationary to 'unknown'.

Lets perform the same type test with a filtered index.

 DECLARE @int INT = 15  
 WHILE @int > 1  
    BEGIN   
      INSERT INTO test2388_filtered  
      SELECT @int;  
      SET @INT = @INT - 1;  
      IF @INT % 3 = 0   
         BEGIN   
           UPDATE STATISTICS test2388_filtered(nci_col1_filtered)  
           DBCC TRACEON(2388);  
           DBCC SHOW_STATISTICS('test2388_filtered','nci_col1_filtered')  
           DBCC TRACEOFF(2388);  
         END  
    END  

Cool.  Also ended up with a 'stationary' key.

Let's do one more stats update, as we did for the index without a filter.

 UPDATE STATISTICS test2388_filtered(nci_col1_filtered)  
 DBCC TRACEON(2388);  
 DBCC SHOW_STATISTICS('test2388_filtered','nci_col1_filtered')  
 DBCC TRACEOFF(2388);  

Look at that!  Oh... wait a minute.  Still stationary.  Let me try that again.

 UPDATE STATISTICS test2388_filtered(nci_col1_filtered)  
 DBCC TRACEON(2388);  
 DBCC SHOW_STATISTICS('test2388_filtered','nci_col1_filtered')  
 DBCC TRACEOFF(2388);  

Aha! That's what I wanted to show*.  Yes - a filtered index that is branded as 'stationary' can be rebranded as 'unknown' if stats are updated when no changes to the underlying table have been made.

That's not too surprising since that's what happened for the nonclustered index without a filter above.  But the optimizer engaging its trace flag 2390 quickstats behavior for an 'unknown' leading index column might be a surprise here.

Combine that with a problem in quickstats behavior for filtered indexes of large tables and query compiles can start to take minutes.  (The longest I've seen so far has been about 34 minutes on a table of roughly 600 million rows).

But the actual work of showing evidence of a problem with quickstats for filtered indexes on large tables will have to wait for another day.

*I don't know why it took 2 updates with no data changes (after the 4 updates with changed data) for the filtered index key to change from 'stationary' to 'unknown'.  I've got to leave that question for someone more knowledgeable than myself.