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 functionssame 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