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.