SQL University – Performance Tuning Week

Welcome back to another week of SQL University where the topic is Performance Tuning.

Show of hands: how many people think performance tuning is hard? OK, put your hands down, you are sitting at a computer and it looks really weird. Anyway, many folks think performance tuning is hard and here are the main reasons:

• You need to know a lot of different things (network, SAN, hardware, IIS, AD, wtc.)
• You are almost always in a reactive mode, so time is a factor and most folks don’t like being rushed for answers
• Proactive tuning is often a low priority (don’t bother working on that Johnny, nobody is complaining about it yet)
• Even if you have the time and the knowledge, you don’t necessarily know where to begin

My goal today is to help you find a way to make your life a little bit easier. I have a talk titled “Performance Tuning Made Easy” that seems to be fairly popular these days and will try to break it down for you in this post. Go ahead and read that post and I’ll wait. OK, readynow? Let’s begin.

tempdb

The first thing you need to do is to have awareness. There are many, many things going on in your shop right now. Some of them you know about, others you know nothing about. You don’t have a chance when it comes to performance tuning unless you have accomplished these two items: define and measure.

You need to define performance problems. The easiest way to do this is to head over to MSDN and write down the acceptable thresholds for performance. A good starting point is the 2005 Waits and Queues Whitepaper (I hope this gets updated for Denali). Another good starting point is to talk to your end users about the performance they expect for the various systems that they use. The point here is for you to define what is good and what is not good. That way the next time somebody stops by and says “hey, this is bad” you can refer back to your definitions and say “hey, not according to what we agreed on previously”.

You also need to be measuring performance. You can collect data by using native tools, or 3rd party vendor tools (and I can recommend one), or a combination of both. Whatever you choose is up to you, but if you are not measuring for performance on a regular basis then you are going to have trouble answering a very simple question: “is this a problem?” The trick here is to make certain you are measuring against your definitions. It is easy to overlook things but if you are not measuring against a definition then you are going to have trouble helping to identify problems (other than having your phone ring, of course).

msdb

Now we need to help you get the job done. Once you have your definition and measure in place you can go about analyzing the details. Most of the time this will be done in a “reactive” mode, but that is OK because if you have your definitions and measures it allows for you to be able to arrive at an answer faster than not having them.

The trick here is this: When performing your analysis make certain you refer back to your definitions. This is how you decide if something is a problem or not. If a developer opens up Task Manager and decides that the server is paging all of its memory to disk you need to be able to quickly show to them that the measures you have in place indicate there is not a memory problem.

model

Here is where you need to lead by example. You want to be able to suggest improvements and need to do so in a way that makes people feel GOOD about your suggestions. So instead of saying “I see what’s wrong with your code”, try saying things like “I think we might see an improvement if we could make a change here”. You are often going to be handed issues with code and queries that you have never seen before and how you react to these situations will dictate if people will want to seek out your advice or just avoid you at all costs.

master

This is where you enter a zen-like state of enhanced consciousness. How do you get there? One word: proactive. You have your definitions in place. You have your measures. You are able analyze and suggest improvements. And you are able to do all of this before your phone rings.

That’s when you know you are a master at performance tuning. When you fix problems before they become problems.

resourcedb

Here is a list of people that I consider to be at the top of the game right now with regards to performance tuning. They are in no particular order, just people that know what they are doing and are willing to help you understand as well:

• Grant Fritchey (blog | @gfritchey)
• Kimberly Tripp (blog | @KimberlyLTripp)
• Paul Randal (blog | @PaulRandal)
• Gail Shaw (blog | @SQLintheWild)
• Michelle Ufford (blog | @sqlfool)