Troubleshoot Long Running Queries in SQL Server Database?

Slow or long-running queries in SQL Server often affect the performance, relative applications and even the health of the database itself. Thus, users must keep their database optimized to keep the slow-running queries away partially or fully fix them. Thus, this article is going to help users find long queries, understand their reasons, and find the best ways to fix them.

How to Find and Resolve Long Running Queries in SQL Server?

The very first step here for users is to examine that they have slow running queries. Examining the time taken by queries to execute (elapsed time) is the key to the right path. In order to know the roots of the problem, users must know why the database is taking longer to respond to simple queries than usual.

Just by checking the total_elapsed_time and cpu_time columns in the sys.dm_exec_requests is a nice initiative for current statements under execution.

SELECT 
    req.session_id
    , req.total_elapsed_time AS duration_ms
    , req.cpu_time AS cpu_time_ms
    , req.total_elapsed_time - req.cpu_time AS wait_time
    , req.logical_reads
    , SUBSTRING (REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1, 
       ((CASE statement_end_offset
           WHEN -1
           THEN DATALENGTH(ST.text)  
           ELSE req.statement_end_offset
         END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 
      1, 512)  AS statement_text  
FROM sys.dm_exec_requests AS req
    CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
ORDER BY total_elapsed_time DESC;

For Older or past executed queries, users must check for last_elapsed_time and last_worker_time columns in sys.dm_exec_query_stats. The below-mentioned query is how users are going to do this.

SELECT t.text,
     (qs.total_elapsed_time/1000) / qs.execution_count AS avg_elapsed_time,
     (qs.total_worker_time/1000) / qs.execution_count AS avg_cpu_time,
     ((qs.total_elapsed_time/1000) / qs.execution_count ) - ((qs.total_worker_time/1000) / qs.execution_count) AS avg_wait_time,
     qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
     qs.total_logical_writes / qs.execution_count AS avg_writes,
     (qs.total_elapsed_time/1000) AS cumulative_elapsed_time_all_executions
FROM sys.dm_exec_query_stats qs
     CROSS apply sys.Dm_exec_sql_text (sql_handle) t
WHERE t.text like '<Your Query>%'

-- Here, Replace <Your_Query> with your query or the beginning section of your query. The special chars like '[','_','%','^' in the query should be escaped.

ORDER BY (qs.total_elapsed_time / qs.execution_count) DESC

Also, users must note that the avg_wait_time shows a negative value, it’s definitely a parallel query. To use SSMS or Azure Data Studio, in case users can execute a query on demand, users must run it with  SET STATISTICS TIME ON and SET STATISTICS IO ON.

The command looks like this:

SET STATISTICS TIME ON
SET STATISTICS IO ON
<Your_Query>
SET STATISTICS IO OFF
SET STATISTICS TIME OFF

After running, this, users can read CPU time, elapsed time and logical reads from the messages i\n the below format:

Table ‘tblTest’. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

  SQL Server Execution Times:
    CPU time = 460 ms,  elapsed time = 470 ms.

Also, in case, users want to check or identify a never-ending query, they must run the below query: 

DECLARE @cntr int = 0

WHILE (@cntr < 3)
BEGIN
    SELECT TOP 10 s.session_id,
                    r.status,
                    r.wait_time,
                    r.wait_type,
                    r.wait_resource,
                    r.cpu_time,
                    r.logical_reads,
                    r.reads,
                    r.writes,
                    r.total_elapsed_time / (1000 * 60) 'Elaps M',
                    SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,
                    ((CASE r.statement_end_offset
                        WHEN -1 THEN DATALENGTH(st.TEXT)
                        ELSE r.statement_end_offset
                    END - r.statement_start_offset) / 2) + 1) AS statement_text,
                    COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) 
                    + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,
                    r.command,
                    s.login_name,
                    s.host_name,
                    s.program_name,
                    s.last_request_end_time,
                    s.login_time,
                    r.open_transaction_count,
                    atrn.name as transaction_name,
                    atrn.transaction_id,
                    atrn.transaction_state
        FROM sys.dm_exec_sessions AS s
        JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id 
                CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
        LEFT JOIN (sys.dm_tran_session_transactions AS stran 
             JOIN sys.dm_tran_active_transactions AS atrn
                ON stran.transaction_id = atrn.transaction_id)
        ON stran.session_id =s.session_id
        WHERE r.session_id != @@SPID
        ORDER BY r.cpu_time DESC

    SET @cntr = @cntr + 1
WAITFOR DELAY '00:00:05'
END

Now, that users know the ways to find long-running transactions completely, it’s time to understand the reason for it as well.

Reasons for Long Running Queries in SQL Server

Now, we know how to check if we have slow-running SQL transactions, it is time that we move ahead to understand the causes behind this. There is not a fixed reason and most of these reasons can vary as per the situation and user to user. However, we are going to mention the most common reason ahead.

#1 Insufficient Query Design

