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

BANDAI SPIRITS(バンダイ スピリッツ) HG 機動戦士Gundam GQuuuuuuX 軍警ザク 1/144スケール 色分け済みプラモデル

(25)

￥3,780 (2025年4月30日 00:08 GMT +09:00 時点 - 詳細はこちら価格および発送可能時期は表示された日付/時刻の時点のものであり、変更される場合があります。本商品の購入においては、購入の時点で当該の Amazon サイトに表示されている価格および発送可能時期の情報が適用されます。)

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

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

マイクロソフトのサイトを参考に、システム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