sql创建重新组织索引维护失败 sqlserver重建索引强制停止

 

目前我们的优化主要是在数据库层面，而索引又是重要的一环。但实质上所有的索引都将随着时间产生碎片，从而产生性能的下降，在先前万科的数据库中检查发现，几乎所有的索引碎片都超过了90%，这会严重影响性能。因此，在这里找来一些索引维护、整理方面的知识和大家一起学习。

索引需要维护的原因

        几乎所有的UPDATE、INSERT、DELETE活动都将引起索引比最初创建时变得更无组织。页拆分更多，大量的页上只有很少的数据，因此满足每个SELECT需要更多的I/O。数据和索引的碎片越多，应用程序就会更慢，数据花费的空间就更多。所以我们需要定期的维护索引。  

有什么方法来维护？ 

     基本上我们可以使用数据库维护向导来执行索引重建，创建维护计划来完成。但是它有一定的的缺陷。首先，用维护向导来配置和完成索引重建是不慎重的。它将重建每一个索引，不管它是否需要重建。如果你有一个有很多大表和大量索引的大数据库，这会出问题，因为不加区别的重建整个数据库的索引会花费很长的时间，会使你的维护窗口不可用。问题在于，要么全部重建，要么全部不重建，你根本不能以任何方式分批处理数据库的表。

    那么有什么别的能做吗？可以写一个脚本来重建选择的表的索引。这样你能对数据库分批处理以减少在重建索引时你维护窗口执行的时间。你需要将这个时间减小到最少，因为重建索引会对表执行排它锁，在重建索引期间禁止用户访问。所以你可以每周的每个工作日的晚上重建五分之一表的索引，所有的索引至少一周做一次。然而，这也是不慎重的――我们将重建所有表的索引而不论数据和索引是否是有碎片。

        因此这里推荐选择性的重建索引。你需要检查表的索引和数据的碎片，保留数据，据此操作，重建索引要用确定的且区别对待的方式。仅仅通过这样系统的方法，你可以仅重建那些实际需要重建的表的数据和索引。而且也只有这种方式能最小化索引重建的时间。在整个索引重建期间，如果你不想影响你的用户的话，减少索引重建的时间是至关重要的。 

那么我们怎样可以解决呢？

可以使用命令

   DBCC SHOWCONTIG()

DBCC SHOWCONTIG是SQLServer提供来检查索引碎片情况的工具。在以前的版本里（7.0和更早的版本），这个命令只输出文本，如果手工处理这个命令很好，然而，要实现自动化目的，它会带来严重的问题。那意味着你要循环执行每一个表并将结果输出到文本文件，然后为了读和解释原文的输出结果以便获得你寻找的信息，需要进行烦人的结构化处理。 

     SQLServer2000对DBCC SHOWCONTIG()命令引进了一个关键子句，名为WITH TABLERESULTS。这意味着你能运行这个命令然后将捕获的数据直接输出到表里，而不是还需要使用XP_CMDSHELL来操作的文本文件里。

在SQLServer2000里，这意味着你能结构化的循环处理表，通过在它们上面运行DBCC SHOWCONTIG命令以将捕获碎片信息插入表中。然后你能循环使用这个结果，根据碎片的情况，选择性的进行碎片整理。可以用下面的存储过程实现： 

