Aaron Bertrand

Please help improve SQL Server statistics!

November 12, 2015 by in SQL Statistics | 1 Comment
Monitor and alert on Azure SQL Database performance alongside your in-house database servers.  More
SentryOne eBooks

In these books, you will find useful, hand-picked articles that will help give insight into some of your most vexing performance problems. These articles were written by several of the SQL Server industry’s leading experts, including Aaron Bertrand, Paul White, Paul Randal, Jonathan Kehayias, Erin Stellato, Glenn Berry, and Joe Sack.

Register to Download

Featured Author

Jonathan Kehayias is a Principal Consultant with SQLskills and the youngest MCM ever.

Jonathan’s Posts

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