WindowsServer2012R2のWSUSのDBインデックス再構成

(1)SQL Server Management Studio Express をダウンロードし、WSUS サーバーにインストール。

Microsoft® SQL Server 2014 Express
https://www.microsoft.com/ja-jp/download/details.aspx?id=42299
※ ダウンロード時に MgmtStudio 64BIT\SQLManagementStudio_x64_JPN.exe を選択。

(2)SQL Server Management Studio Express を起動、下記のパラメータを入力し、データベースへ [接続] 。

サーバーの種類 : データベースエンジン
サーバー名 : \\ .\pipe\Microsoft##WID\tsql\query
認証 : Windows 認証

(3)左ペインのデータベースのツリーから [SUSDB] を右クリックして[新しいクエリ] を選択。

(4)右ペインのクエリ画面内に、下記の WSUS データベースメンテナンス用スクリプトをコピーペースト＋実行。

/****************************************************************************** 
This sample T-SQL script performs basic maintenance tasks on SUSDB 
1. Identifies indexes that are fragmented and defragments them. For certain 
   tables, a fill-factor is set in order to improve insert performance. 
   Based on MSDN sample at http://msdn2.microsoft.com/en-us/library/ms188917.aspx 
   and tailored for SUSDB requirements 
2. Updates potentially out-of-date table statistics. 
******************************************************************************/ 
 
USE SUSDB; 
GO 
SET NOCOUNT ON; 
 
-- Rebuild or reorganize indexes based on their fragmentation levels 
DECLARE @work_to_do TABLE ( 
    objectid int 
    , indexid int 
    , pagedensity float 
    , fragmentation float 
    , numrows int 
) 
 
DECLARE @objectid int; 
DECLARE @indexid int; 
DECLARE @schemaname nvarchar(130);  
DECLARE @objectname nvarchar(130);  
DECLARE @indexname nvarchar(130);  
DECLARE @numrows int 
DECLARE @density float; 
DECLARE @fragmentation float; 
DECLARE @command nvarchar(4000);  
DECLARE @fillfactorset bit 
DECLARE @numpages int 
 
-- Select indexes that need to be defragmented based on the following 
-- * Page density is low 
-- * External fragmentation is high in relation to index size 
PRINT 'Estimating fragmentation: Begin. ' + convert(nvarchar, getdate(), 121)  
INSERT @work_to_do 
SELECT 
    f.object_id 
    , index_id 
    , avg_page_space_used_in_percent 
    , avg_fragmentation_in_percent 
    , record_count 
FROM  
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'SAMPLED') AS f 
WHERE 
    (f.avg_page_space_used_in_percent < 85.0 and f.avg_page_space_used_in_percent/100.0 * page_count < page_count - 1) 
    or (f.page_count > 50 and f.avg_fragmentation_in_percent > 15.0) 
    or (f.page_count > 10 and f.avg_fragmentation_in_percent > 80.0) 
 
PRINT 'Number of indexes to rebuild: ' + cast(@@ROWCOUNT as nvarchar(20)) 
 
PRINT 'Estimating fragmentation: End. ' + convert(nvarchar, getdate(), 121) 
 
SELECT @numpages = sum(ps.used_page_count) 
FROM 
    @work_to_do AS fi 
    INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id 
    INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id 
 
-- Declare the cursor for the list of indexes to be processed. 
DECLARE curIndexes CURSOR FOR SELECT * FROM @work_to_do 
 
-- Open the cursor. 
OPEN curIndexes 
 
-- Loop through the indexes 
WHILE (1=1) 
BEGIN 
    FETCH NEXT FROM curIndexes 
    INTO @objectid, @indexid, @density, @fragmentation, @numrows; 
    IF @@FETCH_STATUS < 0 BREAK; 
 
    SELECT  
        @objectname = QUOTENAME(o.name) 
        , @schemaname = QUOTENAME(s.name) 
    FROM  
        sys.objects AS o 
        INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id 
    WHERE  
        o.object_id = @objectid; 
 
    SELECT  
        @indexname = QUOTENAME(name) 
        , @fillfactorset = CASE fill_factor WHEN 0 THEN 0 ELSE 1 END 
    FROM  
        sys.indexes 
    WHERE 
        object_id = @objectid AND index_id = @indexid; 
 
    IF ((@density BETWEEN 75.0 AND 85.0) AND @fillfactorset = 1) OR (@fragmentation < 30.0) 
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'; 
    ELSE IF @numrows >= 5000 AND @fillfactorset = 0 
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90)'; 
    ELSE 
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'; 
    PRINT convert(nvarchar, getdate(), 121) + N' Executing: ' + @command; 
    EXEC (@command); 
    PRINT convert(nvarchar, getdate(), 121) + N' Done.'; 
END 
 
-- Close and deallocate the cursor. 
CLOSE curIndexes; 
DEALLOCATE curIndexes; 
 
 
IF EXISTS (SELECT * FROM @work_to_do) 
BEGIN 
    PRINT 'Estimated number of pages in fragmented indexes: ' + cast(@numpages as nvarchar(20)) 
    SELECT @numpages = @numpages - sum(ps.used_page_count) 
    FROM 
        @work_to_do AS fi 
        INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id 
        INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id 
 
    PRINT 'Estimated number of pages freed: ' + cast(@numpages as nvarchar(20)) 