CREATE PROCEDURE sp_defragment_indexes @maxfrag DECIMAL 
 AS  
 --声明变量 
 SET NOCOUNT ON 
 DECLARE @tablename VARCHAR (128) 
 DECLARE @execstr VARCHAR (255) 
 DECLARE @objectid INT 
 DECLARE @objectowner VARCHAR(255) 
 DECLARE @indexid INT 
 DECLARE @frag DECIMAL 
 DECLARE @indexname CHAR(255) 
 DECLARE @dbname sysname 
 DECLARE @tableid INT 
 DECLARE @tableidchar VARCHAR(255) 
 --检查是否在用户数据库里运行 
 SELECT @dbname = db_name() 
 IF @dbname IN ('master', 'msdb', 'model', 'tempdb') 
 BEGIN 
 PRINT 'This procedure should not be run in system databases.' 
 RETURN 
 END 
 --第1阶段:检测碎片 
 --声明游标 
 DECLARE tables CURSOR FOR 
 SELECT convert(varchar,so.id) 
 FROM sysobjects so 
 JOIN sysindexes si 
 ON so.id = si.id 
 WHERE so.type ='U' 
 AND si.indid < 2 
 AND si.rows > 0 
 -- 创建一个临时表来存储碎片信息 
 CREATE TABLE #fraglist ( 
 ObjectName CHAR (255), 
 ObjectId INT, 
 IndexName CHAR (255), 
 IndexId INT, 
 Lvl INT, 
 CountPages INT, 
 CountRows INT, 
 MinRecSize INT, 
 MaxRecSize INT, 
 AvgRecSize INT, 
 ForRecCount INT, 
 Extents INT, 
 ExtentSwitches INT, 
 AvgFreeBytes INT, 
 AvgPageDensity INT, 
 ScanDensity DECIMAL, 
 BestCount INT, 
 ActualCount INT, 
 LogicalFrag DECIMAL, 
 ExtentFrag DECIMAL) 
 --打开游标 
 OPEN tables 
 -- 对数据库的所有表循环执行dbcc showcontig命令 
 FETCH NEXT 
 FROM tables 
 INTO @tableidchar 
 WHILE @@FETCH_STATUS = 0 
 BEGIN 
 --对表的所有索引进行统计 
 INSERT INTO #fraglist 
 EXEC ('DBCC SHOWCONTIG (' + @tableidchar + ') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS') 
 FETCH NEXT 
 FROM tables 
 INTO @tableidchar 
 END 
 -- 关闭释放游标 
 CLOSE tables 
 DEALLOCATE tables 
 -- 为了检查，报告统计结果 
 SELECT * FROM #fraglist 
 --第2阶段: (整理碎片) 为每一个要整理碎片的索引声明游标 
 DECLARE indexes CURSOR FOR 
 SELECT ObjectName, ObjectOwner = user_name(so.uid), ObjectId, IndexName, ScanDensity 
 FROM #fraglist f 
 JOIN sysobjects so ON f.ObjectId=so.id 
 WHERE ScanDensity <= @maxfrag 
 AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0 
 -- 输出开始时间 
 SELECT 'Started defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE()) 
 --打开游标 
 OPEN indexes 
 --循环所有的索引 
 FETCH NEXT 
 FROM indexes 
 INTO @tablename, @objectowner, @objectid, @indexname, @frag 
 WHILE @@FETCH_STATUS = 0 
 BEGIN 
 SET QUOTED_IDENTIFIER ON 
 SELECT @execstr = 'DBCC DBREINDEX (' +''''+ RTRIM(@objectowner) + '.' + RTRIM(@tablename) +'''' + 
 ', ''' + RTRIM(@indexname) + ''') WITH NO_INFOMSGS' 
 SELECT 'Now executing: ' 
 SELECT(@execstr) 
 EXEC (@execstr) 
 SET QUOTED_IDENTIFIER OFF 
 FETCH NEXT 
 FROM indexes 
 INTO @tablename, @objectowner, @objectid, @indexname, @frag 
 END 
 -- 关闭释放游标 
 CLOSE indexes 
 DEALLOCATE indexes 
 -- 报告结束时间 
 SELECT 'Finished defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE()) 
 -- 删除临时表 
 DROP TABLE #fraglist 
 GO

使用

这个存储过程应该创建在master数据库里，以便你能在服务器上的任何用户数据库里使用。

在用户数据库里通过传递一个参数（MAXFRAG）来运行。该参数是一个百分比值。意思是任何索引的碎片扫描密度小于这个值。例如，如果你想要整理那些扫描密度小于95％的索引的碎片：

USE pubs

GO

EXEC sp_defragment_indexes 95.00

局限 

这个过程依赖于的标准是扫描密度，但扫描密度对于那些跨越多个文件的索引来说不是一个有效的标准。如果你的索引确实跨越多个文件，你需要用另一个标准（如Logical Frag）来更改这个存储过程。然而，这类更改超过本文的范围；如果你的索引跨越多个文件，你需要做更多的工作。 

怎样做，做什么？ 

这个存储过程有两个独特的部分。

第1阶段

在这部分里，存储过程通过在数据库里的每个表上运行下面的命令来检查索引碎片：

DBCC SHOWCONTIG (‘tablename') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS

命令的结果存储在预先创建的临时表#fraglist里。这里就会用到DBCC SHOWCONTIG 语句的WITH TABLERESULTS的好处，仅这一点，真正的节省了太多的以前版本得到同样结果所花费的麻烦和精力。

你应该注意该存储过程工作的数据库的表的拥有者是不是dbo，通常是。我发现我最初的版本不起作用，当时一个软件经销商给我们提供的新系统的数据库里就出现了拥有者不是dbo的表。当我在这个新系统上第一次运行我的碎片整理过程时，这个程序的缺点就暴露无遗了，最后彻底失败。这个问题实际上出现在碎片整理阶段（阶段2），因为表在这里要引用表名，而在阶段1，DBCC SHOWCONTIG命令引用的时表的ID即object_id。

第2阶段

这儿使用了另一个游标来循环处理表#fraglist里的记录，这些记录是那些扫描密度低于传给存储过程参数的那个阈值的表：

DBCC DBREINDEX()

执行的结果以输出文件的形式显示在表#fraglist的内容之后，以便你能查看表和索引的碎片，正如屏幕上所显示的那样，也可以通过查看DBCC DBREINDEX()执行的结果列表来查看采取的动作。利用这些你也能推导出每个索引重建的时间。 

输出结果是什么意思？ 

输出示例：

上面是在Excel里打开的存储过程输出文本文件的一个截屏。为了简洁一些列已经删掉了。你需要用文本文件向导来打开它，选择固定列宽，打开导入从第三行起。

这里，你能够检查你选择检查的数据库里的表的扫描密度。

在接下来的输出文件里（DBCC SHOWCONTIG输出结果的后面），你会发现正被重建索引的每个表或索引的细节，这部分的开始和结束部分都有重建索引的开始和结束时间。如下面例子显示的那样： 

为什么不使用DBCC INDEXDEFRAG()去减少阻塞？

答案是如果你想要或者需要的话就使用它。如果你需要7×24小时的在线操作，那么DBCC DBREINDEX()的排他表锁不适合你的业务，你可以使用它来代替DBCC DBREINDEX()。然而，你需要适当改变一下语法，因为它们是不相同的（谢谢，微软！）。如果你不知道它们的区别，这里有一个简单的摘要：当运行DBCC DBREINDEX()的时候，必须对表有排他锁，因为它是一个完全的，彻头彻尾的索引重建操作。而DBCC INDEXDEFRAG()就不那么完全了，在线的操作试图改善你索引的环境而不至于引起阻塞和中断OLTP（希望如此）。我必须承认我从来不用DBCC INDEXDEFRAG()，因为很幸运的是我的系统不需要严格的7×24在线且要求不阻塞，所以我不敢担保是否有效率。我已经理解它不是和DBCC DBREINDEX一样有效率。然而它的确比什么都没有强，所以如果你的数据库运行一个全球的WEB站点并且从来不能停止，这在今天这也很普遍，那么你需要使用它来代替以改变这个存储过程。 

添加到调度任务里 

        可以将这个维护作为一个独立的任务或在你存在的维护作业里的一个步骤，让其在每周末凌晨执行，这样可以让SQL自动的去维护索引，减少对用户的影响。

结论 

       现在的数据库维护开销很大，本文提供的方法在影响用户和执行时间上保持最小的同时，也提供了高效和良好的数据库服务器维护。希望这篇文章和代码能帮助我们在维护数据库时起到一点作用。