Wanted: Practical SQL Server Performance Monitoring and Tuning Skills

Ask a DBA which area of their job they wish they had more hands-on experience in and most will tell you performance tuning. Performance problems affect all SQL Server environments at one point or another, and if you aren’t sure which areas of your SQL Server environment you should be regularly monitoring or where to look if trouble arises, you’re going to be spending way too much time trying to tune your SQL Server performance.

This is where SQL Server performance tuning expert Andrew J. Kelly’s Practical Performance Monitoring and Tuning Workshop comes in. He is teaching a series of hands-on training and lab sessions that show you the practical aspects of performance monitoring and tuning, with the goal of reducing the time it takes you to troubleshoot performance problems or optimize performance in your environment. I recently spoke with Andrew to get a better understanding of what the three-day workshop will cover and what the benefits are of the in-person training and lab sessions.

What are some of the main topics you’re planning to cover during the three-day workshop?

Andrew: We’ll cover a lot of topics during the three-day session, but one of the key areas, of course, are the file and wait stats; I always like to cover those. The query plan cache and plan reuse and storage and IO are always hot topics as well. In reality, we are going to cover all of the relevant areas that a DBA should be monitoring or may expect to get performance issues in. I haven’t counted them up, but I think there are maybe 12 or so different high-level topics.

What level of SQL Server experience should attendees have?

Andrew: They should have a good grasp on the overall workings of SQL Server, but they don’t need to be an expert in any particular area. Having the ability to read and write Transact-SQL will certainly help so that they can understand the code that we’re doing. But really an introductory level to the DMVs is kind of all they need because we’re going to cover all the relevant components.

What level do you consider the content to be? You mentioned that there will be about 12 high-level topics, but I take it you’ll dive into some of these topics in more detail?

Andrew: Remember, this is a class on the practical aspects of performance monitoring and tuning. So what that means is we’ll cover some areas in great detail and other areas in lesser detail than you might find in a similar course. For instance, we may get into enough detail about how an index or a B-tree works under the covers, but that’s really so the students can fully comprehend the concepts and techniques that we’re going to be covering later when we’re talking about the monitoring and tuning of them. But we won’t go to the level that some people might go to such as looking at the bits on a particular page. Those are the kinds of the details that for the most part are not really part of a DBA’s day-to-day tasks and not required for most performance tuning aspects. So we’re going to try to stay at the practical level whenever possible, and in some cases that’s deep and in other cases it’s high level.

What do you consider to be the benefit of taking a three-day workshop on performance monitoring and tuning?

Andrew: I think the biggest benefit is that you get more one-on-one time with the instructor than in a conference or even a one-day class. There’s just more time to cover the topics than you’d normally have. But you also get the benefits of hearing real-life examples related to performance tuning, not only from me but from other students in the class as well. The three-day event gives you the time to look at the examples in a lot more detail than say a seminar, and you’d be surprised at how much information you can obtain just from listening or being an actual part of the discussion.

And I’m sure the labs help with that, as well.

Andrew: Yes, some people don’t grasp it until they run the commands themselves. So the labs will help with that.

Do you have any suggestions for potential attendees regarding how they can justify the money and time spent out of the office on this training?

Andrew: Yeah, that’s always a battle. I think the key really is your return on investment. A three-day class is really cheap money compared to the time and the money that people can spend troubleshooting when they really don’t have the correct knowledge about where to start in the first place. What it really boils down to is how much money is your time worth? A lot of people will spend a lot of time, or I should say waste a lot of time, troubleshooting areas that they shouldn’t even be looking into because they really don’t have a good grasp on what they should be doing. So overall on their day-to-day tasks they could do things much more efficiently and spend time working on other projects, so it’s a return on investment.

You recommend that attendees come with a machine that has SQL Server 2008 R2 or SQL Server 2008 installed on it. But if someone only has SQL Server 2005, will they be able to follow along?

Andrew: They’ll be able to follow along. There are some areas that we get into that are SQL Server 2008 features only, but a majority of the topics are relevant above SQL Server 2005 or SQL Server 2008. There may be one lab in particular that I can think of that they may not be able to do if they don’t have SQL Server 2008. For instance, the management data warehouse is a SQL Server 2008 feature only. But it’s not a deal breaker if they don’t have that because we’re still going to cover all the information. There just may be a few things that they’re not able to play with on their own.

Is there anything else that potential attendees need to know about the three-day workshop?

Andrew: I think that the biggest aspect again is just remembering that this covers the practical aspect of all of this. I try not to just preach and go through the topics and say “Okay, here’s what the topics are.” I try to show real-life, practical examples of the types of things they’ll be using or that they should be looking for. I guess if they get in that mind-set right off the bat versus say just a lecture type of a course. And again, some people really like when you get deep down into the internals, and we’re not going to get to that level on everything. We’re going to get to the level that we need to get to make this useful for people. But it is stressing the practical aspects of it. I think if they come with that mind-set they’ll have a really fun time and get a lot out of it.