SQL: Queries Running Long

Symptoms : Queries are running longer than expected

Impact : High

Slow SQL responses will degrade the user experience, resulting in poor efficiency of your organization’s operations.

Expected behavior :

There are no standard metrics for length of SQL Query execution. For establishing best benchmarks, queries should require about the same time to complete when testing and when running in production (assuming the same volume of data processed).

Possible causes of failure

Queries are waiting for data that depends on locked resources  Priority : High
Another transaction has locked and prevented this transaction from being committed
Recommended action :
Where possible, use “WITH (NOLOCK)” in reading transactions. Reprioritize processes according to needs.

ALL CPU capacity is consumed  Priority : High

Recommended action :
Check what processes occupy the CPU the most, or which processes consume the most memory. Where possible, reschedule non-essential activity (e.g virus scans, backup etc.) to hours of lowest SQL demand.

Insufficient free memory  Priority : High
Insufficient RAM can force excessive page file swapping, which reduces overall speed to disk-access times and increses response times dramatically.
Recommended action :
Using AimBetter Observer, identify the processes that are occupying most RAM. If possible, achieve optimal balance between OS RAM and page file allocation. If no other possibility, add RAM.

Inefficient query design  Priority : Medium
Incorrect query code may result in inefficient data access.
Recommended action :
Check what are the bottlenecks of the long running queries, and try to optimize accordingly. Look for functions over fields that can be tuned or avoided in order to improve performance. Minimize the number of repeats (reading records more than once) as much as possible. Minimize the number of fields and records in the result sets as much as possible so that they would only return what is needed and nothing more. Break complex queries to more simple steps. Look for often used “Cluster Lookup” and “Hush Merger” and minimize their frequency by optimizing the code.

Missing indexes  Priority : Medium
Recommended action :
Detect missing indexes by looking at the execution plans.

Background

Queries or updates that take longer than expected to execute can be caused by a variety of reasons. Slow-running queries can be caused by performance problems related to your network or the computer where SQL Server is running. Slow-running queries can also be caused by problems with your physical database design.

There are a number of common reasons for slow-running queries and updates:

  • Slow network communication
  • Inadequate memory in the server computer, or not enough memory available for SQL Server
  • Lack of useful statistics
  • Lack of useful indexes
  • Lack of useful indexed view
  • Lack of useful data striping
  • Lack of useful partitioning.