A long time ago, I used to publish Connect digests – little posts that highlighted a few bug reports or suggestions on Connect that I thought deserved more attention. Now, I will say this: I am not really a big fan of a system where the person with the most friends willing to vote gets his or her way, because the SQL Server team should be able to ignore or defer noise, and focus on the most important and impactful bugs or suggestions. But that is not how they do thing in Redmond. So, today, I have a request: help me by voting and commenting on these three Connect items, all of which aim to improve how SQL Server statistics work.
(Note that comments hold much more weight than mere vote counts, so please state your business case, if you have one that is sharable.)
MAXDOP hint for UPDATE STATISTICS
SQL Server 2016 has added a MAXDOP hint for DBCC CHECK commands, so why not for stats updates? On partitioned tables this can have a great impact on the rest of the workload. We should also be able to override the system-defined MAXDOP for automatic stats updates, but for now I'd be happy with more control over manual statistics management. The request is captured in the following Connect item:
Let the query optimizer see partition-level stats
Erin Stellato has blogged about the benefits of incremental stats here, but really hit the nail on the head about its problems in this post: Incremental Statistics are NOT used by the Query Optimizer. Please read through that and then vote and comment on the item I just created (I can't believe I never noticed that a DCR didn't already exist for this):
Auto-stats should consider the number of rows in a filtered index/stat
Currently, relying on automatic updates to filtered indexes and statistics is like Waiting for Godot – the algorithm uses the number of rows in the table when determining the churn threshold, not the number of rows in the index. This means that most filtered indexes – and indeed the most useful filtered indexes – will never be updated automatically. (I talk about this here, and Kimberly Tripp talks about it here and here. I'm sure others have blogged about it too.) I think it's time for this to change – if you agree, please vote and comment on Joe Sack's item (the title indicates filtered statistics, but it really relates to both):
Please continue making these kinds of posts, Aaron. Maybe the "people in Redmond" are a little too high on cloud, Columnstore and Hekaton to notice all the low hanging fruits in the basic engine that most customers are still using.
*Many* such fruits. What could be hard about making update stats respect MAXDOP?? Super easy to implement since update stats is driven through the query processor which is already capable of implementing a MAXDOP. I think there's just nobody looking.
Another low hanging fruit: Move an index *including LOB data* to another filegroup. A normal rebuild WITH DROP_EXISTING strangely keeps the LOB data in place. But a rebuild on a null partitioning scheme does not. So the engine is already capable of doing this. There's probably a boolean flag somewhere that controls this behavior.
Maybe you could institute a monthly Connect roundup post series. Also encourage readers to RSS subscribe. I will support you.