1、建立t_mon_table_stat 用于存过需要更新统计信息的表
2、查找需要更新统计信息的表
3、创建存储过程
4、然后将改存储过程放到作业里面去就OK
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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29507357/viewspace-1720546/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29507357/viewspace-1720546/