I think this is how my mom would have felt if I went to college.
You see, once you stop cargo culting around index fragmentation, and disk queue length, you start to realize which things are actually important to query performance.
Things like statistics, and cardinality estimation. And, sure, having the right indexes can help with that, too.
Annoyance in chief
Microsoft, in the past, has surfaced Proustian levels of information about indexes, and fragmentation, and they published numbers at which you should reorganize and rebuild your indexes on those 5200 RPM drives to keep disk queue lengths low.
This has led to an astounding, if not absurd, number of questions about index maintenance.
It doesn’t help that Microsoft hasn’t budged from the 5%/30%/1000 pages numbers, the same way they haven’t budged from the MAXDOP/Cost Threshold for Parallelism defaults despite modern single CPU sockets that have more cores than entire server rooms had when the defaults were decided on.
More information about statistics has started to get added and surfaced. Helpful information!
Starting with the vNext DMV sys.dm_db_stats_histogram.
Now, something else cool got added. Statistics information resides in query plans, and not just when you use a special Trace Flag.
Now, it’s not quite as verbose as other statistics information that you can get with certain Trace Flags turned on (2363 if you’re on 2014+, 9202/9292 previously), but it’s a real good start.
You can immediately get an idea of how stale your statistics are via the last update and modification values, and how high or low your sampling rate is.
And yes, I’ll be surfacing this in sp_BlitzCache. Just, you know, you’re gonna have to upgrade to see it.
Won’t you please just upgrade?
I wanna hug whoever added this.
Fair warning: if I find out who you are, and you’re at PASS, the hug is happening.
Thanks for reading!