当前位置：首页 > 数据库 > MSSQL教程 > 关于更新SQLserver统计信息的存储过程 > 正文

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

来源：这里教程网 时间：2026-03-02 10:42:54 作者：
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

编辑推荐：

相关推荐

最新软件资讯

优化临时表使用，SQL语句性能提升100倍
优化临时表使用，SQL语句性能提升100倍

热文推荐

天极热推