关于重新组织和重新生成索引sp_RefreshIndex的介绍

开始:

--------------------------------------------------------------------------------

在上周,客户反映一个系统问题,当处理大量数据的时候,出现网络超时。后来,我们跟踪测试,发现是由于索引碎片多而引起的网络超时。

解决方法,自然是重新组织和重新生成索引。在这里,我写了一个存储过程sp_RefreshIndex来实现。

存储过程sp_RefreshIndex:


代码如下:

use master
go
if object_id('sp_RefreshIndex') Is not null
    Drop Proc sp_RefreshIndex
Go
create proc sp_RefreshIndex
(
    @Reorganize_Fragmentation_Percent    smallint = 5    -- 当逻辑碎片百分比 > 5%  重新组织索引
    ,@Rebuild_Fragmentation_Percent        smallint = 30    -- 当逻辑碎片百分比 > 30% 重新生成索引
)
as
begin
    /* 调用方法:
.针对当前实例所有数据库:    exec sys.sp_MSforeachdb 'use ?;exec sp_RefreshIndex'
.针对当前数据库:            exec sp_RefreshIndex
    */

--对系统数据库不作重新组织索引和重新生成索引
    if (db_name() in ('master','model','msdb','tempdb')) return;

--如果逻辑碎片(索引中的无序页)的百分比 <= 5% ,就不作重新组织索引和重新生成索引
    if not exists(select 1 from sys.dm_db_index_physical_stats(db_id(),null,null,null,null) a where a.index_id>0 and a.avg_fragmentation_in_percent > @Reorganize_Fragmentation_Percent) return

print replicate('-',60)+char(13)+char(10)+replicate(' ',14)+N'对数据库 '+quotename(db_name())+N' 进行索引优化'+replicate(' ',20)+char(13)+char(10)

declare @sql nvarchar(2000),@str nvarchar(2000)

declare cur_x cursor for
        select 'alter index '+quotename(a.name)+' on '+quotename(object_schema_name(a.object_id))+'.'+quotename(object_name(a.object_id))+case when b.avg_fragmentation_in_percent<=@Rebuild_Fragmentation_Percent then ' reorganize;'else ' rebuild;'end as [sql]
                ,case when b.avg_fragmentation_in_percent<=@Rebuild_Fragmentation_Percent then N'重新组织索引:' else N'重新生成索引:'end +quotename(object_schema_name(a.object_id))+'.'+quotename(object_name(a.object_id))+'.'+quotename(a.name) as [str]
            from sys.indexes a
                inner join sys.dm_db_index_physical_stats(db_id(),null,null,null,null) b on b.object_id=a.object_id
                    and b.index_id=a.index_id   
            where a.index_id>0   
                and b.avg_fragmentation_in_percent > @Reorganize_Fragmentation_Percent
            order by object_name(a.object_id),a.index_id

open cur_x
    fetch next from cur_x into @sql,@str

while (@@fetch_status = 0)
    begin

exec(@sql)
        print @str
        fetch next from cur_x into @sql,@str

end
    close cur_x
    deallocate cur_x

end
go
exec sp_ms_marksystemobject 'sp_RefreshIndex'
go

调用方法:


代码如下:

use master
go
exec sys.sp_MSforeachdb 'use ?;exec sp_RefreshIndex'
go

注:我们根据实际的环境,修改@Reorganize_Fragmentation_Percent 和 @Rebuild_Fragmentation_Percent 值。

存储过程  sp_RefreshIndex 已在下面的环境测试通过:

SQL Server 2005 (SP4)/2008/2008R2/2012

扩展:

--------------------------------------------------------------------------------

我们可以把上面的SQL代码写入Job。再通过SQL Agent 服务,选择一个月或两个月执行一次job。

(0)

