A SQL Server long-running query is one that takes a significant amount of time to execute, negatively impacting overall system performance.
Queries or updates that take longer than expected to execute can be caused by a variety of reasons. It may be related to performance problems related to the network or the computer where the SQL Server is running or can also be caused by problems with the physical database design.
There are several common reasons for long-running queries and updates:
- Slow network communication
- Insufficient memory in the SQL Server host or not enough memory available for the SQL Server
- Lack of useful statistics, data striping, or useful partitioning
- Lack of useful indexes
- Lack of useful indexed view
Find out how you can save hours of work when investigating the root cause of this issue.
Symptoms :
Queries are running longer than expected.
Impact: High
Slow SQL responses will degrade the user experience, resulting in poor efficiency in your organization’s operations. In addition, they might cause queries to be blocked or not completed due to timeout.
Expected behavior :
There are no standard metrics for the length of SQL Query execution. For establishing the best benchmarks, queries should require about the same time to complete when testing and when running in production (assuming the same volume of data is processed).
In addition, it depends on business flow needs and if regular execution time can be improved or not, depending on the specific code and transactions at the same query.
Possible causes of long-running queries:
1- Queries are waiting for data that depends on locked resources. Priority: High
When multiple transactions are contending for resources, blocking situations can occur, causing delays in query execution.
Blocking happens when another transaction has locked the table source and prevented this transaction from being committed since the table is already in use.
Problem identification:
You should identify the blocking queries currently and check if their resource is the same as the query that is running long.
- Use tracking tools, such as SQL Server Profiler, to identify blocking queries currently. This task might be complicated since you have to make sure that the database and table in these queries are the same as the query that is running long. You should search for data related to session ID, wait duration, wait type, wait resource, and the code of the affected queries.
- By comparing the blocking query to the affected queries, you can determine if the blocking query is causing delays or long execution times for the affected queries. Make sure which of the affected queries you want to check by checking the application, execution start time, login, and more.
- Take into account that multiple layers of blocking or complex dependencies can exist. Therefore, you might aim for a wider insight by analyzing the transaction isolation levels, settings, and overall database workload.
Recommended action :
To resolve blockings, you may need to adjust the query design and execution plan, optimize indexing, implement correct isolation levels, tune queries, and change settings if required.
Where possible, use “WITH (NOLOCK)” in reading transactions. Reprioritize processes according to needs.
2- Overloaded or wrongly set hardware resources Priority: Hig
Overloaded server resources such as CPU, memory, disk I/O, or network can slow down query execution. It might happen due to a specific application or many processes running simultaneously. These processes might be external operations such as anti-virus scans, backups, and restores from networked data stores.
Other cases might be that the resource settings do not fit the requirements of the instance operation—for example, there are not enough cores or disks are set improperly.
Problem identification:
Check if CPU, memory, disk I/O, or network resources are highly utilized. Identify the source of the load and if it is recurrent. Try to reschedule heavy tasks to a time of low user demand.
- First, you have to identify the overload. For Windows OS, you can check the Task Manager to identify an overload of hardware resources. For Linux, you can check with the commands “top” for CPU, “free” for Memory, “iostat” for Disk I/O, “vmstat” for Virtual Memory statistics, and “load” Network.
- Use OS tracking tools such as Performance Monitor to identify which processes use hardware resources. For network activity, use a network monitoring tool to check how much traffic flows through your network and which applications or devices use the most bandwidth. Take into account that most tools help pinpoint when a problem starts, which you can’t compare to old time frames.
- Check whether the long-running query wait time is for a specific hardware resource and which hardware resource is more utilized than others while the query is running. For that, you should use tracing tools such as SQL Server profiler. This step might be completed and take time, and the result probably won’t be precise while checking only online statuses.
Cancel unnecessary programs that cause high hardware utilization. When possible, reschedule non-essential activity (e.g., virus scans, backup, etc.) to hours of lowest SQL demand.
Consider upgrading hardware if possible.
3- Inefficient query design or indexing Priority: Medium
Queries not properly optimized with inefficient query execution plans can lead to poor performance, inefficient data access, and increased resource usage. This can include missing or outdated statistics, inappropriate use of joins or subqueries, unnecessary sorting or grouping, or inefficient use of functions
Execution plans provide valuable insights into how the queries are being processed and help to identify areas where inefficiencies exist.
Problem identification:
Identify which queries are running long and whether resource usage has increased. Check the indexing and query execution plan.
- Track the database server activity: Identify which SQL statements have high execution time and consume most resources. For SQL Server, you can use the built-in activity monitors or SQL Profiler. This mission might be complicated and require hours or days of work, requiring a highly skilled DBA.
- Identify whether the query is a stored procedure, a job running for a specific time schedule, or a single user running it from a specific application.
- Investigate the query code and execution plan, looking for missing indexes or excess subqueries. To get this data, you have to run the query live again from the SQL Server Management Studio, which might increase the overload and the investigation time of this issue.
- Examine index usage and fragmentation: Evaluate the usage and fragmentation of indexes in the database. Unused or fragmented indexes can impact query performance.
All running queries can be viewed from a single screen.
You can filter and sort the queries by execution time or resource utilization or even search for a specific query text.
The query execution plan and missing indexes are available immediately, even when the queries run live!
No need to waste time waiting or running them again, including notes showing recommendations for improvements.
Recommended action :
Check the bottlenecks of the long-running queries and optimize accordingly. Before making these changes, consult with a DBA who understands the data model.
- Look for functions over fields that can be tuned or avoided to improve performance.
- Ensure you are using proper indexes; add or delete indexes if necessary.
- Minimize the number of fields and records in the result sets as much as possible so that they only return what is needed and nothing more.
- Break complex queries into simpler steps and use proper filtering, WHERE clauses, and JOIN operations.
- Investigate the query’s execution plan and look for heavy tasks and loads of data.
4- Inefficient server and database design Priority: Medium
You should follow this step after checking the query’s design and server resources.
Inadequate database design, either by not properly normalizing or denormalizing the database schema, can result in inefficient queries. In addition, you have to ensure that the instance’s current design fits the business flow and application requirements.
Problem identification:
Analyze the query’s performance, looking for queries that consistently take longer to execute and identifying which instance and databases they originate from. Once you are sure the reason is not an inefficient query execution plan or lack of sufficient resources, you should check instance and database settings.
- Identify which SQL statements have a high execution time. You can use the built-in activity monitors or SQL Profiler to do this. Identify the database and instance from which the query is running.
- Check the database settings, such as data and log file settings and division, backup type, compatibility, and more. This must be done by a professional DBA who knows the instance’s structure.
- Check the instance settings, such as parallelism, optimization, and memory settings. Remember to evaluate statistics and daily tasks to improve the databases’ functionality. As for server settings, make sure that the infrastructure fits the best mode of working—for example, advanced system properties.
If needed, change the settings of the database, instance, or host. You should work with a professional and track these changes with proper monitoring tools.
Ensure that the server is appropriately sized and configured based on the workload demands.
