主頁 > 知識庫 > SQL Server 索引維護sql語句

SQL Server 索引維護sql語句

熱門標簽:分享百度地圖標注多個位置 湖南電腦外呼系統(tǒng)平臺 外呼系統(tǒng)改進 廣東防封卡外呼系統(tǒng)原理是什么 地圖標注牌 知名電銷機器人價格 長沙智能外呼系統(tǒng) 菏澤語音電銷機器人加盟公司 電銷機器人公司 需要哪些牌照
使用以下腳本查看數(shù)據(jù)庫索引碎片的大小情況:
復制代碼 代碼如下:

DBCC SHOWCONTIG WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS

以下使用腳本來處理維護作業(yè):
復制代碼 代碼如下:

/*Perform a 'USE database name>' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON;
DECLARE @tablename varchar(255);
DECLARE @execstr varchar(400);
DECLARE @objectid int;
Declare @IndexName varchar(500);
DECLARE @indexid int;
DECLARE @frag decimal;
DECLARE @maxfrag decimal;
DECLARE @TmpName varchar(500);

-- Declare @TmpName =''
set @TmpName = ''

-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0;

-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
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,IndexName,IndexId, LogicalFrag
FROM #fraglist
WHERE INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;

-- Open the cursor.
OPEN indexes;

-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @IndexName,@indexid, @frag;


WHILE @@FETCH_STATUS = 0
BEGIN;
if @frag @maxfrag
Begin
SELECT @execstr = 'ALTER INDEX [' + RTRIM(@IndexName) + '] ON [' + RTRIM(@tablename) + '] REORGANIZE WITH ( LOB_COMPACTION = ON ) '
print @maxfrag + ' ' + @execstr
End
else
Begin
SELECT @execstr = 'ALTER INDEX [' + RTRIM(@IndexName) + '] ON [' + RTRIM(@tablename) + '] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )'
print @maxfrag + ' ' + @execstr
End

EXEC (@execstr);

--更新統(tǒng)計信息
IF @TmpName>@tablename
BEGIN
SET @tmpName=@tableName
PRINT 'UPDATE STATISTICS '+@TableName + ' WITH FULLSCAN '
EXEC ('UPDATE STATISTICS '+@TableName + ' WITH FULLSCAN ')
END


FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @IndexName,@indexid, @frag;
END;

-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;

-- Delete the temporary table.
DROP TABLE #fraglist;
GO

標簽:天水 商洛 美容院 呼和浩特 珠海 福建 西寧 泉州

巨人網絡通訊聲明:本文標題《SQL Server 索引維護sql語句》,本文關鍵詞  SQL,Server,索引,維護,sql,語句,;如發(fā)現(xiàn)本文內容存在版權問題,煩請?zhí)峁┫嚓P信息告之我們,我們將及時溝通與處理。本站內容系統(tǒng)采集于網絡,涉及言論、版權與本站無關。
  • 相關文章
  • 下面列出與本文章《SQL Server 索引維護sql語句》相關的同類信息!
  • 本頁收集關于SQL Server 索引維護sql語句的相關信息資訊供網民參考!
  • 推薦文章