SQL Performance tuning - Asking the right question

19 February,2013 by Jack Vamvas

Asking the right questions in SQL   performance tuning is critical to understanding and fixing performance based problems.

For example, when a user complains about slower performance of a report – the DBA needs to define a number of details. At this point it could be one of three types of problems – although all resulting in a slower response for the user. Understanding the difference will inform the  drill – down and solution

What are the types of SQL performance problems?

Problem Type 1 – Performance

Problem Type 2 – Efficiency

Problem Type 3 – Capacity


SQL Performance troubleshooting – what are the questions?

The first question is identify the task . What task is running?

The second question. How long is the task  taking ?

The third question – How long should the task take?

For example – The task is a SQL Server backup . It’s taking 5 hrs and it normally takes 2 hrs.

A SQL performance problem is defined by the task and time.  So, when we talk about a SQL performance problem, we mean a drop in throughput i.e What is the amount of seconds per task and how many tasks occur per second?

If we discover that increased traffic has led to the problem, it’s a capacity problem.  Based on knowledge of the normal rate of traffic versus the increased rate.

Defining and understanding the difference between a drop in throughput versus increase work requests is important in diagnosing and suggesting a fix.

It’s always interesting how users report performance problems. To clarify the issue – I ask for time based evidence. Hours:Minutes:Seconds.  I cannot tell you how many times an owner will report an issue with general terms such as “it’s slow”. OK, what is slow? and how are you measuring it’s slow, is it for example a Datawarehouse report  that is taking 30 minutes ? OK, next question , how long does it normally take to complete?

In the next post , I’ll continue discussing ,how to take the response time of the task and drill into the details

 Read More

SQL Server – How to troubleshoot a slow running query

SQL Server – Find high impact queries with sys.dm_exec_query_stats

SQL Server – Write everything down during problem troubleshooting

How to report a SQL Server performance problem


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on SQL Performance tuning - Asking the right question

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer