The Art of Performance Tuning

Andrew Pruski, 2014-09-04 (first published: 2014-08-28)

Performance tuning often gets called an art as people feel that a certain knack or innate talent comes into play. And whilst I don’t disagree that a certain level of knowledge is involved, I completely disagree that only certain people can performance tune.

Given the correct approach, anyone should be able to learn to effectively performance tune.

But how should performance tuning be approached? I’d like to take a step back from delving into specifics and define the process of performance tuning, a set of guidelines that can be used in any circumstance.

I believe that performance tuning should follow the scientific method, which is defined as:-

“A set of principles and procedures for the systematic pursuit of knowledge involving the recognition and formulation of a problem, the collection of data through observation and experiment, and the formulation and testing of hypotheses”

In practice, this can be broken down into the following steps:-

1) Observation

2) Declaration of the end goal or issue

3) Forming a hypothesis

4) Testing the hypothesis

5) Analysis of results

6) Conclusion

7) Further research

This way of approaching performance tuning comes into its own particularly when investigation is required in order to respond to issues in a live environment. The trick is to follow the method and not to go with “gut” feelings or guesses. Stick to what you know.

For example, your production server has started responding slowly, with queries taking longer than expected to complete. This could be due to memory pressure, but you are not sure. It would be better to start off by declaring “Performance of the server is poor”, which you know to be 100% correct, rather than declaring “Performance of the server is poor due to memory pressure”.

So the steps to follow would be:-

1. Observation

-Queries on production running for longer than expected

2. Declaration

-Performance of the server is poor

3. Hypothesis

-The poor performance of the server is due to memory pressure

4. Testing

-Running an extended events session on the server to catch incoming queries

5. Analysis

-Several queries performing scans of large tables

6. Conclusion

-Creation of a covering non-clustered index to prevent the table scans

7. Further Research

-Are there any other queries being executed that are performing table scans?

These steps may seem obvious but using the scientific method will prevent you from being lead down blind alleys and potentially missing the actual problem. What if the analysis in the example above indicated that memory pressure was not the cause of the poor performance of the server? If you had originally declared “Performance of the server is poor due to memory pressure” you could end up wasting valuable time and effort looking for other indications to back that statement up.

The scientific method provides a structure for the investigation of any performance issues you encounter. It’s very easy to feel under pressure when investigating performance issues, which can lead to mistakes being made or obvious problems being missed. Following the process of problem declaration, forming a hypothesis and then testing gives you a rigid structure to stick to when you’re feeling under the cosh, something to rely on as being a tried and tested method of investigation.





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis


1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren


1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren


360 reads