相关推荐

  • SQL2005重新生成索引的的存储过程 sp_rebuild_index 原创

    公司运营着的网站,流量很大,网站是交互式的,经常在过了三四个月的时候索引生成的碎片就很多,由于很大一部分页面没有生成静态,这就导致网站在打开的速度上会变慢. 以前都是手工右击索引重新生成,但是索引太多,操作起来费时费力,索引在网上找了个存储过程,自己整理了一下,执行的时候只需要选择相应的数据库,运行exec sp_rebuild_index即可,如下. USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE

  • Sql Server中的非聚集索引详细介

    非聚集索引,这个是大家都非常熟悉的一个东西,有时候我们由于业务原因,sql写的非常复杂,需要join很多张表,然后就泪流满面了...这时候就有DBA或者资深的开发给你看这个猥琐的sql,通过执行计划一分析...或许就看出了不该有的表扫描...万恶之源...然后给你在关键的字段加上非聚集索引后...才发现提速比阿斯顿马丁还要快...那么一个问题来了,为什么非聚集索引能提速这么快...怎么做到的???是不是非常的好奇??? 这篇我们来解开神秘面纱.  一:现象 先让我们一睹非聚集索引的真容,看看到底

  • SQL2005CLR函数扩展 - 关于山寨索引

    本文只是一个山寨试验品,思路仅供参考. --------------------------------------------------------------------------------原理介绍:索引建立 目录结构划分方案也只是很简易的实现了一下,通过unicode把任意连续的两个字符(中文或英文)分为4个字节来做四层目录,把索引的内容对应的主关键字(主要为了使用sql索引和唯一性)作为文件名,两个字符在索引内容中的位置作为文件后缀来存储.文件本身为0字节,不保存任何信息. 比如一

  • 在SQL SERVER中导致索引查找变成索引扫描的问题分析

    SQL Server 中什么情况会导致其执行计划从索引查找(Index Seek)变成索引扫描(Index Scan)呢? 下面从几个方面结合上下文具体场景做了下测试.总结.归纳. 1:隐式转换会导致执行计划从索引查找(Index Seek)变为索引扫描(Index Scan) Implicit Conversion will cause index scan instead of index seek. While implicit conversions occur in SQL Serve

  • mssql 建立索引第1/2页

    表的索引与附在一本书后面的索引非常相似.它可以极大地提高查询的速度.对一个较大的表来说,通过加索引,一个通常要花费几个小时来完成的查询只要几分钟就可以完成.因此没有理由对需要频繁查询的表增加索引. 注意: 当你的内存容量或硬盘空间不足时,也许你不想给一个表增加索引.对于包含索引的数据库,SQL Sever需要一个可观的额外空间.例如,要建立一个聚簇索引,需要大约1.2倍于数据大小的空间.要看一看一个表的索引在数据库中所占的空间大小,你可以使用系统存储过程sp_spaceused,对象名指定为被索

  • SQL2000 全文索引完全图解

    全文检索可以对varchar,text,image型字段进行检索,但一个表最多只能建一个全文索引SQL Server 2000 引入了对存储在 image 列中的这些类型的数据执行全文检索的能力.如果没有全文索引,对字符的模糊查询只能对基表进行全表扫描(或索引扫描),执行模糊查询都需要对全表扫描或索引扫描意味着消耗大量IO.如果模糊查询经常发生,会造成数据库性能恶化.本篇为简单起见,仅在varchar型字段上图文演示非常完整的而且是高效可行的全文索引创建及维护过程.1:在企业管理器中展开要建立全

  • MSSQL自动重建出现碎片的索引的方法分享

    1.索引碎片的产生? 由于在表里大量的插入.修改.删除操作而使索引页分裂.如果索引有了高的碎片,有两种情况,一种情况是扫描索引需要花费很多的时间,另一种情况是在查询的时候索引根本不使用索引,都会导致性能降低. 2.碎片类型分为: 2.1 内部破碎 由于索引页里的数据插入或修改操作而发生,以数据作为稀疏矩阵的形式的分布而结束,这将导致数据页的增加,从而增加查询时间. 2.2外部破碎 由于索引/数据页的数据插入或修改而发生,以页码分离和在文件系统里不连贯的新的索引页的分配而结束,数据库服务器不能利用

  • 详解sqlserver查询表索引

    SELECT   索引名称=a.name ,表名=c.name ,索引字段名=d.name ,索引字段位置=d.colid FROM sysindexes a JOIN sysindexkeys b ON a.id=b.id AND a.indid=b.indid JOIN sysobjects c ON b.id=c.id JOIN syscolumns d ON b.id=d.id AND b.colid=d.colid WHERE a.indid NOT IN(0,255) -- and

  • 理解Sql Server中的聚集索引

    说到聚集索引,我想每个码农都明白,但是也有很多像我这样的猥程序员,只能用死记硬背来解决这个问题,什么表中只能建一个聚集索引,然后又扯到了目录查找来帮助读者记忆....问题就在这里,我们不是学文科,,,不需要去死记硬背,,,我们需要的就是能看到在眼里面的真实东西.....我们都喜欢聚集索引,因为它能够把无序的堆表记录变成有序,还玩起了B树...这样就把复杂度从N降低到了LogMN... 这样的话逻辑读,物理读就下来了.  一:现象 1:无索引的情况 还是老规矩,看个例子感受下,首先我有一个Prod

  • MSSQL 大量数据时,建立索引或添加字段后保存更改提示超时的解决方法

    一般我们都喜欢用数据库管理器的UI来对数据表结构进行更改,然后自然而然地点"保存" 按钮进行保存,但数据量比较大的时候,用这招往往会出现"无法创建索引"IX_索引名". 超时时间已到.在操作完成之前超时时间已过或服务器未响应. "这种错误.一时不知所措,蜡人张的文章 复制代码 代码如下: 修改表属性后使用"索引/键"对话框为一个大型表(记录数13,239,473)创建索引,提示: - 无法创建索引"IX_TableN

随机推荐