Rohit Kainth
Write down the names of DMVs to find long running queries or stored procedures.
By Rohit Kainth in SQL Server on Sep 25 2018
  • Rohit Kainth
    Sep, 2018 25

    sys.dm_exec_requests and sys.dm_exec_query_stats are used to identify long running queries or slow queries. Sys.dm_exec_requests gives you insight about the currently running queries(Each Request executing), wait stats, hostname, dbname, start_time etc on the sql server, whereas sys.dm_exec_query_stats gives you insight about the total duration, execution count, TableName etc. In both the DMVs you can join sys.dm_exec_sql_text(SQL handle either of Requests or of Query Stats) to get the sql text(query) associated and sys.dm_exec_query_plan(Plan handle either of Requests or of Query Stats) to get the Cached plan associated with the query. For your understanding : Cached plan are the source of identifying how the query is executing and what improvements can be made to the given query. First take a snapshot of the query plan and then diagnose the query associated with that plan. You can use SET SHOWPLAN_ALL ON/OFF to check the query plan if you make any changes to the query. If you write SET SHOWPLAN_ALL ON, the query will not execute, rather gives you the plan which the query will use. So you can make changes to your query until you are satisfied with the changes.

    • 0

Most Popular Job Functions