Slow query SQL Server 2016

Question

Hello Everyone, I have a query that is taking more than 18 hours and doesn’t finish, as it doesn’t finish I can’t generate the actual execution plan.

SELECT
req.session_id,
--req.percent_complete,
req.blocking_session_id,
req.start_time,
CONVERT(VARCHAR,DATEADD(ms, req.total_elapsed_time, '1900-01-01 00:00:00'),114) AS [hh:mm:ss:mmm],
req.total_elapsed_time,
req.wait_time,
req.wait_type,
req.command,
DB_NAME(req.database_id) as 'Database',
req.cpu_time,
req.reads,
req.writes,
req.logical_reads,
req.percent_complete
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

Can anyone help me decrease the query execution time?
Thank you so much.

Answer

Have you tested this query on a smaller set of data to verify that it produces the correct result? I mean, there is little point in optimising an incorrect query.

The query is very complex. I would try materialising some of the virtual tables to temp tables and index these temp tables. That could help performance. If nothing else, it can help to debug the query.

Answer

Hi @fernando satonni ，

Welcome to Microsoft Q&A!

Sounds like that you face one blocking issue.

Please refer below steps and check whether it is helpful:

Check any resource bottleneck such as memory, disk I/O and CPU if the statement that could be completed quickly now takes a long time.
Check whether there is any blocking caused by a transaction that did not commit as expected.
Check whether it is due to the client's failure to fetch the result set in time.
Check whether it is any deadlock.
Check whether it is due to multiple entries for the same SPID(representing parallelism).
Enhance this statement to make it simpler and adjust the indexes or table structure.

Besides, you could also refer below queries which may be helpful:

--find deadlock，notice column named BlkBy which represents the SPID that is currently stopping the SPID in the row.  
sp_who2  
  
--find blocking  
SELECT *   
FROM sys.dm_exec_requests  
WHERE DB_NAME(database_id) = 'YourDBName'   
AND blocking_session_id <> 0  
  
--The following script will show the blocking processes (lead blocker)  
SELECT spid ,  
       sp.status ,  
       loginame = SUBSTRING(loginame, 1, 12) ,  
       hostname = SUBSTRING(hostname, 1, 12) ,  
       blk = CONVERT(char(3), blocked) ,  
       open_tran ,  
       dbname = SUBSTRING(DB_NAME(sp.dbid),1,10) ,  
       cmd ,  
       waittype ,  
       waittime ,  
       last_batch ,  
       SQLStatement = SUBSTRING ( qt.text, er.statement_start_offset/2, (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2 )  
FROM master.dbo.sysprocesses sp  
LEFT JOIN sys.dm_exec_requests er ON er.session_id = sp.spid OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt  
WHERE spid IN  
    (SELECT blocked  
     FROM master.dbo.sysprocesses)  
  AND blocked = 0

Best regards
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer

An estimated plan will help you diagnose the issue.

However, most likely you are running into a blocking problem, not a query performance problem.

Slow query SQL Server 2016

3 answers