SQLskills Immersion Training – Day 2, Waits and Queues, Extended Events and Query Plan Analysis

Jonathan finished his introduction to SQLOS with a fire hose introduction to memory management.  This is a topic I’ve been studying for the past two years. It is not easy and there is a lot to learn.  Using various sources from the interweb I’ve developed a methodology for investigating memory pressures (internal, external, VAS) and was hoping to get some more ammunition. I was initially disappointed because this seemed to simply be a collection disconnected facts and lists of DMVs but with no information about how to detect memory pressure or troubleshoot memory problems.

However, if I peek ahead in the book I see that there is an additional section on troubleshooting memory pressures, specifically internal, external and VAS.  Excellent! This is what I would have expected to see.

Paul taught about the Waits and Queues methodology of performance tuning. This included an introduction to thread scheduling in SQLOS, latches and spinlocks.  He gave some very detailed information about the four most common waits and what those really mean.  I’ve been using the Waits and Queues methodology for a couple of years now so this wasn’t new to me but there was a lot of extra information to help round out how to use this methodology better and how to not chase red herrings.

Following the Waits and Queues section was a section about Extended Events.  In short, it was a good intro.  Given how broad this topic is there is only so much one can get in a single 2 hour session.

Having some pretty good knowledge of SQLOS memory management and Waits and Queues but almost no knowledge of Extended Events I was able to gauge how well the material targets a class with a broad range of experience.  The Waits and Queues module gave me a good bit of info to add to what I already knew but also appeared to be a decent introduction for those who don’t already use the methodology.  The Extended Events module gave me a good introduction to the topic but likely had some advanced information for those in the class who have been using them for a while.  The ability to target this broad range of experience in a class is a sign of a well-developed curriculum, I believe.

The last module of the day was the first this week taught by Joe, Query Plan Analysis.  Once again I’m pleased to see that the methodology I use in analyzing query plans is much like the one Joe presented. We ran out of class time and this topic will continue on Day 3 where Joe will get into some of the more common operators.

For bonus content Glenn Berry gave an hour long presentation about how to choose the right SQL Server hardware. He talked specifically about which processors to choose, and possibly more important, which processors not to choose.

It was a long day but packed with excellent information.

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