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 the 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 increases response times dramatically.
Recommended action :
Using AimBetter Observer, identify the processes that are occupying most RAM. If possible, achieve an 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 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 several 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.
