SQLServer索引维护指导
索引在数据库相关工作者的日常工作中占据了很重要的位置，索引需要牵涉到索引创建、优化和维护多方面的工作，本文以实例结合相关原理来介绍索引维护相关的知识。文中的相关代码，也可以满足多数情况下索引的维护需求。
实现步骤
1、以什么标准判断索引是否需要维护?
2、索引维护的方法有哪些?
3、能否方便地整理出比较通用的维护过程，实现自动化维护?
一、以什么标准判断索引是否需要维护?
由于本文集中讨论索引维护相关，所以我们暂且抛开创建的不合理的那些索引，仅从维护的角度来讨论。从索引维护的角度来讲，最主要的参考标准就是索引碎片的大小。通常情况下，索引碎片在10%以内，是可以接受的。下面介绍获取索引碎片的方法：
SQLServer2000:DBCCSHOWCONTIG
SQLServer2005:sys.dm_db_index_physical_stats
实例(取db_test数据库所有索引碎片相关信息)：
SQLServer2000:
USE[db_test];
GO
DBCCSHOWCONTIGWITHTABLERESULTS,ALL_INDEXES
GO
SQLServer2005:
DECLARE@db_nameVARCHAR(256)
SET@db_name=''db_test''
SELECT
db_name(a.database_id)[db_name],
c.name[table_name],
b.name[index_name],
a.avg_fragmentation_in_percent
FROM
sys.dm_db_index_physical_stats(DB_ID(@db_name),NULL,NULL,NULL,''Limited'')ASa
JOIN
sys.indexesASbONa.object_id=b.object_idANDa.index_id=b.index_id
JOIN
sys.tablesAScONa.object_id=c.object_id
WHERE
a.index_id>0
ANDa.avg_fragmentation_in_percent>5-–碎片程度大于5
二、索引维护的方法有哪些?
注：维护方式的选择，一方面要考虑是否是联机维护，另一方面就是速度上的考虑。一般碎片<=30%时，使用重新组织的方法速度比索引重建快;碎片>30%时，索引重建的速度比重新组织要快。
1、联机维护
SQLServer2000:
DBCCINDEXDEFRAG重新组织索引，占用资源少，锁定资源周期短，可联机进行。
SQLServer2005:
联机重新组织：
ALTERINDEX[index_name]ON[table_name]
REORGANIZE;
2、联机重建：
ALTERINDEX[index_name]ON[table_name]
REBUILDWITH(FILLFACTOR=85,SORT_IN_TEMPDB=OFF,
STATISTICS_NORECOMPUTE=ON,ONLINE=ON);
3、脱机维护
SQLServer2000：DBCCDBREINDEX
SQLServer2005：ALTERINDEX[indexname]ON[table_name]REBUILD;
CREATEINDEXWITHDROP_EXISTING
4、能否方便地整理出比较通用的维护过程，实现自动化维护?
a)获取及查看所有索引的碎片情况
SQLServer2000:
/
描述：获取服务器上所有数据库的逻辑碎片率>5的索引信息
适用：SqlServer2000以后版本
/
SETNOCOUNTON
DECLARE@db_namevarchar(128)
DECLARE@tablenamevarchar(128)
DECLARE@table_schemavarchar(128)
DECLARE@execstrvarchar(255)
DECLARE@objectidint
DECLARE@indexidint
DECLARE@fragdecimal
DECLARE@maxfragdecimal
DECLARE@sqlvarchar(8000)
--Decideonthemaximumfragmentationtoallowfor.
SELECT@maxfrag=5
--Createthetable.
ifnotexists(select1fromsys.tableswherename=''dba_manage_index_defrag'')
createtabledba_manage_index_defrag
([db_name]varchar(255)
,[table_name]varchar(255)
,[index_name]varchar(255)
,avg_fragmentation_in_percentreal
,write_timedatetimedefaultgetdate()
)
ifnotexists(select1fromdbo.sysobjectswherename=''dba_manage_index_defrag_temp'')
CREATETABLEdba_manage_index_defrag_temp(
[db_name]char(255)default'''',
ObjectNamechar(255),
ObjectIdint,
IndexNamechar(255),
IndexIdint,
Lvlint,
CountPagesint,
CountRowsint,
MinRecSizeint,
MaxRecSizeint,
AvgRecSizeint,
ForRecCountint,
Extentsint,
ExtentSwitchesint,
AvgFreeBytesint,
AvgPageDensityint,
ScanDensitydecimal,
BestCountint,
ActualCountint,
LogicalFragdecimal,
ExtentFragdecimal)
--Declareacursor.
DECLAREdatabasesCURSORFOR
select
name
from
master.dbo.sysdatabases
where
dbid>4
--Openthecursor.
opendatabases
fetchdatabasesinto@db_name
while(@@fetch_status=0)
begin
insertintodba_manage_index_defrag_temp
(ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag)
exec(''use[''+@db_name+''];
dbccshowcontig
with
FAST,
TABLERESULTS,
ALL_INDEXES,
NO_INFOMSGS'')
update
dba_manage_index_defrag_temp
set
[db_name]=@db_name
where
[db_name]=''''
fetchnextfromdatabasesinto@db_name
end
closedatabases
deallocatedatabases
insertintodba_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
logicalfrag>5
--Deletethetemporarytable.
DROPTABLEdba_manage_index_defrag_temp
GO
SELECTFROMdba_manage_index_defrag--查看结果
SQLServer2005:
/
描述：只显示逻辑碎片率大于5%的索引信息
限制：针对SqlServer2005以后版本。
功能：对数据库服务器所有非系统数据库进行索引碎片检查
返回碎片率>5%的索引信息
/
createprocp_dba_manage_get_index_defrage
as
setnocounton
ifnotexists(select1fromsys.tableswherename=''dba_manage_index_defrag'')
createtabledba_manage_index_defrag
([db_name]varchar(255)
,[table_name]varchar(255)
,[index_name]varchar(255)
,avg_fragmentation_in_percentreal
,write_timedatetimedefaultgetdate()
)
declare@db_namenvarchar(40)
set@db_name=''''
declarecur_db_namecursorfor
select
name
from
sys.databases
where
database_id>4andstate=0
opencur_db_name
fetchcur_db_nameinto@db_name
while(@@fetch_status=0)
begin
insertintodba_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_percent
FROM
sys.dm_db_index_physical_stats(DB_ID(@db_name),null,NULL,NULL,''Limited'')ASa
JOIN
sys.indexesASbONa.object_id=b.object_idANDa.index_id=b.index_id
join
sys.tablesascona.object_id=c.object_id
where
a.index_id>0
anda.avg_fragmentation_in_percent>5
fetchnextfromcur_db_nameinto@db_name
end
CLOSEcur_db_name
DEALLOCATEcur_db_name
GO
selectfromdba_manage_index_defrag–查看结果
b)根据索引碎片的情况自动选择合适的处理方法
针对SqlServer2000的联机维护：
/Performa''USE''toselectthedatabaseinwhichtorunthescript./
--Declarevariables
SETNOCOUNTON;
DECLARE@tablenamevarchar(128);
DECLARE@execstrvarchar(255);
DECLARE@objectidint;
DECLARE@indexidint;
DECLARE@fragdecimal;
DECLARE@maxfragdecimal;
--Decideonthemaximumfragmentationtoallowfor.
SELECT@maxfrag=30.0;
--Declareacursor.
DECLAREtablesCURSORFOR
SELECTTABLE_SCHEMA+''.''+TABLE_NAME--MSDN上面直接使用TABLE_NAME，如果SCHEMA不是DBO就会出错
FROMINFORMATION_SCHEMA.TABLES
WHERETABLE_TYPE=''BASETABLE'';
--Createthetable.
CREATETABLE#fraglist(
ObjectNamechar(255),
ObjectIdint,
IndexNamechar(255),
IndexIdint,
Lvlint,
CountPagesint,
CountRowsint,
MinRecSizeint,
MaxRecSizeint,
AvgRecSizeint,
ForRecCountint,
Extentsint,
ExtentSwitchesint,
AvgFreeBytesint,
AvgPageDensityint,
ScanDensitydecimal,
BestCountint,
ActualCountint,
LogicalFragdecimal,
ExtentFragdecimal);
--Openthecursor.
OPENtables;
--Loopthroughallthetablesinthedatabase.
FETCHNEXT
FROMtables
INTO@tablename;
WHILE@@FETCH_STATUS=0
BEGIN
--Dotheshowcontigofallindexesofthetable
INSERTINTO#fraglist
EXEC(''DBCCSHOWCONTIG(''''''+@tablename+'''''')
WITHFAST,TABLERESULTS,ALL_INDEXES,NO_INFOMSGS'');
FETCHNEXT
FROMtables
INTO@tablename;
END;
--Closeanddeallocatethecursor.
CLOSEtables;
DEALLOCATEtables;
--Declarethecursorforthelistofindexestobedefragged.
DECLAREindexesCURSORFOR
SELECTObjectName,ObjectId,IndexId,LogicalFrag
FROM#fraglist
WHERELogicalFrag>=@maxfrag
ANDINDEXPROPERTY(ObjectId,IndexName,''IndexDepth'')>0;
--Openthecursor.
OPENindexes;
--Loopthroughtheindexes.
FETCHNEXT
FROMindexes
INTO@tablename,@objectid,@indexid,@frag;
WHILE@@FETCH_STATUS=0
BEGIN
PRINT''ExecutingDBCCINDEXDEFRAG(0,''+RTRIM(@tablename)+'',
''+RTRIM(@indexid)+'')-fragmentationcurrently''
+RTRIM(CONVERTwww.wang027.com(varchar(15),@frag))+''%'';
SELECT@execstr=''DBCCINDEXDEFRAG(0,''+RTRIM(@objectid)+'',
''+RTRIM(@indexid)+'')'';
EXEC(@execstr);
FETCHNEXT
FROMindexes
INTO@tablename,@objectid,@indexid,@frag;
END;
--Closeanddeallocatethecursor.
CLOSEindexes;
DEALLOCATEindexes;
--Deletethetemporarytable.
DROPTABLE#fraglist;
GO
针对SQLServer2000的脱机维护：
sp_msforeachtable@command1="dbccdbreindex(''?'','''',85)"
针对SQLServer2005的通用维护过程
(碎片小于30%的联机组织，碎片>=30%的脱机重建)：
--ensureaUSEstatementhasbeenexecutedfirst.
SETNOCOUNTON;
DECLARE@objectidint;
DECLARE@indexidint;
DECLARE@partitioncountbigint;
DECLARE@schemanamesysname;
DECLARE@objectnamesysname;
DECLARE@indexnamesysname;
DECLARE@partitionnumbigint;
DECLARE@partitionsbigint;
DECLARE@fragfloat;
DECLARE@commandvarchar(8000);
--ensurethetemporarytabledoesnotexist
IFEXISTS(SELECTnameFROMsys.objectsWHEREname=''work_to_do'')
DROPTABLEwork_to_do;
--conditionallyselectfromthefunction,convertingobjectandindexIDstonames.
SELECT
object_idASobjectid,
index_idASindexid,
partition_numberASpartitionnum,
avg_fragmentation_in_percentASfrag
INTOwork_to_do
FROMsys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,''LIMITED'')
WHEREavg_fragmentation_in_percent>10.0ANDindex_id>0;
--Declarethecursorforthelistofpartitionstobeprocessed.
DECLAREpartitionsCURSORFORSELECTFROMwork_to_do;
--Openthecursor.
OPENpartitions;
--Loopthroughthepartitions.
FETCHNEXT
FROMpartitions
INTO@objectid,@indexid,@partitionnum,@frag;
WHILE@@FETCH_STATUS=0
BEGIN;
SELECT@objectname=o.name,@schemaname=s.name
FROMsys.objectsASo
JOINsys.schemasassONs.schema_id=o.schema_id
WHEREo.object_id=@objectid;
SELECT@indexname=name
FROMsys.indexes
WHEREobject_id=@objectidANDindex_id=@indexid;
SELECT@partitioncount=count()
FROMsys.partitions
WHEREobject_id=@objectidANDindex_id=@indexid;
--30isanarbitrarydecisionpointatwhichtoswitchbetweenreorganizingand
rebuilding
IF@frag<30.0and@frag>5
BEGIN;
SELECT@command=''ALTERINDEX''+@indexname+''ON''+@schemaname+''.''+@objectname
+''REORGANIZE'';
IF@partitioncount>1
SELECT@command=@command+''PARTITION=''+CONVERT(CHAR,@partitionnum);
EXEC(@command);
END;
IF@frag>=30.0
BEGIN;
SELECT@command=''ALTERINDEX''+@indexname+''ON''+@schemaname+''.''+@objectname+
''REBUILD'';
IF@partitioncount>1
SELECT@command=@command+''PARTITION=''+CONVERT(CHAR,@partitionnum);
EXEC(@command);
END;
PRINT''Executed''+@command;
FETCHNEXTFROMpartitionsINTO@objectid,@indexid,@partitionnum,@frag;
END;
--Closeanddeallocatethecursor.
CLOSEpartitions;
DEALLOCATEpartitions;
--dropthetemporarytable
IFEXISTS(SELECTnameFROMsys.objectsWHEREname=''work_to_do'')
DROPTABLEwork_to_do;
GO
总结
索引的维护是有参考依据的，应该根据具体的碎片情况以及是否需要联机操作等需求，采用合理的维护方法。自动化的索引维护策略是可行的。
|
|