• Mistakes in Queries – Complex queries with additional joins and subqueries with multiple causes, can often degrade the performance speed of SQL Server along with its quality.
• Improper Indexing – Without the proper index structures, the database has to scan the entire database & tables to get any data. This makes the queries long and slow which spoils user experiences as well.
• Suboptimal Query Plan – Zero or insufficient plans & strategies for queries lead to the consumption of resources more than required. There, the necessary queries might not get the needed resources & lead to long-running query issues.

Also Read: SQL Server Error 9002 Fixed without Hassles

#2 Database Design Issues

• Data Integrity Problems: Inconsistent, inaccurate, and incompetent data often create issues in executing any query without any errors. This later causes the database to work slowly & queries to take longer time to execute.
• Normalization Issues: Over-normalization or even under-normalization leads to complications in queries like complex joins, slow queries and other performance-related errors.

#3 Resource Constraints

• Hardware Constraints – Issues with the CPU memory, and disk I/O errors, can often bottleneck the query execution which can further make users wait longer to get the results. Outdated and unhealthy databases are the major causes here.
• Network Congestions – To troubleshoot long-running queries in the SQL Server database, users must know the cause of network congestion. If users execute data transfers or even remote queries, network latency can slow down their work for sure.

#4 Locking and Blocking

• Poor Isolation problem – Locking and blocking events can be increased if users don’t monitor the isolation levels and it gets poor. This causes major issues in the database including transactions taking longer than usal.
• Long Running Queries in SQL Server – In case a transaction isn’t completed successfully or it’s taking too long, it might disturb another query relying on it. This blocking of queries leads to performance degradation and more time-consuming database functions.

The command to check for blocking is:

SELECT 
    blocking_session_id AS BlockerSessionID,
    session_id AS BlockedSessionID,
    wait_type,
    wait_time
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

#5 Resource Waits

Buffer Pool Waits – Frequent disk reads are a common issue which is due to insufficient memory for data caching purposes. It even contributes to the time taken by a transaction to execute and display results.

Page I/O Waits – Disk I/O tasks when exceeding the normal standard limit, can definitely lead to slow execution of queries. To prevent this, users must take care of their I/O health at frequent intervals.

Latch Waits – Delays & slow transactions can also be due to contention of internal structures of databases.

The command to check the wait statistics is:

SELECT 
    wait_type, 
    wait_time_ms / 1000.0 AS WaitTimeSec,
    waiting_tasks_count AS WaitCount
FROM sys.dm_os_wait_stats
ORDER BY WaitTimeSec DESC;

#6 Corrupted File

Corruption is another major issue which causes SQL data to take longer than usual to load. Several other tasks like data search, sorting, etc are also there that get affected due to corrupted data. However, SysTools SQL Recovery Tool is more than enough to fix such corruption issues apart from DBCC CHECKDB commands.

By fixing the corrupted data, users can easily, fix such long-running transactions in SQL Server database.

How to Resolve Long-Running Queries in SQL Server?

To troubleshoot the slow-running SQL queries, users just need to simply Find the SQL long-running transactions. We already learnt this above. Now, it’s time to know the other steps in the queue to proceed with.

Step-1. Analyzing the Query – 

Now, users need to analyze the query strategy and plan. It includes identifying operators that are insufficient. For example, table scans and operations. Checking for missing indexes, and analyzing the cost of each operator also plays a major role here. Also examining the query text for any mistakes or syntax errors is another way to fix this error.

Step-2. Optimize the Query – 

Creating the missing indexes to let the database engine search & sort data faster helps in fixing the long running transactions in SQL Server. Selective indexes with supported data types are a better option for users. Always break down the confusing long queries in smaller parts to reduce risk and resource acquisition. Avoiding function calls and computations that aren’t as significant as they should be helps reduce query time.

Step-3. SQL Database Tuning

Tuning the SQL Server database to get the optimum performance from it is what we have to do next. Users must update their statistics and monetary data to make sure they use their database to its fullest. Moreover, troubleshooting long-running queries in SQL Server occurs due to the bad health of database hardware. Thus, hardware health and the right configuration are equally important here.

Step-5. Consider Indexing Strategies

Make sure to always build the right indexes wherever required. Rather a clustered index, a non-clustered index or even a filtered index, choose wisely.

Tips and Tricks to Get Rid of Slow Running SQL Query

Now, when we discuss additional tips, users must take care of a few things.

1. Data Corruption is one thing users need to be careful about.
2. Understanding the difference between running and waiting queries.
3. Using parameterized queries is another way for users to get the best results.
4. Always conduct database health checks frequently to find slow running queries.
5. Consulting a team of qualified database administrators and MVPs is a good option.

The Conclusion

Finally, users are aware of the best ways to troubleshoot long-running queries in SQL Server without any hassles. Here, we have mentioned the causes as well as the ways to find such slow running queries in SQL databases. In a nutshell, we can say that it’s not a tough task but indeed a consistent one.