SQL Server索引维护指导.docx
《SQL Server索引维护指导.docx》由会员分享,可在线阅读,更多相关《SQL Server索引维护指导.docx(14页珍藏版)》请在冰豆网上搜索。
![SQL Server索引维护指导.docx](https://file1.bdocx.com/fileroot1/2022-12/16/98eb8bad-0c31-43f3-bc05-4fc00db8bd81/98eb8bad-0c31-43f3-bc05-4fc00db8bd811.gif)
SQLServer索引维护指导
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
SELECT*FROMdba_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
select*fromdba_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(CONVERT(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.
DECLAREpartitionsCURSORFORSELECT*FROMwork_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'+@schemana