How To Check Long Running Queries in SQL Server?

Long Running Queries

Identifying and troubleshooting long running queries is essential to ensure SQL Server operates efficiently. If you’re wondering how to check long running queries in SQL Server, this process can help you optimize performance and prevent application slowness. Slow queries can create an application slowness problem, if any query is taking more time to execute, then your application performance can be decreased and increased.

Long running query can be a poor user experience, as users may have to wait longer for their request to get completed. This may increase the server utilizations like CPU and memory, which may impact other processes on the server.

Why your SQL queries are slow?

When you find that your application performance is decreasing and your database is SQL, first you should check database performance. Now you may think about how to check long running queries in SQL Server. The first step is to identify long running queries that is taking long time to execute, to find it, you can use the sys.dm_exec_query_stats view.

Long running queries can be because of many reasons like missing or inefficient indexing due to which any query which points to the table where indexes are not created can forces to scan full table.

Unwanted joins or subqueries can also be a reason for long running queries, if there is a query or procedure which is fetching data from more than one table through join or written some subqueries then it can degrade performance.

To avoid this situation, you should analyse your query execution plans and optimize them regularly, this approach can help you to improve performance.

Also Read: Do SQL Views Update Automatically?

Waiting Queries

Waiting queries in SQL Server occur when a query is unable to proceed because it is waiting for a resource to become available. These waits can significantly impact database performance if not properly identified and resolved. Below are few common reasons for waiting queries.

  1. Lock Contention: When multiple queries compete for the same data.
  2. Resource Bottlenecks: Insufficient CPU, memory, or I/O capacity.
  3. Blocking: Queries are blocked by others holding locks on required resources.
  4. Deadlocks: Two or more queries are waiting on each other in a cyclic dependency.

To identify waiting queries in SQL Server, you can use below approaches.

1. Dynamic Management Views (DMVs):

  • Query sys.dm_exec_requests to see active requests and their wait types.
  • Check sys.dm_os_waiting_tasks for details on what each task is waiting for.

2. Activity Monitor:

  • Provides an overview of processes, waits, and resources in use.

3. Extended Events or SQL Server Profiler:

  • Track and log waiting events for analysis.

Example Query:

SELECT
r.session_id,
r.status,
r.wait_type,
r.wait_time / 1000.0 AS wait_time_in_seconds,
r.blocking_session_id,
r.total_elapsed_time / 1000.0 AS elapsed_time_in_seconds,
t.text AS query_text
FROM
sys.dm_exec_requests r
CROSS APPLY
sys.dm_exec_sql_text(r.sql_handle) t
WHERE
r.wait_type IS NOT NULL
ORDER BY
r.wait_time DESC;

This query retrieves information about active queries in SQL Server that are currently waiting for resources. It displays the session ID, current status, type of wait, wait duration (in seconds), blocking session ID (if applicable), total elapsed time (in seconds), and the SQL text of the query. By filtering for queries with a non-NULL wait type, it focuses on those experiencing delays and orders the results by wait time in descending order to prioritize the longest-waiting queries.

Running Queries

Running queries are those currently running on the SQL Server at any time and utilizing the server’s resources such as CPU and memory. We can utilize the sys.dm_exec_requests DMV to find which all queries are running and using more resources along with their execution time. Knowing the running queries is essential in helping improve database and application performance.

Example Query:

SELECT
r.session_id,
r.status,
r.cpu_time / 1000.0 AS cpu_time_in_seconds,
r.reads,
r.writes,
r.total_elapsed_time / 1000.0 AS elapsed_time_in_seconds,
t.text AS query_text
FROM
sys.dm_exec_requests r
CROSS APPLY
sys.dm_exec_sql_text(r.sql_handle) t
WHERE
r.status = 'running'
ORDER BY
r.total_elapsed_time DESC;

This query fetches the data about queries currently running in the SQL Server. This query returns session ID, the status of the query, CPU time consumed (in seconds), reads and writes performed, total elapsed time (in seconds), and finally the text of the query. The result set is filtered by querying only running queries, further sorted by total elapsed time, in descending order. These are the queries that have consumed the most time in the database to run.

Stored Procedures

Stored procedures are precompiled collections of SQL statements and logic stored in a database. They are reusable and designed to perform a specific task like data retrieval, update, or complex operations. This will help us to enhance database performance, security, and maintainability.

Poorly written stored procedure can also decrease the database performance. We can identify performance of any stored procedures by reviewing execution plans and using DMVs to analyze execution statistics.

Example Query:

SELECT
sp.name AS ProcedureName,
qs.total_elapsed_time / qs.execution_count AS AverageElapsedTime
FROM
sys.dm_exec_query_stats qs
INNER JOIN
sys.procedures sp ON qs.sql_handle = OBJECT_ID(sp.name)
ORDER BY
AverageElapsedTime DESC;

The query fetches the names of stored procedures along with their average execution time by dividing the total elapsed time by the number of executions. It joins the sys.dm_exec_query_stats view, which contains query execution statistics, with the sys.procedures system view, which holds information about stored procedures. The query filters by procedure based on SQL handle, ordering the results in descending average elapsed time, thereby spotlighting the stored procedures which have the longest average execution times.

Best Practices to Identify and Troubleshoot Slow SQL Queries

1. Use Dynamic Management Views (DMVs)

You should monitor your query performance regularly and identify long running queries by using DMVs such as sys.dm_exec_query_statssys.dm_exec_requests, and sys.dm_exec_sql_text. These queries will help to get detailed information of queries execution and resource usage.

2. Analyze Execution Plans

Reviewing execution plan will help to identify that which task is taking more time like scanning of table, missing indexes, and how we can optimize the query to improve performance.

3. Implement SQL Server Profiler and Extended Events

You can use tools like Profiler or extended events to capture queries in real time and analyse long running queries and you can optimize them to improve database performance.

4. Optimize Queries and Indexes

Once you are done with your analysis for long running queries, you can optimize your queries, or you can rewrite for better performance and create missing indexes also. Proper indexing may help to improve query execution time.

5. Regularly Monitor and Tune Performance:

Continuously monitor the performance of your queries and adjust SQL Server settings, indexes, and queries as needed. Regular performance assessments and optimizations help keep your SQL Server environment efficient and responsive.

Conclusion

Long-running queries in SQL Server should be identified and addressed in order to maintain optimal performance and user experience. By knowing the root causes of slow queries, such as inefficient indexing, resource bottlenecks, or blocking, you will be able to take the steps necessary to optimize your queries. The use of tools such as Dynamic Management Views (DMVs), analysis of execution plans, and the use of SQL Server Profiler or Extended Events can be very helpful in identifying and troubleshooting slow queries. Monitoring and fine-tuning your database environment, along with best practices for query optimization and indexing, will ensure that your SQL Server runs efficiently, avoiding slowdowns and improving overall performance.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top