在數(shù)據(jù)的使用過(guò)程中，由于索引page碎片過(guò)多，帶來(lái)一些不利的性能問(wèn)題，我們有時(shí)候需要對(duì)數(shù)據(jù)庫(kù)中的索引進(jìn)行重組或者重建工作。通常這個(gè)閾值為30%，大于30%我們建議進(jìn)行索引重建，小于則進(jìn)行重組操作。以下腳本可以針對(duì)固定的數(shù)據(jù)庫(kù)進(jìn)行自動(dòng)的索引重建和重組工作：
腳本來(lái)源于網(wǎng)絡(luò)，如侵犯版權(quán)請(qǐng)聯(lián)系刪除：
set nocount on --使用游標(biāo)重新組織指定庫(kù)中的索引,消除索引碎片 --R_T層游標(biāo)取出當(dāng)前數(shù)據(jù)庫(kù)所有表 declare R_T cursor for select name from 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 for select t.name,i.name,s.avg_fragmentation_in_percent from 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),@avg int,@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 set nocount off
上述代碼可以針對(duì)指定的數(shù)據(jù)庫(kù)，進(jìn)行索引的重組重建的工作，我們也可以針對(duì)數(shù)據(jù)庫(kù)中的所有數(shù)據(jù)庫(kù)進(jìn)行相應(yīng)的工作，以下代碼來(lái)源于 ： https://www.mssqltips.com/sqlservertip/1367/sql-server-script-to-rebuild-all-indexes-for-all-tables-and-all-databases/
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 + '']'' as tableName 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 -- SQL 2005 or higher command SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' EXEC (@cmd) END ELSE BEGIN -- SQL 2000 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
聯(lián)系客服
