SQL Server索引维护指导.docx

1、SQL Server索引维护指导SQL Server索引维护指导索引在数据库相关工作者的日常工作中占据了很重要的位置，索引需要牵涉到索引创建、优化和维护多方面的工作，本文以实例结合相关原理来介绍索引维护相关的知识。文中的相关代码，也可以满足多数情况下索引的维护需求。实现步骤1、以什么标准判断索引是否需要维护?2、索引维护的方法有哪些?3、能否方便地整理出比较通用的维护过程，实现自动化维护?一、 以什么标准判断索引是否需要维护?由于本文集中讨论索引维护相关，所以我们暂且抛开创建的不合理的那些索引，仅从维护的角度来讨论。从索引维护的角度来讲，最主要的参考标准就是索引碎片的大小。通常情况下，索引碎片

2、在10%以内，是可以接受的。下面介绍获取索引碎片的方法：SQL Server 2000: DBCC SHOWCONTIGSQL Server 2005: sys.dm_db_index_physical_stats实例(取db_test数据库所有索引碎片相关信息)：SQL Server 2000:USE db_test;GODBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXESGO SQL Server 2005:DECLARE db_name VARCHAR(256)SET db_name=db_test SELECT db_name(a.databas

3、e_id) db_name,c.name table_name, b.name index_name, a.avg_fragmentation_in_percentFROM sys.dm_db_index_physical_stats (DB_ID(db_name), NULL,NULL, NULL, Limited) AS aJOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_idJOINsys.tables AS c ON a.object_id = c.object_idWHERE a.i

4、ndex_id0 AND a.avg_fragmentation_in_percent5 -碎片程度大于5二、 索引维护的方法有哪些?注：维护方式的选择，一方面要考虑是否是联机维护，另一方面就是速度上的考虑。一般碎片30%时，索引重建的速度比重新组织要快。1、联机维护SQL Server2000:DBCC INDEXDEFRAG 重新组织索引，占用资源少，锁定资源周期短，可联机进行。SQL Server 2005:联机重新组织：ALTER INDEX index_name ON table_nameREORGANIZE;2、 联机重建：ALTER INDEX index_name ON tab

5、le_nameREBUILD WITH (FILLFACTOR = 85, SORT_IN_TEMPDB = OFF,STATISTICS_NORECOMPUTE = ON,ONLINE = ON);3、脱机维护SQL Server2000：DBCC DBREINDEXSQL Server 2005：ALTER INDEX indexname ON table_name REBUILD;CREATE INDEX WITH DROP_EXISTING4、能否方便地整理出比较通用的维护过程，实现自动化维护?a) 获取及查看所有索引的碎片情况SQL Server2000:/*描述：获取服务器上所有数

6、据库的逻辑碎片率5的索引信息适用：SqlServer2000以后版本*/SET NOCOUNT ONDECLARE db_name varchar(128)DECLARE tablename varchar(128)DECLARE table_schema varchar(128)DECLARE execstr varchar(255)DECLARE objectid intDECLARE indexid intDECLARE frag decimalDECLARE maxfrag decimalDECLARE sql varchar(8000)- Decide on the maximum

7、fragmentation to allow for.SELECT maxfrag = 5 - Create the table.if not exists(select 1 from sys.tables where name = dba_manage_index_defrag)create table dba_manage_index_defrag(db_name varchar(255),table_name varchar(255),index_name varchar(255),avg_fragmentation_in_percent real,write_time datetime

8、 default getdate()if not exists(select 1 from dbo.sysobjects where name = dba_manage_index_defrag_temp)CREATE TABLE dba_manage_index_defrag_temp (db_name char(255) default ,ObjectName char(255),ObjectId int,IndexName char(255),IndexId int,Lvl int,CountPages int,CountRows int,MinRecSize int,MaxRecSiz

9、e int,AvgRecSize int,ForRecCount int,Extents int,ExtentSwitches int,AvgFreeBytes int,AvgPageDensity int,ScanDensity decimal,BestCount int,ActualCount int,LogicalFrag decimal,ExtentFrag decimal)- Declare a cursor.DECLARE databases CURSOR FORselect name from master.dbo.sysdatabases where dbid4- Open t

10、he cursor.open databasesfetch databases into db_namewhile (fetch_status=0)begininsert into dba_manage_index_defrag_temp (ObjectName ,ObjectId ,IndexName,IndexId ,Lvl ,CountPages ,CountRows ,MinRecSize ,MaxRecSize ,AvgRecSize ,ForRecCount ,Extents ,ExtentSwitches ,AvgFreeBytes ,AvgPageDensity ,ScanDe

11、nsity ,BestCount ,ActualCount ,LogicalFrag ,ExtentFrag )exec(use +db_name+; dbcc showcontig with FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS)update dba_manage_index_defrag_tempsetdb_name = db_namewhere db_name = fetch next from databases into db_nameendclose databasesdeallocate databasesinsert into

12、 dba_manage_index_defrag(db_name ,table_name ,index_name ,avg_fragmentation_in_percent )select db_name,ObjectName table_name,indexname index_name,LogicalFrag avg_fragmentation_in_percent from dba_manage_index_defrag_temp where logicalfrag5- Delete the temporary table.DROP TABLE dba_manage_index_defr

13、ag_tempGOSELECT * FROM dba_manage_index_defrag -查看结果SQL Server 2005:/*描述：只显示逻辑碎片率大于5%的索引信息限制：针对SqlServer2005以后版本。功能：对数据库服务器所有非系统数据库进行索引碎片检查返回碎片率5%的索引信息*/create proc p_dba_manage_get_index_defrageasset nocount on if not exists(select 1 from sys.tables where name = dba_manage_index_defrag)create table

14、 dba_manage_index_defrag(db_name varchar(255),table_name varchar(255),index_name varchar(255),avg_fragmentation_in_percent real,write_time datetime default getdate() declare db_name nvarchar(40)set db_name = declare cur_db_name cursor for select name from sys.databaseswhere database_id 4 and state =

15、 0open cur_db_namefetch cur_db_name into db_namewhile (fetch_status=0)begininsert into dba_manage_index_defrag(db_name,table_name,index_name,avg_fragmentation_in_percent)SELECT db_name(a.database_id) db_name,c.name table_name, b.name index_name, a.avg_fragmentation_in_percentFROM sys.dm_db_index_phy

16、sical_stats (DB_ID(db_name), null,NULL, NULL, Limited) AS aJOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_idjoin sys.tables as c on a.object_id = c.object_idwhere a.index_id0 and a.avg_fragmentation_in_percent5fetch next from cur_db_name into db_nameendCLOSE cur_db_nameD

17、EALLOCATE cur_db_nameGOselect * from dba_manage_index_defrag 查看结果b) 根据索引碎片的情况自动选择合适的处理方法针对Sql Server2000的联机维护：/*Perform a USE to select the database in which to run the script.*/- Declare variablesSET NOCOUNT ON;DECLARE tablename varchar(128);DECLARE execstr varchar(255);DECLARE objectid int;DECLARE

18、 indexid int;DECLARE frag decimal;DECLARE maxfrag decimal; - Decide on the maximum fragmentation to allow for.SELECT maxfrag = 30.0;- Declare a cursor.DECLARE tables CURSOR FORSELECT TABLE_SCHEMA+.+TABLE_NAME -MSDN上面直接使用TABLE_NAME，如果SCHEMA不是DBO就会出错FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_TYPE = BAS

19、E TABLE;- Create the table.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 decima

20、l,BestCount int,ActualCount int,LogicalFrag decimal,ExtentFrag decimal);- Open the cursor.OPEN tables;- Loop through all the tables in the database.FETCH NEXTFROM tablesINTO tablename;WHILE FETCH_STATUS = 0BEGIN- Do the showcontig of all indexes of the tableINSERT INTO #fraglist EXEC (DBCC SHOWCONTI

