September 12, 2014 at 6:57 am
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...
September 12, 2014 at 7:57 am
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
September 14, 2014 at 1:04 am
Hi Mr.Lempster,
Thank you for response ,I ll Try this and let you know the status.....
Regards
Chowdary...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply