存储过程的执行信息

ALTER PROC [dbo].[DBA_TroubleShooting]
AS
BEGINDECLARE &＃64;StartTime DATETIMEDECLARE &＃64;EndTime DATETIMESELECT &＃64;StartTime&＃61;CONVERT(VARCHAR,GETDATE()-1,112),&＃64;EndTime&＃61;CONVERT(VARCHAR,GETDATE(),112)DECLARE &＃64;Spreader VARCHAR(32)DECLARE loop_cursor CURSOR FOR SELECT spreadAccount FROM dbo.SpreadStatisticsAccount ssa WITH(NOLOCK)--WHERE spreadAccount&＃61;&＃39;LG0537&＃39; --分别用LG0537、LG0540测试OPEN loop_cursor;FETCH NEXT FROM loop_cursor INTO &＃64;SpreaderWHILE &＃64;&＃64;FETCH_STATUS &＃61; 0BEGIN-- 日登录用户DECLARE &＃64;DayLoginUserNum INTSELECT &＃64;DayLoginUserNum &＃61; COUNT(DISTINCT ais.UserId)FROM dbo.AccountsInfoSimple ais WITH(NOLOCK)INNER JOIN dbo.UserLoginRecord t WITH(NOLOCK) ON ais.userID &＃61; t.userIDWHERE ais.spreaderAccount &＃61; &＃64;Spreader AND ais.userType &＃61; 0AND t.loginDate BETWEEN &＃64;StartTime AND &＃64;EndTime AND t.typeCode &＃61; &＃39;02&＃39;;SELECT &＃64;DayLoginUserNum --注册、登录、充值、活跃数、游戏时长...--INSERT INTO dbo.SpreadAccountStatisticsNew(...)VALUES(...,&＃64;Spreader,&＃64;DayLoginUserNum,...)FETCH NEXT FROM loop_cursor INTO &＃64;SpreaderENDCLOSE loop_cursor;DEALLOCATE loop_cursor;
END

--存储过程的执行情况(b汇总值会小于a的实际值)
SELECT top 50 CASE when c.dbid &＃61; 32767then &＃39;Resource&＃39; else DB_NAME(c.dbid) end DbName--,OBJECT_SCHEMA_NAME(c.objectid,c.dbid) AS SchName,OBJECT_NAME(c.objectid,c.dbid) AS ObjName,MIN(b.creation_time) AS creation_time --有时候多个计划存在于同一储存过程的缓存中,MIN(b.last_execution_time) AS last_execution_time,SUM(a.usecounts) AS UseCount,SUM(b.total_logical_reads) / SUM(a.usecounts) AS avg_logical_reads,SUM(b.total_logical_writes) / SUM(a.usecounts) AS avg_logical_writes,SUM(b.total_worker_time) / SUM(a.usecounts) AS avg_worker_time,SUM(b.total_elapsed_time) / SUM(a.usecounts) AS avg_elapsed_time
FROM sys.dm_exec_cached_plans a with(nolock)INNER JOIN (SELECT MIN(creation_time) AS creation_time,MIN(last_execution_time) AS last_execution_time,SUM(total_logical_reads) AS total_logical_reads,SUM(total_logical_writes) AS total_logical_writes,SUM(total_worker_time) AS total_worker_time,SUM(total_elapsed_time) AS total_elapsed_time,plan_handle FROM sys.dm_exec_query_stats with(nolock)GROUP BY plan_handle) bON a.plan_handle &＃61; b.plan_handleCROSS APPLY sys.dm_exec_query_plan(a.plan_handle) c--CROSS APPLY sys.dm_exec_sql_text(a.plan_handle) c --用于筛选text信息
WHERE a.objtype &＃61; &＃39;Proc&＃39;--AND c.text like &＃39;Proc&＃39;
GROUP BY c.dbid,c.objectid
ORDER BY SUM(b.total_logical_reads) / SUM(a.usecounts) DESC;--SQLCmd in Proc or Batch
SELECT top 500 CASE when c.dbid &＃61; 32767 then &＃39;Resource&＃39; else DB_NAME(c.dbid) end DbName--,OBJECT_SCHEMA_NAME(c.objectid,c.dbid) AS SchName,OBJECT_NAME(c.objectid,c.dbid) AS ObjName,a.usecounts AS ProcExCount--,a.plan_handle, SUBSTRING (c.text,(b.statement_start_offset/2) &＃43; 1, ((CASE WHEN b.statement_end_offset &＃61; -1 THEN LEN(CONVERT(NVARCHAR(MAX), c.text)) * 2 ELSE b.statement_end_offset END - b.statement_start_offset)/2) &＃43; 1) RunSQL,b.creation_time --编译计划的时间,b.last_execution_time --上次开始执行计划的时间,b.execution_count AS SQLExCount--计划自上次编译以来所执行的次数,b.last_logical_reads --上次执行计划时所执行的逻辑读取次数,b.total_logical_reads/b.execution_count avg_logical_reads,b.last_worker_time --上次执行计划所用的 CPU 时间(微秒),b.last_elapsed_time --最近一次完成执行此计划所用的时间(微秒),b.total_elapsed_time/b.execution_count avg_elapsed_time --上次完成执行此计划所用的总时间--,b.*
FROM sys.dm_exec_cached_plans a with(nolock)INNER JOIN sys.dm_exec_query_stats b with(nolock)ON a.plan_handle &＃61; b.plan_handleCROSS APPLY sys.dm_exec_sql_text(b.sql_handle) c
WHERE a.objtype &＃61; &＃39;Proc&＃39;
and OBJECT_NAME(c.objectid,c.dbid)&＃61;&＃39;DBA_TroubleShooting&＃39;
order by a.plan_handle,b.sql_handle;--Execution Plan
SELECT top 50 CASE when b.dbid &＃61; 32767 then &＃39;Resource&＃39; else DB_NAME(b.dbid) end DbName,OBJECT_SCHEMA_NAME(b.objectid,b.dbid) AS SchName,OBJECT_NAME(b.objectid,b.dbid) AS ObjName,a.usecounts,a.plan_handle,b.query_plan
FROM sys.dm_exec_cached_plans a with(nolock)CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) b
WHERE a.objtype &＃61; &＃39;Proc&＃39;
and OBJECT_NAME(b.objectid,b.dbid)&＃61;&＃39;DBA_TroubleShooting&＃39;;--特定语句
select top 100 SUBSTRING (c.text,(b.statement_start_offset/2) &＃43; 1, ((CASE WHEN b.statement_end_offset &＃61; -1 THEN LEN(CONVERT(NVARCHAR(MAX), c.text)) * 2 ELSE b.statement_end_offset END - b.statement_start_offset)/2) &＃43; 1) RunSQL,b.creation_time --编译计划的时间,b.last_execution_time --上次开始执行计划的时间,b.execution_count AS SQLExCount--计划自上次编译以来所执行的次数,b.last_logical_reads --上次执行计划时所执行的逻辑读取次数,b.total_logical_reads/b.execution_count avg_logical_reads,b.last_worker_time --上次执行计划所用的 CPU 时间(微秒),b.last_elapsed_time --最近一次完成执行此计划所用的时间(微秒),b.total_elapsed_time/b.execution_count avg_elapsed_time --上次完成执行此计划所用的总时间--,b.sql_handle,b.plan_handle--,d.query_plan,c.text
FROM sys.dm_exec_query_stats b with(nolock)
CROSS APPLY sys.dm_exec_sql_text(b.sql_handle) c
CROSS APPLY sys.dm_exec_query_plan(b.plan_handle) d
where c.text like &＃39;%keywords%&＃39;