A SQL Server long-running query refers to a query that is taking a significant amount of time to execute, negatively impacting the 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 of your organization’s operations. In addition, it might cause queries to be blocked or not be completed due to time-out.
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 in order to identify blocking queries currently, such as SQL Server Profiler. 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 which 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 and 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 is the query that you wanted to check by checking the application, execution start time, login, and more.
- Take into account that there can be multiple layers of blocking or complex dependencies. Therefore you might aim for a wider insight: analyze the transaction isolation levels, settings, and the 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 at the same time. 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, not enough cores or improper settings of disks.
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.
- Check the Task Manager in order to identify the overload of hardware resources.
- Use OS tracking tools such as Performance Monitor in order to identify which processes use hardware resources. For network activity, use a network monitoring tool, and 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, with which you can’t compare to old time frames.
- Check whether the long-running queries 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 be using 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.
Recommended action :
Cancel unnecessary programs that cause high hardware utilization. Where 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 that are 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 there is increased resource usage. Check indexing and query execution plan.
- Track the database server activity: identify which SQL statements have high execution time and consume mostly the resources. For SQL Server, you can use the built-in activity monitors or SQL Profiler. This mission might be complicated and take hours or days of work, requiring a highly skilled DBA.
- Identify whether the query is a stored procedure or a job that is running for a specific time schedule, or a single user who is running it from a specific application.
- Investigate the query code and execution plan, trying to look for missing indexes or excess subqueries. In order to get this data, you have to run the query live again from the 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 at the moment can be viewed from a single screen, and you can filter and sort the queries by execution time or resource utilization or even search for a specific query text.
Queries execution plan and missing indexes are available immediately, even while the queries are running live! No need to waste time waiting or running them again, including notes showing recommendations for improvements.
Recommended action :
Check what are the bottlenecks of the long-running queries, and try to optimize accordingly. You should consult with a DBA who understands the data model before making these changes.
- Look for functions over fields that can be tuned or avoided to improve performance.
- Make sure you are using proper indexes; add or delete indexes if possible and needed.
- 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 into more simple steps, and make sure to 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
After checking the query’s design and server resources, you should follow this step.
Inadequate database design, either by not properly normalizing or denormalizing the database schema, can result in inefficient queries. In addition, you have to make sure that the instance’s current design fits the business flow and application requirements.
Problem identification:
Analyze the query’s performance and look for queries that consistently take longer to execute, and identify which instance and databases its origins from. Once you are sure that 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. For that, you can use the built-in activity monitors or SQL Profiler. 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 with a professional DBA who knows the structure of the instance.
- Check the instance settings, for example, parallelism and optimization and memory settings. Don’t forget to evaluate statistics and daily tasks to improve the functionality of the databases. As for server settings, make sure that infrastructure fits the best mode of working- for example, advanced system properties.
Recommended action :
Change the settings of the database, instance, or host if needed. You should work along 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.
