存储过程性能查询语句

sys.dm_exec_procedure_stats返回缓存存储过程的聚合性能统计信息，所以用这个函数我们可以查询缓存的存储过程性能，然后针对耗资源的部分进行优化。

SELECT DB_NAME(database_id) DBName,
OBJECT_NAME(object_id) SPName,
datediff(second, last_execution_time, getdate()) SecondsAgo,
last_execution_time LastExecDate,
CASE WHEN execution_count = 0 THEN '--' ELSE
RIGHT('0'+convert(varchar(5),(total_elapsed_time/(1000000*execution_count))/3600),2)+':'+
RIGHT('0'+convert(varchar(5),(total_elapsed_time/(1000000*execution_count))%3600/60),2)+':'+
RIGHT('0'+convert(varchar(5),((total_elapsed_time/(1000000*execution_count))%60)),2) END ReadableTime,
CASE WHEN execution_count= 0 THEN 0 ELSE total_elapsed_time/(1000*execution_count) END AvgTimeMS,
CASE WHEN execution_count= 0 THEN 0 ELSE total_worker_time/(1000*execution_count) END AvgTimeCPU,
last_elapsed_time/1000 LastTimeMS,
min_elapsed_time/1000 MinTimeMS,
total_elapsed_time/1000 TotalTimeMS,
CASE WHEN DATEDIFF(second, s.cached_time, GETDATE()) < 1 THEN 0 ELSE
cast(execution_count as decimal) / cast(DATEDIFF(second, s.cached_time, GETDATE()) as decimal) END ExecPerSecond,
execution_count TotalExecCount,
last_worker_time/1000 LastWorkerCPU,
last_physical_reads LastPReads,
max_physical_reads MaxPReads,
last_logical_writes LastLWrites,
last_logical_reads LastLReads
FROM sys.dm_exec_procedure_stats s
WHERE database_id = DB_ID()
AND last_execution_time > dateadd(day, -7, getdate())
ORDER BY 6 desc, 3

---Investigating logical reads performed by cached stored procedures
-- Top Cached SPs By Total Logical Reads (SQL 2008 only).
-- Logical reads relate to memory pressure
SELECT TOP ( 25 )
p.name AS [SP Name] ,
deps.total_logical_reads AS [TotalLogicalReads] ,
deps.total_logical_reads / deps.execution_count AS [AvgLogicalReads] ,
deps.execution_count ,
ISNULL(deps.execution_count / DATEDIFF(Second, deps.cached_time,
GETDATE()), 0) AS [Calls/Second] ,
deps.total_elapsed_time ,
deps.total_elapsed_time / deps.execution_count AS [avg_elapsed_time] ,
deps.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats
AS deps ON p.[object_id] = deps.[object_id]
WHERE deps.database_id = DB_ID()
ORDER BY deps.total_logical_reads DESC ;

更多信息可以参考：http://msdn.microsoft.com/zh-cn/library/cc280701.aspx