question

fernandosatonni-2275 avatar image
0 Votes"
fernandosatonni-2275 asked ·

Slow query SQL Server 2016

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.




sql-server-transact-sql
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @fernandosatonni-2275

Could you please validate all the answers so far and provide any update?

Best regards
Melissa

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ·

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.

· 2 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog, thanks for the feedback,

I tried to make a SELECT TOP 10, but it also didn't end

Yes, this SELECT is very complex, could you help me build this SELECT with the temporary tables to improve performance?

Thanks.

0 Votes 0 ·
ErlandSommarskog avatar image ErlandSommarskog fernandosatonni-2275 ·

You will have to excuse me, but first you construct a very complex query - and then you want someone else to break it apart for you?

OK, there are some indications that this query is generated by some tool. But not a very good tool, if it adds those NOLOCK and inlines the parameters.

But, no, I'm inclined to try to straighten out that mess.

Again, did you test on a small data set to verify that the query is correct? This is a necessity, because if the query is not correct, you would only be wasting your time on trying to speed it up.

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ·

Hi @fernandosatonni-2275

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered ·

An estimated plan will help you diagnose the issue.

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

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.