預存程序效能查詢語句

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