SQLskills Immersion Training – Day 3, Query Plan Analysis, CPU Performance Tuning, Plan Cache and Index Analysis

This was my favorite day of training so far.  Most of the topics I am already very familiar with.  This isn’t a waste of time though. My learning style works by constructing a large framework of basic information then filling in that framework with more detailed and esoteric info.  I already had a very solid base in these areas and the training today helped fill in some of the blanks.

Yesterday Joe began a module teaching about query plan analysis. He continued that today teaching about the more common operators, the different join types and when they are best used and which operators may indicate you are having performance problems.  He went into depth on hash algorithms, MAX DOP and parallelism, cardinality estimation problems, and much more.  The last slide of the module was his “watch” list of things he looks for when analyzing query plans.  I’m happy to say that a lot of what I looked for is already on the list.  I’m also happy to say that I learned a few more things to look for that I hadn’t considered before or had forgotten.

Getting repetition and reinforcement of things already learned is another reason these classes are good.  Most of what is being taught, 95% at least, I’ve been exposed to before either though training classes, videos or whitepapers. But a lot of it doesn’t get absorbed the first or second time.  The repetition helps to solidify that in memory while getting some new tidbits to add to it.

Joe taught about how to troubleshoot CPU performance and did an introduction to resource governor.  This can tie into the previous module because in many cases the causes of high CPU utilization can be discovered from query plan analysis.

To end the day Kimberly began teaching a module on plan cache and index analysis.  This is a good follow up on the same material from IE1.  I took that class last year and don’t remember exactly what was taught but there appears to be lot of repeat information from IE1. It is hard for me to say what is different since it has been a year.  The info is still very good though. The repetition is obviously good since I have a ton of notes.

Like the previous modules I discovered I was already doing a lot of things they recommend but added to my knowledge and a few new tricks.  That statement appears to be the broken record of the blog this week.

Update 8/23/2012 – Here are links to each day:
Day 0
Day 1
Day 2
Day 3
Day 4
Day 5