WSUS-Datenbankindex neu erstellen / defragmentieren (SUSDB)

Sicherheitshinweis

Die hier bereitgestellten Skripte sind stets auf eigene Gefahr anzuwenden!
Daher immer erst mit einem nicht-kritischen System oder in einer Testumgebung ausführlich testen!

Einleitung

Wird die WSUS-Datenbank nicht regelmäßig indexiert, kann die Leistung mit der Zeit entsprechend abnehmen. Nach massiven Änderungen wie z.B. das Entfernen einer Klassifizierung und deren Updatedateien, sollte IMMER eine Re-Indexierung der WSUS-Datenbank zur Folge haben.

Empfehlung:

Die neu Indexierung sollte aber grundsätzlich regelmäßig durchgeführt werden.

Voraussetzungen

In diesem Beitrag erläutere ich, wie diese Tätigkeiten mittels der Kommandozeile entsprechend durchgeführt werden können. Jedoch sind hierzu gewisse Voraussetzungen zu treffen, damit auf die SUSDB aus der Kommandozeile erfolgreich zugegriffen werden kann. Daher sind die folgenden Installationen zwingende Voraussetzungen…

Möglichkeit 1:

Microsoft SQL Server Management Studio (SSMS) (Download)
– Ersetzt Möglichkeit 2! –

Möglichkeit 2:

Microsoft Visual C++ 201x Redistributable (Download)
Microsoft ODBC Driver 17 for SQL Server (Download)
Microsoft Befehlenszeilenprogramm 15 für SQL Server (Download)
– Ersetzt Möglichkeit 1! –

Vorbereitungen

Aus der Kommandozeilenebene heraus, wird eine *.sql- Datei aufgerufen und ausgeführt. Deswegen muss zuvor noch eine entsprechende *.sql- Datei erstellt werden. Die Bennenung der SQL-Datei ist grundsätzlich egal!

SQL-Skript:

