Blog

Take it from me, performance tuning ain’t easy. To do it well you need to use all of your technical skill, plan strategically, and communicate your recommendations effectively. You also need to inspire adoption. After all, what good is a plan to change a system if you can’t convince anyone to go along with you?

We performance tune all sorts of environments— from OLTP SQL Servers running 30K+ batch requests per second to multi-terabyte warehouses. We use a wide base of knowledge about database systems to performance tune these environments, but we always make sure that our process avoids three strategic mistakes.

Mistake #1: Specialist Syndrome

It’s tempting to take on a performance problem solo with your core team of super-technical database engineers. It feels efficient, focused,  controllable. This is a strategic mistake– by doing this you exclude input from very important parts of your team.

When working with clients we bring together DBAs, developers, managers, sysadmins and business users. We show the whole team how to analyze the database environment from the storage subsystem, hardware, and OS configuration up through the SQL Server configuration and query tuning. Everyone on the team gets a broader perspective about the application environment and how bottlenecks manifest between storage, the database, the application, and the users. Everyone learns more about the problem and explores potential solutions.

We take the whole team through the process, no matter what level they’re at with SQL Server. This is tricky, because it requires explaining complex topics in everyday language. We do this because it’s important, and everyone is completely capable of getting these concepts and understanding performance bottlenecks. Everyone has different pieces of the puzzle and will play roles in short or long term changes.

I wish I knew how many times I’ve heard a business user say, “You guys are still running that? We stopped using that last year.” Unfortunately, I’ve lost count.

Luckily it doesn’t have to lie still.
[This image has been remixed]

Mistake #2: Doing Daily MRIs

Database administrators and developers LOVE routines. They naturally crave activities they can do daily. Performance tuning works best in larger phases, though: focus on a high intensity period of analysis. Then follow it with longer periods of implementing changes, while measuring adjustments in the system.

We work with clients in four day bursts of high-intensity SQL Server workouts because this pattern is effective. We first identify the big pain points we’re going to resolve. We then work through a wide ranging discovery process, put together recommendations, and take the group through training as a recap. We explain context around bottlenecks in the database server and how they relate to the client’s pain points. We plan out recommendations for the next week, the next month, and the next quarter to address those pains.

Following this intense analytical period the client teams disperse. People take what they’ve learned back into their ordinary jobs and execute on their tasks.

Think about performance tuning as a big medical exam: you’re doing bloodwork, getting XRays, and thinking deeply. You reassess your big pain points periodically and use that big burst of activity to communicate across teams and build a larger plan. Doing this intermittently allows you to lead your system to change. Trying this daily turns you into that person always sending irate emails.

Mistake #3: Old Medicine

Whether or not you’re calling in a consultant, you need another pair of eyes on your performance tuning techniques. We’ve refined our process across many clients, and we’re always thinking of new ways we can convey ideas and strategize solutions together. Inside our company, we’re always asking each other, “How can we do this even better?” Just like doctors, we can never accept that we know the best answer for all time to a pain point. We have to be open to new treatments.

Your performance tuning techniques need to evolve. This happens because of changes in your environment, software, data patterns, user patterns, and business processes. Document how you do performance tuning. This includes methods for identifying your bottlenecks and pain points as well as measuring improvement from changes in the system. It also includes defining the team, investigating pain points, planning a solution, and communicating and tracking recommendations.

How you write this down doesn’t matter. What’s important is that each time you use the process, you get feedback on how to make it better and make notes. Since you’re doing this intermittently, those notes are valuable!

If you look back at your method after a year and nothing has changed, you’re probably doing it wrong. By its very nature, performance tuning involves growth and learning. And that’s exactly what keeps it fun.

↑ Back to top
  1. Looks like that DB would fit better in the MRI machine if you lay it on its side a little.

    Nice post. I’m biased to healthcare analogies so I extra like this post.

  2. Pingback: Log Buffer #290, A Carnival of the Vanities for DBAs | The Pythian Blog

  3. Pingback: Atom Wire » Blog Archive » Log Buffer #290, A Carnival of the Vanities for DBAs

  4. Hi Kendra,

    I expect this post to be much more technical – I’m not saying it’s bad, but it’s just about good practices in performance tuning.

    About #3 – Old Medicine: I think Old Medicine is here to stay especially when optimizing SQL queries (which is something I assume you do a lot).

    By the way, a technical post on optimizing SQL queries would be very insightful.

    • Hi Fadi,

      You’ve hit exactly on my point– see #1. The reason many people fail at performance tuning is that they focus so much on the technical part of the process that they try to solve the wrong problems, and get nowhere.

      Yes, technical expertise is also necessary when doing performance tuning. Do people always miss the same thing when it comes to the technical tools they’re using? Nope, because they have different root causes.

      Do people commonly fail at performance tuning because a big problem with their process? Yes, hence this article.

  5. Kendra, I agree totally. You can have the perfect application, if that were to exist, and without trying to figure out how people will use an application, any performance tuning will fail.

    Another issue I see is the over reliance on tools, such as FogLight and Idera. They can be great tools but they should not be the entire breadth of your performance tuning toolbox. To me, that would be the same as always turning to your old medicine. New skills must be gained for new technology and if you refuse to change your process, you are in the wrong field.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php