SQLskills Insider Session 9: Understanding Statistics - MORE on the Histogram!
Well… we ended taking the whole 2 hours with tons of questions and super fun tangents. I’ve written up all of discussions / demos as scripts but there’s still so much more to discuss. So, for Part 2 we’ll look more histograms, histograms where data is uneven and start to discuss problems with histograms! Bring your histogram questions too – going off script and into all sorts of tangents was incredibly fun in session 8!
Session #9: Resources
Well, after last week, I knew we were going to go into tangent-land! It was quite fun. I still created a few slides and did a review of what we looked at last week. After the session, I ended up creating more formal demos around all of the discussions. What you’ll find in this zip is a bit more complicated…
- 00 RestoreCredit.sql is obvious… but, you will want to restore credit between demos to make sure that you have the correct setup
- 01 ReadingHistograms.sql is for reading data that’s not evenly distributed (like we did in the last session). You’ll look at corp_no as well as distinct value estimation from the density vector
- 02 CatalogViewQueriesForStats&Indexes.sql is the sys.stats queries and a few other helpful details
- 03 WhichStatisticsGotUsedLegacy&NewCE.sql shows how to see which statistic was used by the query
- 04 FilteredIndexesStoredProceduresRecompilation.sql is why you shouldn’t use CREATE or EXEC with recompile in production and why you should use OPTION(RECOMPILE). You can see how it does better optimization with the statement-level option rather than the procedure-level. And, it’s not as limited wrt troubleshooting!
- 05 HistogramProblems_ExistenceHypothesizing.sql this was a concept we discussed, and I said I needed to think about it more… I did. The script has a couple of scenarios.
- Directory: VLTs has an SSMSSLN for the additional directories. This is for the VLT scenario and ties in with the demos I did in the PASS session I recommend watching: Skewed Data, Poor Cardinality Estimates, and Plans Gone Bad.
Resources for this SQLskills Insider Session: download
See our upcoming Insider Sessions here.
Kimberly L. Tripp
President and Founder, SQLskills
Read my full bio here: Kimberly L. Tripp and about my Celebrating 25 Years of SQLskills here: https://www.sqlskills.com/about/meet-the-team/celebrating25years/