由于公司最近的数据库服务器CPU非常不稳定。

wKioL1brtJnTPHFYAAA6lHonNxU307.png

于是乎下手查找问题的来源。想了下，只能从存储过程的执行状态中下手。

查了下资料，发现MSSQL中的系统表sys.dm_exec_procedure_stats会记录存储过程的执行状态数据。字段含义就不累述了。开始干活：

1、将数据插入一张新表记录

select convert(nvarchar(10),getdate(),121) as countdate,d.* into procedure_stats_daily 
from SYS.procedures S JOIN sys.dm_exec_procedure_stats D  ON S.object_id=D.object_id


2、每天作业执行，将数据插入记录表

insert into procedure_stats_daily
select convert(nvarchar(10),getdate(),121),d.*
from SYS.procedures S JOIN SYS.dm_exec_procedure_stats D  ON S.object_id=D.object_id


3、查看时间@Date的执行情况

CREATE PROC CS_RO_Base_GetDailyProcedureStats
@Date datetime=''
AS
BEGIN
    DECLARE @Date1 datetime
    SET @Date1=@Date+1
    SELECT a.object_id,c.name as '存储过程名称',
    CASE WHEN a.cached_time>=@Date THEN ISNULL(a.execution_count,0) ELSE ISNULL(a.execution_count-b.execution_count,0)END as '执行次数',
    CASE WHEN a.cached_time>=@Date THEN ISNULL(a.total_worker_time/1000000,0)ELSE ISNULL((a.total_worker_time-b.total_worker_time)/1000000,0)END as '执行时间/秒',
    CASE WHEN a.cached_time>=@Date THEN convert(numeric(18,4),(ISNULL(a.total_worker_time,0)))/ISNULL(a.execution_count,0)/1000000 
    WHEN ISNULL(a.execution_count-b.execution_count,0)=0 THEN 0.00 
    ELSE convert(numeric(18,4),(a.total_worker_time-b.total_worker_time))/(a.execution_count-b.execution_count)/1000000 end as '平均执行时间/秒' 
    FROM
    (
    select object_id,cached_time,plan_handle,isnull(execution_count,0) as execution_count,isnull(total_worker_time,0)as total_worker_time 
    from procedure_stats_daily where countdate=@Date1
    )a
    FULL JOIN 
    (
    select object_id,cached_time,plan_handle,isnull(execution_count,0) as execution_count,isnull(total_worker_time,0)as total_worker_time 
    from procedure_stats_daily where countdate=@Date
    )b on a.plan_handle=b.plan_handle
    left join sys.objects c on a.object_id=c.object_id
    order by CASE WHEN a.cached_time>=@Date THEN ISNULL(a.execution_count,0) ELSE ISNULL(a.execution_count-b.execution_count,0)END desc
END


 

根据以上的数据，我们可以查出最频繁的存储过程和效率最低的存储过程，进而给我们的优化工作提供了方向。