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?!?
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)
I made some interesting observations here.
SQL Server trace flag 2388 shows negative numbers of deletes/inserts for a filtered index since previous update?
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...
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)
Filtered Index execution plan is not optimized(Closed as won't fix)
Covering index produce a Lookup when it should not (Active)
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)
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?