二、 索引维护的方法有哪些?

　　注：维护方式的选择，一方面要考虑是否是联机维护，另一方面就是速度上的考虑。一般碎片<=30%时，使用重新组织的方法速度比索引重建快;碎片>30%时，索引重建的速度比重新组织要快。

　　1. 联机维护

　　SQL Server2000:

　　DBCC INDEXDEFRAG 重新组织索引，占用资源少，锁定资源周期短，可联机进行。

　　SQL Server 2005:

　　1. 联机重新组织：

　　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

---

亿贸网全球商人社区，全球商人聚集地！

　SQL Server 2005:

/*
描述：只显示逻辑碎片率大于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 –查看结果

---

亿贸网全球商人社区，全球商人聚集地！

　针对SQL Server 2005的通用维护过程

　　(碎片小于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



　　总结

　　索引的维护是有参考依据的，应该根据具体的碎片情况以及是否需要联机操作等需求，采用合理的维护方法。自动化的索引维护策略是可行的。

---

亿贸网全球商人社区，全球商人聚集地！