インデックスデフラグと統計情報更新

日々の運用で断片化したテーブルのインデックスデフラグ(インデックスの再構成)と統計情報の更新をします。

インデックスのデフラグ(再構成)とリビルド(再構築)がありますが、下のコマンドはデフラグを行います。また、統計情報の更新をします。

マイクロソフトのサイトを参考に、システムDB以外、読み取り専用になっていない全DBを対象にテーブル所有者にも配慮したものにしました。

DECLARE @comand VARCHAR (8000)

----------------------------------
--@comand　ここから
SELECT @comand='
-- Declare variables
SET NOCOUNT ON
DECLARE @dbename VARCHAR (255)
DECLARE @tableownername VARCHAR (128)
DECLARE @tableownername2 VARCHAR (128)
DECLARE @tablename VARCHAR (128)
DECLARE @tablename2 VARCHAR (128)
DECLARE @inflg   INT
DECLARE @execstr   VARCHAR (255)
DECLARE @objectid  INT
DECLARE @indexid   INT
DECLARE @frag      DECIMAL
DECLARE @maxfrag   DECIMAL
DECLARE @IndexName VARCHAR (255)

-- Decide on the maximum fragmentation to allow
-- どれくらい断片化が進んだテーブルを対象にするか 10～30%
SELECT @maxfrag = 10.0

-- 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)

-- Do the showcontig of all indexes of the table
EXEC SP_MSForEachTable ''
   INSERT INTO #fraglist 
   EXEC (''''DBCC SHOWCONTIG (''''''''?'''''''') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS'''');
''

-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
   SELECT DISTINCT TABLE_SCHEMA, ObjectName, ObjectId, LogicalFrag, IndexName
   FROM #fraglist A
   inner join INFORMATION_SCHEMA.TABLES B
   ON A.ObjectName=B.TABLE_NAME 
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, ''IndexDepth'') > 0
      AND ( IndexName IS NOT NULL OR RTRIM(IndexName) <> '''' )

-- Open the cursor
OPEN indexes

SELECT @inflg  = 0
-- loop through the indexes
FETCH NEXT
   FROM indexes
   INTO @tableownername, @tablename, @objectid, @frag, @IndexName

WHILE @@FETCH_STATUS = 0
BEGIN

   SELECT @inflg  = 1
   SELECT @tablename2 = @tablename
   SELECT @tableownername2 = @tableownername

   PRINT ''Executing DBCC INDEXDEFRAG (0, ''''['' + RTRIM(@tableownername) + ''].['' + RTRIM(@tablename) + '']'''', ['' + RTRIM(@IndexName) + '']) - fragmentation currently ''
       + RTRIM(CONVERT(varchar(15),@frag)) + ''%''
   SELECT @execstr = ''DBCC INDEXDEFRAG (0, ''''['' + RTRIM(@tableownername) + ''].['' + RTRIM(@tablename) + '']'''', ['' + RTRIM(@IndexName) + ''])''
   EXEC (@execstr)


   FETCH NEXT
      FROM indexes
      INTO @tableownername, @tablename, @objectid, @frag, @IndexName

   --統計情報更新
   IF @tablename2 <> @tablename
   BEGIN
      PRINT ''UPDATE STATISTICS ['' + RTRIM(@tableownername2) + ''].['' + RTRIM(@tablename2) + ''] WITH FULLSCAN''
      SELECT @execstr = ''UPDATE STATISTICS ['' + RTRIM(@tableownername2) + ''].['' + RTRIM(@tablename2) + ''] WITH FULLSCAN''
      EXEC (@execstr)
   END

END
   IF @inflg  = 1
   BEGIN
      --統計情報更新
      PRINT ''UPDATE STATISTICS ['' + RTRIM(@tableownername2) + ''].['' + RTRIM(@tablename2) + ''] WITH FULLSCAN''
      SELECT @execstr = ''UPDATE STATISTICS ['' + RTRIM(@tableownername2) + ''].['' + RTRIM(@tablename2) + ''] WITH FULLSCAN''
      EXEC (@execstr)
   END

-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes

-- Delete the temporary table
DROP TABLE #fraglist
'
--@comand　ここまで
----------------------------------


DECLARE @dbename VARCHAR (255)

-- Create the table
CREATE TABLE #dbname (
   dbName VARCHAR (255))

--システム以外、読み取り専用になっていないもの
INSERT INTO #dbname
SELECT [name]
FROM sys.databases
WHERE  is_read_only =0 and database_id > 4


-- Declare cursor for list of indexes to be defragged
DECLARE dbname CURSOR FOR
   SELECT DBName FROM #DBName

-- Open the cursor
OPEN dbname

-- loop through the indexes
FETCH NEXT
   FROM dbname
   INTO @dbename

WHILE @@FETCH_STATUS = 0
BEGIN
print 'DB = ' + @dbename

exec ('use [' + @dbename + ']'
+ @comand +';'
);
-- loop through the indexes
FETCH NEXT
   FROM dbname
   INTO @dbename

END

CLOSE dbname
DEALLOCATE dbname

-- Delete the temporary table
DROP TABLE #dbname

参考サイト:https://msdn.microsoft.com/ja-jp/library/ms177571%28v=sql.110%29.aspx

Tweet