Aaron Bertrand

Please help improve SQL Server statistics!

November 12, 2015 by in SQL Statistics | 1 Comment
Auto-correlate and track performance for all of your Windows processes and services, right now or historically.  More
SentryOne Newsletters

The SQLPerformance.com bi-weekly newsletter keeps you up to speed on the most recent blog posts and forum discussions in the SQL Server community.

eNews is a bi-monthly newsletter with fun information about SentryOne, tips to help improve your productivity, and much more.

Subscribe

Featured Author

Paul Randal, CEO of SQLskills, writes about knee-jerk performance tuning, DBCC, and SQL Server internals.

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