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