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)
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.
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
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
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).
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.
Now its time to populate the table with rows, and update stats enough times to get the lead index column branded ascending or stationary.
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.
Let's check the setup.
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.
All right. Let's change the EE session state to START.
Now execute the stored proc.
Dropping the event from the session "freezes" what we have in the ring buffer.
What did we catch in our net?
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.