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
Another transaction has locked and prevented this transaction from being committed
Where possible, use “WITH (NOLOCK)” in reading transactions. Reprioritize processes according to needs.
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 RAM can force excessive page file swapping, which reduces overall speed to disk-access times and increses response times dramatically.
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.
Incorrect query code may result in inefficient data access.
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
Detect missing indexes by looking at the execution plans.
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.