END 
GO 
 
 
--Update all statistics 
PRINT 'Updating all statistics.' + convert(nvarchar, getdate(), 121)  
EXEC sp_updatestats 
PRINT 'Done updating statistics.' + convert(nvarchar, getdate(), 121)  
GO

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

/******************************************************************************

This sample T-SQL script performs basic maintenance tasks on SUSDB

1. Identifies indexes that are fragmented and defragments them. For certain

tables, a fill-factor is set in order to improve insert performance.

Based on MSDN sample at http://msdn2.microsoft.com/en-us/library/ms188917.aspx

and tailored for SUSDB requirements

2. Updates potentially out-of-date table statistics.

******************************************************************************/

USE SUSDB;

SET NOCOUNT ON;

-- Rebuild or reorganize indexes based on their fragmentation levels

DECLARE @work_to_do TABLE (

objectid int

, indexid int

, pagedensity float

, fragmentation float

, numrows int

)

DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @schemaname nvarchar(130);

DECLARE @objectname nvarchar(130);

DECLARE @indexname nvarchar(130);

DECLARE @numrows int

DECLARE @density float;

DECLARE @fragmentation float;

DECLARE @command nvarchar(4000);

DECLARE @fillfactorset bit

DECLARE @numpages int

-- Select indexes that need to be defragmented based on the following

-- * Page density is low

-- * External fragmentation is high in relation to index size

PRINT 'Estimating fragmentation: Begin. ' + convert(nvarchar, getdate(), 121)

INSERT @work_to_do

SELECT

f.object_id

, index_id

, avg_page_space_used_in_percent

, avg_fragmentation_in_percent

, record_count

FROM

sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'SAMPLED') AS f

WHERE

(f.avg_page_space_used_in_percent < 85.0 and f.avg_page_space_used_in_percent/100.0 * page_count < page_count - 1)

or (f.page_count > 50 and f.avg_fragmentation_in_percent > 15.0)

or (f.page_count > 10 and f.avg_fragmentation_in_percent > 80.0)

PRINT 'Number of indexes to rebuild: ' + cast(@@ROWCOUNT as nvarchar(20))

PRINT 'Estimating fragmentation: End. ' + convert(nvarchar, getdate(), 121)

SELECT @numpages = sum(ps.used_page_count)

FROM

@work_to_do AS fi

INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id

INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id

-- Declare the cursor for the list of indexes to be processed.

DECLARE curIndexes CURSOR FOR SELECT * FROM @work_to_do

-- Open the cursor.

OPEN curIndexes

-- Loop through the indexes

WHILE (1=1)

BEGIN

FETCH NEXT FROM curIndexes

INTO @objectid, @indexid, @density, @fragmentation, @numrows;

IF @@FETCH_STATUS < 0 BREAK;

SELECT

@objectname = QUOTENAME(o.name)

, @schemaname = QUOTENAME(s.name)

FROM

sys.objects AS o

INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id

WHERE

o.object_id = @objectid;

SELECT

@indexname = QUOTENAME(name)

, @fillfactorset = CASE fill_factor WHEN 0 THEN 0 ELSE 1 END

FROM

sys.indexes

WHERE

object_id = @objectid AND index_id = @indexid;

IF ((@density BETWEEN 75.0 AND 85.0) AND @fillfactorset = 1) OR (@fragmentation < 30.0)

SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

ELSE IF @numrows >= 5000 AND @fillfactorset = 0

SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90)';

ELSE

SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

PRINT convert(nvarchar, getdate(), 121) + N' Executing: ' + @command;

EXEC (@command);

PRINT convert(nvarchar, getdate(), 121) + N' Done.';

END

-- Close and deallocate the cursor.

CLOSE curIndexes;

DEALLOCATE curIndexes;

IF EXISTS (SELECT * FROM @work_to_do)

BEGIN

PRINT 'Estimated number of pages in fragmented indexes: ' + cast(@numpages as nvarchar(20))

SELECT @numpages = @numpages - sum(ps.used_page_count)

FROM

@work_to_do AS fi

INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id

INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id

PRINT 'Estimated number of pages freed: ' + cast(@numpages as nvarchar(20))

END

--Update all statistics

PRINT 'Updating all statistics.' + convert(nvarchar, getdate(), 121)

EXEC sp_updatestats

PRINT 'Done updating statistics.' + convert(nvarchar, getdate(), 121)

(5)データベースの最適化が終了すると、 “Statistics for all tables have been updated” または “全テーブルの統計が更新されました” というメッセージが表示される以上。

参考サイト：
Microsoft Technet,”WSUS DB インデックスの再構成の手順について”,
“https://blogs.technet.microsoft.com/jpwsus/2014/03/05/wsus-db/″,(access:2017.7.18)

2025年5月
月	火	水	木	金	土	日
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル