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 1

An estimated plan will help you diagnose the issue.

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

Answer 2

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 3

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.