/****************************************************************************** 
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;
SET QUOTED_IDENTIFIER 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

Für den späteren Aufruf in der Kommandozeile, die *.sql- Datei entsprechend lokal auf dem System abspeichern. Für die Automatisierung bietet sich auf der Kommandozeile ebenfalls eine entsprechende *.cmd- Datei an.

CMD-Skript-Beispiel:

REM ============================================================================
@ECHO OFF
SETLOCAL ENABLEDELAYEDEXPANSION
SET CMDNAME=%~NX0 (%~Z0 Bytes) 
TITLE %CMDNAME%
MODE CON:COLS=81 LINES=12
COLOR 1F
REM - Run SQL Script to prepare the database environment
REM [-S Server]
REM [-E vertrauenswürdige Verbindung]
REM [-i Eingabedatei]
REM [-o Ausgabedatei]
REM [-W nachfolgende Leerzeichen entfernen]

ECHO Preparing Databases...
Sqlcmd -S "np:\\.\pipe\MICROSOFT##WID\tsql\query" -E -i "C:\Scripts\WSUS\index.sql" -o "C:\Scripts\WSUS\_LOG\index.log" -W
ENDLOCAL
EXIT /B %ERRORLEVEL%
REM ============================================================================

^{(Ist die WSUS-DB umbenannt bzw. besitzt einen anderen DB-Namen, muss der entsprechende Aufruf abgeändert werden: \.\pipe\MICROSOFT##WID\tsql\query)}

Für den späteren Aufruf in der Aufgabenplanung, die *.cmd- Datei entsprechend lokal auf dem System abspeichern. Zusätzlich muss das LOG-Verzeichnis zuvor erstellt sein. Die Speicherpfade an die eigenen Bedürfnisse anpassen!

-E -i "C:\Scripts\WSUS\index.sql" -o "C:\Scripts\WSUS\_LOG\index.log" -W

Index neu erstellen

Sind die Vorbereitungen abgearbeitet, ist die Durchführung der ausstehenden Aktionen schnell getan.

Variante 1:

Die *.cmd- Datei manuell mit administrativer Berechtigungen starten und ausführen.

Variante 2:

In der Aufgabenplanung eine neue Aufgabe erstellen und hierbei auf die folgenden Optionen achten…

Allgemein (Reiter) –> Sicherheitsoptionen –>
Unnabhängig von der Benutzeranmeldung ausführen
Allgemein (Reiter) –> Sicherheitsoptionen –>
Mit höchsten Privilegien ausführen
Allgemein (Reiter) –> Konfigurieren für:
Windows Server 20xx
Aktionen (Reiter) –> Neu –> Aktion: –>
Programm starten
Aktionen (Reiter) –> Neu –> Einstellungen –> Programm/Skript –>
C:\Scripts\WSUS\index.cmd

Prüfung der Indexierung

Die Kontrolle, welche Datanbanktabellen aktualisiert worden sind, sind aus der erstellten LOG-Datei zu entnehmen.

Beispiel:

C:\Scripts\WSUS\_LOG\index.log

Für mich war es leider nicht möglich SQLCMD zu verwenden in der DMZ konnte ich keine Files übertragen für Installationen auf einem Normalen WSUS Server hab ich mit der SQLCMD Methode Access Denide bekomen.
Daher hab ich eine Andere Variante für mich geschaffen. Mit einfachen Windowsboardmittel die vorhanden sind. Wen es einem Helfen kann. Einfach in eine Administrative ISE kopieren und ausführen. Das SQL Script das zum Anfang in die Variable $SQLScript angegeben ist/wird. Wird auf der SUSDB angewendet.

#### @‘.. SQL Script ..’@

$SQLScript = @‘
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 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 = 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)
‚@

# Connect to WID SUSDB and execute the SQL Script
$ConnectionString = ’server=\\.\pipe\MICROSOFT##WID\tsql\query;database=SUSDB;trusted_connection=true;‘
$SQLConnection = New-Object System.Data.SQLClient.SQLConnection($ConnectionString)
$SQLConnection.Open()
$SQLCommand = $SQLConnection.CreateCommand()
$SQLCommand.CommandTimeout = 0
$SQLCommand.CommandText = $SQLScript
$SqlDataReader = $SQLCommand.ExecuteReader()
$SQLDataResult = New-Object System.Data.DataTable
$SQLDataResult.Load($SqlDataReader)
$SQLConnection.Close()
$SQLDataResult

Antworten

4 thoughts on “WSUS-Datenbankindex neu erstellen / defragmentieren (SUSDB)”

Jakob Schitik sagt:

5. April 2024 um 19:40 Uhr

Für mich war es leider nicht möglich SQLCMD zu verwenden in der DMZ konnte ich keine Files übertragen für Installationen auf einem Normalen WSUS Server hab ich mit der SQLCMD Methode Access Denide bekomen.
Daher hab ich eine Andere Variante für mich geschaffen. Mit einfachen Windowsboardmittel die vorhanden sind. Wen es einem Helfen kann. Einfach in eine Administrative ISE kopieren und ausführen. Das SQL Script das zum Anfang in die Variable $SQLScript angegeben ist/wird. Wird auf der SUSDB angewendet.

#### @‘.. SQL Script ..’@

$SQLScript = @‘
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 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 = 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)
‚@

# Connect to WID SUSDB and execute the SQL Script
$ConnectionString = ’server=\\.\pipe\MICROSOFT##WID\tsql\query;database=SUSDB;trusted_connection=true;‘
$SQLConnection = New-Object System.Data.SQLClient.SQLConnection($ConnectionString)
$SQLConnection.Open()
$SQLCommand = $SQLConnection.CreateCommand()
$SQLCommand.CommandTimeout = 0
$SQLCommand.CommandText = $SQLScript
$SqlDataReader = $SQLCommand.ExecuteReader()
$SQLDataResult = New-Object System.Data.DataTable
$SQLDataResult.Load($SqlDataReader)
$SQLConnection.Close()
$SQLDataResult

Antworten
1. André Stuhr sagt:
  
  8. April 2024 um 10:05 Uhr
  
  „Viele Wege führen bekanntlich nach Rom!“ ^^
  Schön das auch eine 3. Option zur Verfügung steht. Danke für den Hinweis! 🙂
  
  Antworten
Andreas Steiger sagt:

9. Juni 2024 um 12:44 Uhr

Hallo,
ich möchte ein Reindizierung der SUSDB durchführen. Ich nutze für den WSUS die WID. In dem vom Microsoft empfohlenen Script ist die Zeile

SET QUOTED_IDENTIFIER ON;

nicht enthalten. Was macht die Zeile genau?

Antworten
1. André Stuhr sagt:
  
  9. Juli 2024 um 15:47 Uhr
  
  Moin Andreas,
  
  entschuldige bitte die späte Rückmeldung. Leider kam ich nicht früher zum Antworten.
  
  Diese Zeile besagt folgendes:
  „QUOTED_IDENTIFIER ermöglicht die Verwendung von doppelten Anführungszeichen zum Angeben von Bezeichnern (wie Tabellennamen).“
  
  Du hast Recht damit, dass dieser Aufruf im Original-Skript nicht vorhanden ist:
  https://learn.microsoft.com/de-de/troubleshoot/mem/configmgr/update-management/reindex-the-wsus-database
  
  Es kann sein, dass ich aus irgendeinem bestimmten Grund in der Vergangenheit diesen Aufruf benötigte. Aber leider kann ich Dir aktuell nicht mehr sagen, in welchem Szenario das so gewesen ist.
  
  Ich hoffe, das hilft Dir weiter. 🙂
  
  Grüße,
  André
  
  Antworten