sys.dm_exec_query_stats?

Question

sys.dm_exec_query_stats?

Chowdary's

SSCrazy

Points: 2978
More actions
September 12, 2014 at 6:57 am

#289960

Hi Expert,
For finding the long running query we will use the Syntax "select * from sys.dm_exec_query_stats",It will give some Result from that result how can we find the long running Queries and how to resolve those type of issues....
Regards
Chowdary...

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lempster SSCoach Points: 15651 More actions · Answer 1

You can find out information about query plans from sys.dm_exec_query_stats, but if you want to find long-running queries, queries that consume a lot of CPU or queries that involve a lot of reads, you can use this query (taken from Performance Tuning with SQL Server DMVs by Louis Davidson and Tim Ford):

SELECT der.session_id ,

DB_NAME(der.database_id) AS database_name ,

deqp.query_plan ,

SUBSTRING(dest.text, der.statement_start_offset / 2,

( CASE WHEN der.statement_end_offset = -1

THEN DATALENGTH(dest.text)

ELSE der.statement_end_offset

END - der.statement_start_offset ) / 2)

AS [statement executing] ,

der.cpu_time

der.granted_query_memory

der.wait_time

der.total_elapsed_time

der.reads

FROM sys.dm_exec_requests der

INNER JOIN sys.dm_exec_sessions des

ON des.session_id = der.session_id

CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) dest

CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) deqp

WHERE des.is_user_process = 1

AND der.session_id <> @@spid

--ORDER BY der.cpu_time DESC ;

-- ORDER BY der.granted_query_memory DESC ;

-- ORDER BY der.wait_time DESC;

ORDER BY der.total_elapsed_time DESC;

-- ORDER BY der.reads DESC;

Comment/un-comment the ORDER BY clauses as appropriate.

In terms of troubleshooting long-running queries, well, that's a large topic, but you could start by looking at the query plan to see which operation contributes the largest percentage of the total batch and try and tune that, check that you have appropriate indexes, that statistics are up-to-date.

Regards

Lempster

Chowdary's SSCrazy Points: 2978 More actions · Answer 2

Hi Mr.Lempster,

Thank you for response ,I ll Try this and let you know the status.....

Regards
Chowdary...