存储过程性能查询语句

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_timeLastExecDate,

 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 ELSEtotal_elapsed_time/(1000*execution_count) END AvgTimeMS,

 CASE WHEN execution_count=0 THEN 0 ELSEtotal_worker_time/(1000*execution_count) END AvgTimeCPU,

 last_elapsed_time/1000LastTimeMS,

 min_elapsed_time/1000MinTimeMS,

 total_elapsed_time/1000TotalTimeMS,

 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

WHEREdatabase_id = DB_ID()

ANDlast_execution_time > dateadd(day, -7, getdate())

 ORDER BY 6 desc, 3

 

 

 

---Investigatinglogical reads performed by cached stored procedures

 -- Top Cached SPs ByTotal Logical Reads (SQL 2008 only).

 -- Logical readsrelate to memory pressure

 SELECT TOP ( 25 )

         p.nameAS [SP Name] ,

         deps.total_logical_readsAS [TotalLogicalReads] ,

         deps.total_logical_reads/ deps.execution_countAS [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_countAS [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_readsDESC ;

 

 

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