收藏
下载资源下载资源 加入VIP,免费下载

SQL Server索引维护指导.docx

上传人：b****3 文档编号：5427056 上传时间：2022-12-16 格式：DOCX 页数：14 大小：18.40KB
下载 相关 举报
SQL Server索引维护指导.docx_第1页
第1页 / 共14页
SQL Server索引维护指导.docx_第2页
第2页 / 共14页
SQL Server索引维护指导.docx_第3页
第3页 / 共14页
SQL Server索引维护指导.docx_第4页
第4页 / 共14页
SQL Server索引维护指导.docx_第5页
第5页 / 共14页
点击查看更多>>
下载资源
资源描述

SQL Server索引维护指导.docx

《SQL Server索引维护指导.docx》由会员分享，可在线阅读，更多相关《SQL Server索引维护指导.docx（14页珍藏版）》请在冰豆网上搜索。

SQL Server索引维护指导.docx

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

展开阅读全文
相关资源
猜你喜欢
相关搜索

当前位置：首页 > 医药卫生 > 基础医学

copyright@ 2008-2022 冰豆网网站版权所有

经营许可证编号:鄂ICP备2022015515号-1