SQL Server 2017: Statistics Information Comes To Query Plans

Warm fuzzies

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.

Or something.

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.

Finally

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.

Diddly!

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?

Hugs

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!

, , ,
Previous Post
Will SQL Server Always Do Index Seeks on Numbers?
Next Post
Breaking: SQL Server 2016 SP1 CU2 Supports ENABLE_PARALLEL_PLAN_PREFERENCE Hint

1 Comment. Leave new

  • Ernest Libertucci
    November 2, 2018 8:41 pm

    I can’t say how great this is. I was down the rabbit-hole of performance tuning for the past few days and this would have been super-useful. I’m just glad that I learned about it and can use it potentially as a reason to upgrade.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}