1、建立t_mon_table_stat 用于存过需要更新统计信息的表

2、查找需要更新统计信息的表

点击(此处)折叠或打开

insert into t_mon_table_stat
SELECT DISTINCT SP.rows/SSI.rowmodctr rowdiff,
OBJECT_NAME(SI.object_id) AS Table_Name ,
SI.name AS Statistics_Name ,
STATS_DATE(SI.object_id, SI.index_id) AS Last_Stat_Update_Date ,
SSI.rowmodctr AS RowModCTR ,
SP.rows AS Total_Rows_In_Table ,
\'UPDATE STATISTICS [\' + SCHEMA_NAME(SO.schema_id) + \'].[\'
+ OBJECT_NAME(SI.object_id) + \']\' + SPACE(2) + SI.name AS Update_Stats_Script ,current_timestamp,0,null
FROM sys.indexes AS SI( NOLOCK )
INNER JOIN sys.objects AS SO( NOLOCK ) ON SI.object_id = SO.object_id
INNER JOIN sys.sysindexes SSI( NOLOCK ) ON SI.object_id = SSI.id
AND SI.index_id = SSI.indid
INNER JOIN sys.partitions AS SP ON SI.object_id = SP.object_id
WHERE SSI.rowmodctr > 0
AND STATS_DATE(SI.object_id, SI.index_id) IS NOT NULL
AND SO.type = \'U\'
and abs(SP.rows-SSI.rowmodctr)>10000
and (SP.rows/SSI.rowmodctr <0.4 or SP.rows/SSI.rowmodctr >2.5)
and OBJECT_NAME(SI.object_id) not like \'%History_%\' --排除历史表
and STATS_DATE(SI.object_id, SI.index_id)< dateadd(dd,-2,getdate()) --最近7天未更新统计信息
and OBJECT_NAME(SI.object_id) not like \'%-%\'

3、创建存储过程

点击(此处)折叠或打开

create procedure dbo.p_mon_upate_table_statistics
as
begin
declare
@datetime datetime
set @datetime=getdate()
declare update_tab_stat_cur cursor for select distinct table_name ,Update_Stats_Script,check_date from t_mon_table_stat
where check_date>dateadd(dd,-1,@datetime) and check_date<dateadd(dd,1,@datetime)
open update_tab_stat_cur
--声明变量
declare @sql varchar(1000)
declare @table_name varchar(100)
declare @check_date datetime
fetch next from update_tab_stat_cur into @table_name ,@sql ,@check_date
while @@FETCH_STATUS =0
begin
---更新check_status状态
update a set a.Check_Status=1 from t_mon_table_stat a where a.check_date =@check_date and a.table_name =@table_name and Check_Status in (0);
exec(@sql)
if @@ROWCOUNT >0
begin
---更新update_Date 和 Check_Status的值
update a set a.update_Date=getdate(),a.Check_Status=2 from t_mon_table_stat a where a.check_date =@check_date and a.table_name =@table_name and Check_Status=1;
end
fetch next from update_tab_stat_cur into @table_name ,@sql ,@check_date
end
---关闭游标
close update_tab_stat_cur
---释放游标资源
deallocate update_tab_stat_cur
end

4、然后将改存储过程放到作业里面去就OK

ITPub博客

关于更新SQLserver统计信息的存储过程