sqlserver 全部索引重建

renjixinchina發表於2012-08-31
SQLServer索引
declare  @maxfrag DECIMAL

select @maxfrag = 90

--宣告變數
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @objectowner VARCHAR(255)
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @indexname CHAR(255)
DECLARE @dbname sysname
DECLARE @tableid INT
DECLARE @tableidchar VARCHAR(255)

--檢查是否在使用者資料庫裡執行
SELECT @dbname = db_name()
IF @dbname IN ('master', 'msdb', 'model', 'tempdb')
BEGIN
PRINT 'This procedure should not be run in system databases.'
RETURN
END

--第1階段:檢測碎片
--宣告遊標
DECLARE tables CURSOR FOR
SELECT convert(varchar,so.id)
FROM sysobjects so
JOIN sysindexes si
ON so.id = si.id
WHERE so.type ='U'
AND si.indid < 2
AND si.rows > 0

-- 建立一個臨時表來儲存碎片資訊
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 tables

-- 對資料庫的所有表迴圈執行dbcc showcontig命令
FETCH NEXT
FROM tables
INTO @tableidchar

WHILE @@FETCH_STATUS = 0
BEGIN
--對錶的所有索引進行統計
INSERT INTO #fraglist 
EXEC ('DBCC SHOWCONTIG (' + @tableidchar + ') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tableidchar
END

-- 關閉釋放遊標
CLOSE tables
DEALLOCATE tables

-- 為了檢查，報告統計結果
SELECT * FROM #fraglist

--第2階段: (整理碎片) 為每一個要整理碎片的索引宣告遊標
DECLARE indexes CURSOR FOR
SELECT ObjectName, bjectOwner = user_name(so.uid), ObjectId, IndexName, ScanDensity
FROM #fraglist f
JOIN sysobjects so ON f.ObjectId=so.id
WHERE ScanDensity <= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- 輸出開始時間
SELECT 'Started defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())

--開啟遊標
OPEN indexes

--迴圈所有的索引
FETCH NEXT
FROM indexes
INTO @tablename, @objectowner, @objectid, @indexname, @frag

WHILE @@FETCH_STATUS = 0
BEGIN
SET QUOTED_IDENTIFIER ON

SELECT @execstr = 'DBCC DBREINDEX (' + ''''  + RTRIM(@objectowner) + '.' + RTRIM(@tablename) + ''''+ ', ' + RTRIM(@indexname) + ') WITH NO_INFOMSGS'
SELECT 'Now executing: '
SELECT(@execstr)
EXEC (@execstr)

SET QUOTED_IDENTIFIER OFF

FETCH NEXT
FROM indexes
INTO @tablename, @objectowner, @objectid, @indexname, @frag
END

-- 關閉釋放遊標
CLOSE indexes
DEALLOCATE indexes

-- 報告結束時間
SELECT 'Finished defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())

-- 刪除臨時表
DROP TABLE #fraglist

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15747463/viewspace-742386/，如需轉載，請註明出處，否則將追究法律責任。

相關文章