21、G ( + tablename + ) WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS);FETCH NEXTFROM tablesINTO tablename;END;- Close and deallocate the cursor.CLOSE tables;DEALLOCATE tables;- Declare the cursor for the list of indexes to be defragged.DECLARE indexes CURSOR FORSELECT ObjectName, ObjectId, IndexId,

22、 LogicalFragFROM #fraglistWHERE LogicalFrag = maxfragAND INDEXPROPERTY (ObjectId, IndexName, IndexDepth) 0;- Open the cursor.OPEN indexes;- Loop through the indexes.FETCH NEXTFROM indexesINTO tablename, objectid, indexid, frag;WHILE FETCH_STATUS = 0BEGINPRINT Executing DBCC INDEXDEFRAG (0, + RTRIM(t

23、ablename) + , + RTRIM(indexid) + ) - fragmentation currently + RTRIM(CONVERT(varchar(15),frag) + %;SELECT execstr = DBCC INDEXDEFRAG (0, + RTRIM(objectid) + , + RTRIM(indexid) + );EXEC (execstr);FETCH NEXTFROM indexesINTO tablename, objectid, indexid, frag;END;- Close and deallocate the cursor.CLOSE

24、 indexes;DEALLOCATE indexes;- Delete the temporary table.DROP TABLE #fraglist;GO针对SQL Server 2000的脱机维护：sp_msforeachtable command1=dbcc dbreindex(?,85)针对SQL Server 2005的通用维护过程(碎片小于30%的联机组织，碎片=30%的脱机重建)：- ensure a USE statement has been executed first.SET NOCOUNT ON;DECLARE objectid int;DECLARE indexi

25、d int;DECLARE partitioncount bigint;DECLARE schemaname sysname;DECLARE objectname sysname;DECLARE indexname sysname;DECLARE partitionnum bigint;DECLARE partitions bigint;DECLARE frag float;DECLARE command varchar(8000);- ensure the temporary table does not existIF EXISTS (SELECT name FROM sys.object

26、s WHERE name = work_to_do)DROP TABLE work_to_do;- conditionally select from the function, converting object and index IDs to names.SELECTobject_id AS objectid,index_id AS indexid,partition_number AS partitionnum,avg_fragmentation_in_percent AS fragINTO work_to_doFROM sys.dm_db_index_physical_stats (

27、DB_ID(), NULL, NULL , NULL, LIMITED)WHERE avg_fragmentation_in_percent 10.0 AND index_id 0;- Declare the cursor for the list of partitions to be processed.DECLARE partitions CURSOR FOR SELECT * FROM work_to_do; - Open the cursor.OPEN partitions;- Loop through the partitions.FETCH NEXTFROM partitions

28、INTO objectid, indexid, partitionnum, frag;WHILE FETCH_STATUS = 0BEGIN;SELECT objectname = o.name, schemaname = s.nameFROM sys.objects AS oJOIN sys.schemas as s ON s.schema_id = o.schema_idWHERE o.object_id = objectid;SELECT indexname = name FROM sys.indexesWHERE object_id = objectid AND index_id =

29、indexid;SELECT partitioncount = count (*) FROM sys.partitionsWHERE object_id = objectid AND index_id = indexid;- 30 is an arbitrary decision point at which to switch between reorganizing andrebuildingIF frag 5BEGIN;SELECT command = ALTER INDEX + indexname + ON + schemaname + . + objectname+ REORGANIZE;IF partitioncount 1SELECT command = command + PARTITION= + CONVERT (CHAR, partitionnum);EXEC (command);END;IF frag = 30.0BEGIN;SELECT command = ALTER INDEX + indexname + ON + schemana