在數(shù)據(jù)的使用過程中，由于索引page碎片過多，帶來一些不利的性能問題，我們有時(shí)候需要對(duì)數(shù)據(jù)庫中的索引進(jìn)行重組或者重建工作。通常這個(gè)閾值為30%，大于30%我們建議進(jìn)行索引重建，小于則進(jìn)行重組操作。以下腳本可以針對(duì)固定的數(shù)據(jù)庫進(jìn)行自動(dòng)的索引重建和重組工作：
set nocount on? --使用游標(biāo)重新組織指定庫中的索引,消除索引碎片? --R_T層游標(biāo)取出當(dāng)前數(shù)據(jù)庫所有表?
declare R_T cursor?
? ? forselectnamefrom sys.tables?
declare @T varchar(50)?
open r_t?
fetch next from r_t into @t? while@@fetch_status=0?
begin?
--R_index游標(biāo)判斷指定表索引碎片情況并優(yōu)化?
declare R_Index cursor?
forselectt.name,i.name,s.avg_fragmentation_in_percentfrom sys.tables t?
? join sys.indexes i on i.object_id=t.object_id?
? join sys.dm_db_index_physical_stats(db_id(),object_id(@T),null,null,'limited') s?
? ? on s.object_id=i.object_id and s.index_id=i.index_id?
declare @TName varchar(50),@IName varchar(50),@avgint,@str varchar(500)?
open r_index?
fetch next from r_index into @TName,@Iname,@avg?
while@@fetch_status=0?
begin?
? if@avg>=30--如果碎片大于30,重建索引?
? begin?
? ? set@str='alter index '+rtrim(@Iname)+' on dbo.'+quotename(rtrim(@tname))+' rebuild'?
? end?
? else--如果碎片小于30,重新組織索引?
? begin?
? ? set@STR='alter index '+rtrim(@Iname)+' on dbo.'+quotename(rtrim(@tname))+' reorganize'?
? end?
? print @str?
? exec (@str)? --執(zhí)行?
? fetch next from r_index into @TName,@Iname,@avg?
end?
--結(jié)束r_index游標(biāo)?
close r_index?
deallocate r_index?
fetch next from r_t into @t?
end?
--結(jié)束R_T游標(biāo)?
close r_t?
deallocate r_t?
setnocount off
上述代碼可以針對(duì)指定的數(shù)據(jù)庫，進(jìn)行索引的重組重建的工作，我們也可以針對(duì)數(shù)據(jù)庫中的所有數(shù)據(jù)庫進(jìn)行相應(yīng)的工作，以下代碼來源于?
DECLARE @Database VARCHAR(255)?
DECLARE @Table VARCHAR(255)?
DECLARE @cmd NVARCHAR(500)?
DECLARE @fillfactor INT
SET @fillfactor =90
DECLARE DatabaseCursor CURSOR FOR?
SELECT name FROM master.dbo.sysdatabases?
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')?
ORDER BY 1?
OPEN DatabaseCursor?
FETCH NEXT FROM DatabaseCursor INTO @Database?
WHILE @@FETCH_STATUS =0?
BEGIN?
? SET @cmd ='DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + table_name +'']''astableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES
? WHERE table_type =''BASE TABLE'''-- create table cursor?
? EXEC (@cmd)?
? OPEN TableCursor?
? FETCH NEXT FROM TableCursor INTO @Table?
? WHILE @@FETCH_STATUS =0?
? BEGIN?
? ? ? IF (@@MICROSOFTVERSION / POWER(2,24) >=9)
? ? ? BEGIN
? ? ? ? ? -- SQL2005 or higher command
? ? ? ? ? SET @cmd ='ALTER INDEX ALL ON '+ @Table +' REBUILD WITH (FILLFACTOR = '+ CONVERT(VARCHAR(3),@fillfactor) +')'
? ? ? ? ? EXEC (@cmd)
? ? ? END
? ? ? ELSE
? ? ? BEGIN
? ? ? ? ? -- SQL2000 command
? ? ? ? ? DBCC DBREINDEX(@Table,'',@fillfactor)?
? ? ? END
? ? ? FETCH NEXT FROM TableCursor INTO @Table?
? END?
? CLOSE TableCursor?
? DEALLOCATE TableCursor?
? FETCH NEXT FROM DatabaseCursor INTO @Database?
END?
CLOSE DatabaseCursor?
DEALLOCATE DatabaseCursor
大家可以根據(jù)代碼靈活的選擇重建和重組索引，并設(shè)置不同的閾值。微軟件推薦索引的填充因子為90.
