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

SQL Server 2014 RTM Ignores Trace Flag 2549 - Bad News for VLDB checkdb

Update 9/30/2014: Rats - I was hoping more folks would be ranting about this one.  There is a connect item now, with two votes!!  Maybe I'll have to start reaching out to SAP folks...
 https://connect.microsoft.com/SQLServer/feedback/details/982532/sql-server-2014-rtm-ignores-trace-flag-2549

Full disclosure: This post reports behavior observed on Microsoft SQL Server 2014 - 12.0.2000.8 (X64).  This is SQL Server 2014 RTM.  I believe the behavior represents a significant regression in "dbcc checkdb with physical_only", thus I expect it may change soon.  When that happens, I will update this post with a link to the kb article addressing such. This post is a followup to a previous post found here:
http://sql-sasquatch.blogspot.com/2014/08/sql-server-2014-mount-points-checkdb.html

Running integrity checks on relational database data is important.  Its so important that I feel pretty strongly that it should be included in a lot more performance and benchmark scenarios than it currently is.

Kevin Closson* often points out two things which I fully agree with - the following is loose paraphrase:
1. A platform that can service OLTP workload at high volume with great performance can probably service almost any workload at high performance.
2. If you think you have an exclusively sequential workload at the storage device level, you're probably wrong.

Still... integrity checks are important.  Database backups are important.  Neither of them are random workflows from the database perspective - certainly not in the same way that user-driven workflow can be considered random.  So... just because your system achieves super-awesome response times at high levels of transactional throughput, I'm not willing to assume that integrity check or backup on the system is optimized.  You don't know until you know, and integrity checks and backups are both very important things to know.  OK... enough soapbox.

I'm going to present the events and graphs here in an order different than they occurred. I'm doing this for two reasons: 1) I think it will make the behavior and its import more clear 2) I think I look smarter if I pretend that I performed the tests in the order that they are most easily explained, and skip the bumbling in-between that occurred as I struggled to understand exactly what was happening.

First, using a SQL Server backup to NUL is often proposed as a means to profile read behavior of a system.  I think its a fine tool to include in disk behavior profile of a system - and doing so can give a strong start to optimizing backups on the system.  So I got this system... 2.25 TB database, backup to NUL in 1 hour, 8 minutes, 59 seconds.





 Read more about the backup to NUL on this system here:
http://sql-sasquatch.blogspot.com/2014/08/why-read-bytessec-valleys-in-sql-server.html

There are eight equally sized database files, each file on its own LUN.  When these LUNs are associated with their own Windows drive letters, 'dbcc checkdb with physical_only' with maxdop 4 looks like this.



What if this same database is attached accessing the files via mount points rather than individual drive letters? (Windows Server 2012 allows mount points to have drive letters simultaneously associated with them.)  In this case, trace flag 2549 has been set so that each database file should be considered on its own drive path, rather than the default behavior of considering only each distinct Windows drive letter to be a separate drive path.



 Yikes!!!  Read throughput is way down, and elapsed time is much longer.  There's a particular stretch of time that is especially intriguing, highlighted below.



Huhh.  For a significant period of time, all of the read bytes are coming from one LUN at a time.  Lets just skip over the hours this completely confused me, and the email I sent to my colleagues complaining that things didn't make sense anymore :-)

Lets get right to the part where I decided to run 'dbcc checkdb with physical_only' against the exact same database, without trace flag 2549 enabled.



So with or without trace flag 2549, read throughput of 'dbcc checkdb with physical_only' looks nearly identical... and both look extremely different from the same operation when the database files are each accessed via their own drive letter.

Its because SQL Server 2014 RTM is ignoring trace flag 2549.  And that's a shame.  If there are ANY databases out there that I really want for checkdb to complete, its those which are so large that they must use mount points due to running out of Windows drive letters.

My plea to Microsoft is to enhance the checkdb/checktable read planning, rather than simply fixing the regression.  I think this behavior is too important to relegate to a trace flag - leave it a trace flag and it seems vulnerable to being left off test plans.  With the advent of sys.dm_io_virtual_file_stats, there's no need for checkdb/checktable to guess which files are on separate LUNs: SQL Server knows. 


*****

So maybe you are curious about the sections highlighted in yellow above, where large amounts of data are being read from one drive at a time when the database files are accessed via mount points.  It kinda makes sense since SQL Server checkdb is thinking "well, looks like its all on the C drive, so have to plan all reads as if going through a single disk service queue."  

But if that was all there was to it... wouldn't other large portions of the checkdb look similar? Well... first of all, just wait til I post some graphs from adding trace flag 2562!! :-)  But also, consider the highlighted portion below in the "good" graph from accessing all files via their own Windows drive letters. 





Recall how checkdb during the checktable phase batches hobts - taking a table with all its hobts until the total count approaches but doesn't exceed 512.  After extensive testing with checktable against individual tables, I'm convinced all the yellow highlighted areas are parts of a batch which happen to include a highly contiguous, minimally fragmented ~500 gb table which includes a varchar(8000).  I'll leave that explanation for another day.

*****
*Kevin Closson is a performance engineer active in blogging (http://kevinclosson.net/) and on Twitter (@kevinclosson).

Many of his blog posts are related to Oracle, typically running on Linux x86 servers and pursuing maximum throughput at low latency ("low latency commercial computing").  But even if you primarily deal with AIX on IBMPower, or primarily deal with SQL Server... I still recommend checking out his stuff.  I read Kevin's blog and many others, even if they are not directly related to my current projects, to learn new testing and analysis methods I can apply to the platforms and projects I am working on.