sql 查询存储过程、sql执行情况

--某个库中存储过程平均耗时

SELECT  TOP  ( 25 )

         p. name  AS  [SP  Name ] ,

         qs.total_elapsed_time / qs.execution_count  AS  [avg_elapsed_time] ,

         qs.total_elapsed_time ,

         qs.execution_count ,

         ISNULL (qs.execution_count / DATEDIFF( Second , qs.cached_time, GETDATE()),

                0)  AS  [Calls/ Second ] ,

         qs.total_worker_time / qs.execution_count  AS  [AvgWorkerTime] ,

         qs.total_worker_time  AS  [TotalWorkerTime] ,

         qs.cached_time

FROM     sys.procedures  AS  p  WITH  ( NOLOCK )

         INNER  JOIN  sys.dm_exec_procedure_stats  AS  qs  WITH  ( NOLOCK )  ON  p.[object_id] = qs.[object_id]

WHERE    qs.database_id = DB_ID()

ORDER  BY  avg_elapsed_time  DESC

OPTION   ( RECOMPILE );


--这是普通查询语句的
SELECT  qs.execution_count ,
        qs.total_rows ,
        qs.last_rows ,
        qs.min_rows ,
        qs.max_rows ,
        qs.last_elapsed_time ,
        qs.min_elapsed_time ,
        qs.max_elapsed_time ,
        SUBSTRING(qt.TEXT, qs.statement_start_offset / 2 + 1,
                  ( CASE WHEN qs.statement_end_offset = -1
                         THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
                         ELSE qs.statement_end_offset
                    END - qs.statement_start_offset ) / 2) AS query_text
FROM    sys.dm_exec_query_stats AS qs WITH ( NOLOCK )
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.execution_count DESC
OPTION  ( RECOMPILE );