This video is an hour-long but well worth sharing with you today. One of the main takeaways from this video I want you to have is the fact that troubleshooting performance is not always rocket surgery. When you watch this video and listen to Conor Cunningham talk about some of the real world examples he has faced you will see what I mean.
I also liked how they help you to understand the concept of how to “bucketize” performance issues. It goes something like this:
Are all queries affected, or just a subset of queries affected?
If all queries are having performance issues then you will want to examine settings that affect the entire instance, such as memory settings, or high CPU utilization. You will want to do this first before trying to examine any one particular query.
If it is only a subset of queries (or users, or a particular application) then you will want to focus your efforts on those queries first. Otherwise you will be wasting time trying to fix one query without addressing the root cause of the performance issue affecting all queries. [A great example of this for my customers has to do with virtualization: why waste time trying to tune one query when the reason for the slowness is because your memory settings have been dynamically changed? Better to spend five minutes talking to your server admins about the memory for your guest than to waste time trying to get a query to suddenly run faster with less memory available.]
Just being able to diagnose “all versus some” in the first five minutes of triage in a production down situation can save you a lot of time as you begin to form your action plan to correct problem and bring performance back to within acceptable limits. Set aside some time this week to watch this video and learn more.
