RiCo技術農場

我在 SQL PASS 社團看到一個問題，

基本上就是針對上百隻的SP該如何管理，

我自己是比較頃向使用 DMV 來處理，

不過由於DMV是累計的統計資訊，

而且只要重啟 SQL Server Service 就會遺失相關統計資訊，

所以這點還是要注意一下，底下TSQL只適用於SQL2005以上版本。

 

 

1.從快取中查看資料庫所有 SP 執行次數

SELECT DB_NAME(st.dbid) DBName
      ,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName
      ,OBJECT_NAME(st.objectid,dbid) StoredProcedure
      ,max(cp.usecounts) Execution_count
 FROM sys.dm_exec_cached_plans cp
 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 where DB_NAME(st.dbid) is not null
 and DB_NAME(st.dbid)='ricotest1'  
 and cp.objtype = 'proc'
 group by cp.plan_handle, DB_NAME(st.dbid),
          OBJECT_SCHEMA_NAME(objectid,st.dbid), 
          OBJECT_NAME(objectid,st.dbid) 
 order by max(cp.usecounts) desc

 

查看那些SP很低的執行次數，這有可能意味這些SP可能需要好好整理一下。

 

 

 

2.查看SP使用的I/O資源

SELECT DB_NAME(st.dbid) as DBName
      ,OBJECT_SCHEMA_NAME(objectid,st.dbid) as SchemaName
      ,OBJECT_NAME(objectid,st.dbid) as StoredProcedure
      ,max(cp.usecounts) as execution_count
      ,sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) as total_IO
      ,sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) 

           / (max(cp.usecounts)) as avg_total_IO
      ,sum(qs.total_physical_reads) as total_physical_reads
      ,sum(qs.total_physical_reads) / (max(cp.usecounts) * 1.0) as avg_physical_read    
      ,sum(qs.total_logical_reads) as total_logical_reads
      ,sum(qs.total_logical_reads) / (max(cp.usecounts) * 1.0) as avg_logical_read  
      ,sum(qs.total_logical_writes) as total_logical_writes
      ,sum(qs.total_logical_writes) / (max(cp.usecounts) * 1.0) as avg_logical_writes  
 FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
   join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
  where DB_NAME(st.dbid) is not null 
  and DB_NAME(st.dbid)='ricotest1'
  and cp.objtype = 'proc'
 group by DB_NAME(st.dbid),
          OBJECT_SCHEMA_NAME(objectid,st.dbid), 
          OBJECT_NAME(objectid,st.dbid) 
 order by sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) desc

 

 

 

3.查看SP執行時間

SELECT DB_NAME(st.dbid) as DBName
      ,OBJECT_SCHEMA_NAME(objectid,st.dbid) as SchemaName
      ,OBJECT_NAME(objectid,st.dbid) as StoredProcedure
      ,max(cp.usecounts) as execution_count
      ,sum(qs.total_elapsed_time) as total_elapsed_time
      ,sum(qs.total_elapsed_time) / max(cp.usecounts) as avg_elapsed_time
 FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
   join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
  where DB_NAME(st.dbid) is not null 
  and DB_NAME(st.dbid)='ricotest1' 
  and cp.objtype = 'proc'
 group by DB_NAME(st.dbid),
          OBJECT_SCHEMA_NAME(objectid,st.dbid), 
          OBJECT_NAME(objectid,st.dbid) 
 order by sum(qs.total_elapsed_time) desc

 

 

 

4.查看SP使用CPU資源

SELECT DB_NAME(st.dbid) DBName
      ,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName
      ,OBJECT_NAME(st.objectid,dbid) StoredProcedure
      ,max(cp.usecounts) Execution_count
      ,sum(qs.total_worker_time) total_cpu_time
      ,sum(qs.total_worker_time) / (max(cp.usecounts) * 1.0)  avg_cpu_time 
 FROM sys.dm_exec_cached_plans cp join sys.dm_exec_query_stats qs 

  on cp.plan_handle = qs.plan_handle
      CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 where DB_NAME(st.dbid) is not null 
 and DB_NAME(st.dbid)='ricotest1' 
 and cp.objtype = 'proc'
 group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid) 
 order by sum(qs.total_worker_time) desc
 

 

 

 

 

參考

[SQL SERVER][TSQL]獲取各種高成本查詢語法

Monitoring Stored Procedure Usage