22,209
社区成员
发帖
与我相关
我的任务
分享
--某个库中存储过程平均耗时
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 );