DMV’s in action finding slowest queries and procedures in your database

using the Dynamic management views

SELECT QS.CREATION_TIME,QS.TOTAL_ELAPSED_TIME,QS.EXECUTION_COUNT,QT.TEXT,DB_NAME(QT.DBID) AS DATABASENAME,OBJECT_NAME(QT.OBJECTID) AS OBJECTNAME,QP.QUERY_PLAN 

FROM SYS.DM_EXEC_QUERY_STATS QS 

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) QT 

CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(QS.PLAN_HANDLE) QP

ORDER BY QS.TOTAL_ELAPSED_TIME DESC 

in the above query SYS.DM_EXEC_QUERY_STATS  is system table
SYS.DM_EXEC_SQL_TEXT(),SYS.DM_EXEC_QUERY_PLAN() are table valued functions 



same way we can find the slowest procedures in the database

SELECT QS.TOTAL_ELAPSED_TIME,QS.EXECUTION_COUNT,QT.TEXT,DB_NAME(QT.DBID) AS DATABASENAME,OBJECT_NAME(QT.OBJECTID) AS OBJECTNAME,QP.QUERY_PLAN 

FROM SYS.DM_EXEC_PROCEDURE_STATS QS 

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) QT 

CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(QS.PLAN_HANDLE) QP

ORDER BY QS.TOTAL_ELAPSED_TIME DESC 
 
Finding queries which require most I/O
SELECT QS.TOTAL_LOGICAL_READS,QS.TOTAL_LOGICAL_WRITES,QS.EXECUTION_COUNT,QT.TEXT,DB_NAME(QT.DBID) AS DATABASENAME,OBJECT_NAME(QT.OBJECTID) AS OBJECTNAME,QP.QUERY_PLAN 

FROM SYS.DM_EXEC_QUERY_STATS QS 

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) QT 

CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(QS.PLAN_HANDLE) QP

ORDER BY QS.TOTAL_LOGICAL_READS+QS.TOTAL_LOGICAL_WRITES DESC
Advertisements
Posted in: SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s