This  is the second part to SQL performance tuning – Asking the right question.

The first post focuses on defining the problem . This post focuses on deciding which performance troubleshooting path to take.

Creating a profile of the problem is the first step in breaking the problem down into smaller parts. The purpose of breaking the problem into smaller parts is to quantify the problem and isolate the essential information required to fix the issue

Example 1 – A query is slow because the response time is greater than 10 seconds and normally it’s 2 seconds

Example 2  -  Client User requests are queing up and we notice CPU usage at 100% , whereas the normal level is 25%

In Example 1, we’re using the response time  as the unit of measure for the task i.e query

In Example 2 , CPU usage is the unit of measure for the client user requests

Once the problem is quantified in this manner , break the quantities down into smaller parts. It may be session IDs (SPID), execution plan data etc. Each profile has it’s own data requirements. The key message is : attempt to gather performance data matching the problem

Creating the profile at the correct level of detail requires relevant data. SQL Server offers different methods of collecting data e.g DMV, Traces , Custom queries.   It’s important to collect data at the right range. If analysing a problem at a user level , then collect data at the user level.  Strip out  noise.

If analysing a user session resource usage , don’t use  system wide aggregates as your starting point. The main point is to collect relevant data.

At a user level, SQL Server is a collection of sessions (SPID). Nothing occurs without a session. Sessions are used to execute T-SQL statements. During execution of a  T-SQL statement , there is work on the CPU or the session goes idle – either being blocked or waiting for some activity to complete. In other words, different events are contributing to the overall time spent

Identify the SPID of the user complaint. Break the session down and analyse how the time is spent during the session. How is time spent during the session? IO, CPU, Locks , idle :  all use time and contribute to the response time.

 A chronologically ordered breakdown of how the session spends time gives clues for the delays. The delay could be on the network , application , storage etc

 So far , we’ve looked at the user level. What happens  if insufficient data is available ? At this point the DBA collects more data , waiting for a reoccurrence.

Developing a systematic and objective approach to SQL performance troubleshooting is key to fixing problems. Avoid relying on luck and focus on the facts.

Read More

SQL Server - Find last sql statement based on SPID - SQL Server DBA

SQL Performance tuning - Asking the right question

SQL Server – netstat monitoring and tuning performance

Mobile Commerce and SQL Performance Tuning

The future of the SQL Server Performance Tuning Expert