SQL Performance Tuning by Identifying Slow Running Queries

One of the most important factors in SQL performance tuning is the speed at which your queries are executing. Slow running queries can really handicap system performance. You must identify slow running queries and their potential causes prior to beginning SQL Optimization efforts.

When searching for slow running queries, you will be looking for queries that:

- Slow running queries often take more than a few seconds to run. Sometimes poorly performing queries will take up to 30 minutes or more. When multiplied by the number of users and the number of queries trying to execute, you can easily see how these queries can really bog down a system.
- Run up CPU usage to 90% or more. Other system operations are left with very few CPU resources when a query takes that much by itself.
- Stopping other users from competing their work. Data latches and locks prevent all users from accessing that data until the latch is removed. Required data is then locked away from other system users who aren’t even accessing the queries that caused the lock or latch to be placed on the action.
- Necessitates a help center call. Obviously, the time to fix poorly performing queries is before an end user has to complain, but once they have, you must respond as quickly as possible.

Identifying Slow Running Queries

So now that we have idea of what to look for in our SQL Performance Tuning efforts, how do we find these slow queries? One of the best ways to find these sluggish queries is to use SQL Profiler or another third-party solution that can track queries by their execution speed. Finding these wait-time statistics is a clear way to judge the overall performance health of your system.

When setting what performance information you want to capture be sure to look for data that shows query performance directly, but also any contextual information that my indicate why a query may be running slowly. Queries can come to a SQL Server instance in the form of stored procedures, batches of queries, or individual queries. Your slow running queries may appear in any of these events so you must know which ones you want to capture to identify poor performers.