SQL Server索引维护指导
作者：王红波
摘要
本文以笔者在实际工作中对SQL Server数据库种索引维护的思路和方法为导向，为大家介绍SQL Server索引维护相关的知识和方法。
导言
索引在数据库相关工作者的日常工作中占据了很重要的位置，索引需要牵涉到索引创建、优化和维护多方面的工作，本文以实例结合相关原理来介绍索引维护相关的知识。文中的相关代码，也可以满足多数情况下索引的维护需求。
实现步骤
1
. 以什么标准判断索引是否需要维护？
2
. 索引维护的方法有哪些？
3
. 能否方便地整理出比较通用的维护过程，实现自动化维护？
一、 以什么标准判断索引是否需要维护？
由于本文集中讨论索引维护相关，所以我们暂且抛开创建的不合理的那些索引，仅从维护的角度来讨论。从索引维护的角度来讲，最主要的参考标准就是索引碎片的大小。通常情况下，索引碎片在10
%
以内，是可以接受的。下面介绍获取索引碎片的方法：
SQL Server2000:
DBCC
SHOWCONTIG
SQL Server2005: sys.dm_db_index_physical_stats
实例(取db_test数据库所有索引碎片相关信息)：
SQL Server2000:
USE
[
db_test
]
;
GO
DBCC
SHOWCONTIG
WITH
TABLERESULTS, ALL_INDEXES
GO
SQL Server
2005
:
DECLARE
@db_name
VARCHAR
(
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
'
)
AS
a
JOIN
sys.indexes
AS
b
ON
a.
object_id
=
b.
object_id
AND
a.index_id
=
b.index_id
JOIN
sys.tables
AS
c
ON
a.
object_id
=
c.
object_id
WHERE
a.index_id
>
0
AND
a.avg_fragmentation_in_percent
>
5
-
–碎片程度大于5
二、 索引维护的方法有哪些？
注：维护方式的选择，一方面要考虑是否是联机维护，另一方面就是速度上的考虑。一般碎片
<=
30
%
时，使用重新组织的方法速度比索引重建快；碎片
>
30
%
时，索引重建的速度比重新组织要快。
1
. 联机维护
SQL Server2000:
DBCC
INDEXDEFRAG 重新组织索引，占用资源少，锁定资源周期短，可联机进行。
SQL Server
2005
:
1
. 联机重新组织：
ALTER
INDEX
[
index_name
]
ON
[
table_name
]
REORGANIZE;ALTER
INDEX
[
index_name
]
ON
[
table_name
]
REORGANIZE;ALTER
INDEX
[
index_name
]
ON
[
table_name
]
REORGANIZE;
2
. 联机重建：
ALTER
INDEX
[
index_name
]
ON
[
table_name
]
REBUILD
WITH
(
FILLFACTOR
=
85
, SORT_IN_TEMPDB
=
OFF
,
STATISTICS_NORECOMPUTE
=
ON
,ONLINE
=
ON
);
2
. 脱机维护
SQL Server2000：
DBCC
DBREINDEX
SQL Server
2005
：
ALTER
INDEX
[
indexname
]
ON
[
table_name
]
REBUILD;
CREATE
INDEX
WITH
DROP_EXISTING
3
. 能否方便地整理出比较通用的维护过程，实现自动化维护？
a) 获取及查看所有索引的碎片情况
SQL Server2000:
/**/
/*
描述：获取服务器上所有数据库的逻辑碎片率>5的索引信息
适用：SqlServer2000以后版本
*/
SET
NOCOUNT
ON
DECLARE
@db_name
varchar
(
128
)
DECLARE
@tablename
varchar
(
128
)
DECLARE
@table_schema
varchar
(
128
)
DECLARE
@execstr
varchar
(
255
)
DECLARE
@objectid
int
DECLARE
@indexid
int
DECLARE
@frag
decimal
DECLARE
@maxfrag
decimal
DECLARE
@sql
varchar
(
8000
)
--
Decide on the maximum 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
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
,
MaxRecSize
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
FOR
select
name
from
master.dbo.sysdatabases
where
dbid
>
4
--
Open the cursor.
open
databases
fetch
databases
into
@db_name
while
(
@@fetch_status
=
0
)
begin
insert
into
dba_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
+
'
];
dbcc showcontig
with
FAST,
TABLERESULTS,
ALL_INDEXES,
NO_INFOMSGS
'
)
update
dba_manage_index_defrag_temp
set
[
db_name
]
=
@db_name
where
[
db_name
]
=
''
fetch
next
from
databases
into
@db_name
end
close
databases
deallocate
databases
insert
into
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
logicalfrag
>
5
--
Delete the temporary table.
DROP
TABLE
dba_manage_index_defrag_temp
GO
SELECT
*
FROM
dba_manage_index_defrag
--
查看结果
SQL Server2005:
/**/
/*
描述：只显示逻辑碎片率大于5%的索引信息
限制：针对SqlServer2005以后版本。
功能：对数据库服务器所有非系统数据库进行索引碎片检查
返回碎片率>5%的索引信息
*/
create
proc
p_dba_manage_get_index_defrage
as
set
nocount
on
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
default
getdate
()
)
declare
@db_name
nvarchar
(
40
)
set
@db_name
=
''
declare
cur_db_name
cursor
for
select
name
from
sys.databases
where
database_id
>
4
and
state
=
0
open
cur_db_name
fetch
cur_db_name
into
@db_name
while
(
@@fetch_status
=
0
)
begin
insert
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_percent
FROM
sys.dm_db_index_physical_stats (
DB_ID
(
@db_name
),
null
,
NULL
,
NULL
,
'
Limited
'
)
AS
a
JOIN
sys.indexes
AS
b
ON
a.
object_id
=
b.
object_id
AND
a.index_id
=
b.index_id
join
sys.tables
as
c
on
a.
object_id
=
c.
object_id
where
a.index_id
>
0
and
a.avg_fragmentation_in_percent
>
5
fetch
next
from
cur_db_name
into
@db_name
end
CLOSE
cur_db_name
DEALLOCATE
cur_db_name
GO
select
*
from
dba_manage_index_defrag –查看结果
b) 根据索引碎片的情况自动选择合适的处理方法
针对Sql Server2000的联机维护：
/**/
/*Perform a 'USE <database name>' to select the database in which to run the script.*/
--
Declare variables
SET
NOCOUNT
ON
;
DECLARE
@tablename
varchar
(
128
);
DECLARE
@execstr
varchar
(
255
);
DECLARE
@objectid
int
;
DECLARE
@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
FOR
SELECT
TABLE_SCHEMA
+
'
.
'
+
TABLE_NAME
--
MSDN上面直接使用TABLE_NAME，如果SCHEMA不是DBO就会出错
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE
=
'
BASE 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
decimal
,
BestCount
int
,
ActualCount
int
,
LogicalFrag
decimal
,
ExtentFrag
decimal
);
--
Open the cursor.
OPEN
tables;
--
Loop through all the tables in the database.
FETCH
NEXT
FROM
tables
INTO
@tablename
;
WHILE
@@FETCH_STATUS
=
0
BEGIN
--
Do the showcontig of all indexes of the table
INSERT
INTO
#fraglist
EXEC
(
'
DBCC SHOWCONTIG (
'''
+
@tablename
+
'''
)
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
'
);
FETCH
NEXT
FROM
tables
INTO
@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
FOR
SELECT
ObjectName, ObjectId, IndexId, LogicalFrag
FROM
#fraglist
WHERE
LogicalFrag
>=
@maxfrag
AND
INDEXPROPERTY
(ObjectId, IndexName,
'
IndexDepth
'
)
>
0
;
--
Open the cursor.
OPEN
indexes;
--
Loop through the indexes.
FETCH
NEXT
FROM
indexes
INTO
@tablename
,
@objectid
,
@indexid
,
@frag
;
WHILE
@@FETCH_STATUS
=
0
BEGIN
PRINT
'
Executing DBCC INDEXDEFRAG (0,
'
+
RTRIM
(
@tablename
)
+
'
,
'
+
RTRIM
(
@indexid
)
+
'
) - fragmentation currently
'
+
RTRIM
(
CONVERT
(
varchar
(
15
),
@frag
))
+
'
%
'
;
SELECT
@execstr
=
'
DBCC INDEXDEFRAG (0,
'
+
RTRIM
(
@objectid
)
+
'
,
'
+
RTRIM
(
@indexid
)
+
'
)
'
;
EXEC
(
@execstr
);
FETCH
NEXT
FROM
indexes
INTO
@tablename
,
@objectid
,
@indexid
,
@frag
;
END
;
--
Close and deallocate the cursor.
CLOSE
indexes;
DEALLOCATE
indexes;
--
Delete the temporary table.
DROP
TABLE
#fraglist;
GO
针对SQL Server2000的脱机维护：
sp_msforeachtable
@command1
=
"
dbcc
dbreindex(
'
?
'
,
''
,
85
)"
针对SQL Server2005的通用维护过程
(碎片小于30
%
的联机组织，碎片
>=
30
%
的脱机重建)：
--
ensure a USE <databasename> statement has been executed first.
SET
NOCOUNT
ON
;
DECLARE
@objectid
int
;
DECLARE
@indexid
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 exist
IF
EXISTS
(
SELECT
name
FROM
sys.objects
WHERE
name
=
'
work_to_do
'
)
DROP
TABLE
work_to_do;
--
conditionally select from the function, converting object and index IDs to names.
SELECT
object_id
AS
objectid,
index_id
AS
indexid,
partition_number
AS
partitionnum,
avg_fragmentation_in_percent
AS
frag
INTO
work_to_do
FROM
sys.dm_db_index_physical_stats (
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
NEXT
FROM
partitions
INTO
@objectid
,
@indexid
,
@partitionnum
,
@frag
;
WHILE
@@FETCH_STATUS
=
0
BEGIN
;
SELECT
@objectname
=
o.name,
@schemaname
=
s.name
FROM
sys.objects
AS
o
JOIN
sys.schemas
as
s
ON
s.schema_id
=
o.schema_id
WHERE
o.
object_id
=
@objectid
;
SELECT
@indexname
=
name
FROM
sys.indexes
WHERE
object_id
=
@objectid
AND
index_id
=
@indexid
;
SELECT
@partitioncount
=
count
(
*
)
FROM
sys.partitions
WHERE
object_id
=
@objectid
AND
index_id
=
@indexid
;
--
30 is an arbitrary decision point at which to switch between reorganizing and rebuilding
IF
@frag
<
30.0
and
@frag
>
5
BEGIN
;
SELECT
@command
=
'
ALTER INDEX
'
+
@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
=
'
ALTER INDEX
'
+
@indexname
+
'
ON
'
+
@schemaname
+
'
.
'
+
@objectname
+
'
REBUILD
'
;
IF
@partitioncount
>
1
SELECT
@command
=
@command
+
'
PARTITION=
'
+
CONVERT
(
CHAR
,
@partitionnum
);
EXEC
(
@command
);
END
;
PRINT
'
Executed
'
+
@command
;
FETCH
NEXT
FROM
partitions
INTO
@objectid
,
@indexid
,
@partitionnum
,
@frag
;
END
;
--
Close and deallocate the cursor.
CLOSE
partitions;
DEALLOCATE
partitions;
--
drop the temporary table
IF
EXISTS
(
SELECT
name
FROM
sys.objects
WHERE
name
=
'
work_to_do
'
)
DROP
TABLE
work_to_do;
GO
总结
索引的维护是有参考依据的，应该根据具体的碎片情况以及是否需要联机操作等需求，采用合理的维护方法。自动化的索引维护策略是可行的。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
