Understanding DBCC SHOW_STATISTICS Outputs

Bill Wolf continues his series on statistics by looking at what DBCC SHOW_STATISTICS gives you:

When I was putting together the lesson plans for this, I wanted to make my own query for the comparisons, not borrow one from another site or blog.  Yes, I borrow plenty, but I wanted this to be mine.  When I was presenting my “code tuning” class, I had recently upgraded my instance from 2012 to 2017.  I had also put my database into 2017 compatibility mode.  I had used this query to show that unions that are intensive can cause issues with tempdb and cause spill over.  To my “joy”, when I ran the query in the class I did not get the tempdb spillover.  And right then I realized that I was not in Kansas(2012 compatibility) any longer.  But this proved to be opportunistic for the statistics/optimizer comparison.

Read on for a discussion of the cardinality estimator as well.

Related Posts

Cloning And Columnstore Statistics

Niko Neugebauer points out a fix in SQL Server 2019: I have a huge love for the DBCC CLONEDATABASE command – it has been made available (backported) to every SQL Server version starting with SQL Server 2012, since the original release in SQL Server 2014, while being constantly improved in the Service Packs and Cumulative Updates. This […]

Read More

The Value Of Auto-Created Statistics

Brent Ozar is here to praise statistics auto-creation: Let me rephrase: before you even start playing around with statistics, make sure you haven’t taken away SQL Server’s ability to do this for you. I like to make fun of a lot of SQL Server’s built-in “auto-tuning” capabilities that do a pretty terrible job. Cost Threshold for […]

Read More

Categories

January 2018
MTWTFSS
« Dec Feb »
1234567
891011121314
15161718192021
22232425262728
293031