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…

Scripts:

• 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.