Skip to main content

Posts

Showing posts with the label How To Identify The Long Running Queries in SQL?

How To Identify The Long Running Queries in SQL?

These queries can help you  To Identify The Long Running Queries in SQL. 1.SELECT creation_time ,last_execution_time ,total_physical_reads ,total_logical_reads ,total_logical_writes , execution_count , total_worker_time , total_elapsed_time , total_elapsed_time / execution_count avg_elapsed_time ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st where total_elapsed_time >= 800000000 ORDER BY total_elapsed_time / execution_count DESC; 2.DBCC opentran(This command will display the longest running transactions on the database) and select * from sys.dm_tran_active_snapshot_database_transactions(this command will display the transactions about replication and mirroring and snapshot isolated related transactions)