Skip to content

Instantly share code, notes, and snippets.

@emnavarro02
Last active May 7, 2024 13:04
Show Gist options
  • Save emnavarro02/0ffd6481ce7c9b207f7762732fd73aa8 to your computer and use it in GitHub Desktop.
Save emnavarro02/0ffd6481ce7c9b207f7762732fd73aa8 to your computer and use it in GitHub Desktop.
Re-index the WSUS Database

The performance of large Windows Server Update Services (WSUS) deployments will degrade over time if the WSUS database is not maintained properly. The WSUSDBMaintenance script is a T-SQL script that can be run by SQL Server administrators to re-index and defragment WSUS databases. It should not be used on WSUS 2.0 databases.This script contributed by the Microsoft WSUS team.

If you are using Windows Internal Database, you will need to use the sqlcmd utility, which can be downloaded from docs.microsoft.com page for SQLCMD. This page will also contain additional information about the sqlcmd utility.

To use this script with Windows Internal Database, you should run the following command:

sqlcmd -S np:\\.\pipe\MICROSOFT##WID\tsql\query -i <script-location>\WSUSDBMaintenance.sql

The call to sqlcmd needs to have a -I (capital i) parameter, which tells SQL to run the script with QUOTED_IDENTIFIERS enabled.

Originaly stored at: https://gallery.technet.microsoft.com/scriptcenter/6f8cde49-5c52-4abd-9820-f1d270ddea61#content

/******************************************************************************
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
@Digiover
Copy link

Digiover commented Dec 3, 2021

##SSEE changed into ##WID to connect to Windows Internal Database.

@mapwiz
Copy link

mapwiz commented Oct 17, 2022

The correct command to run against WID is sqlcmd -S np:\\.\pipe\MICROSOFT##WID\tsql\query -i <script-location>\WSUSDBMaintenance.sql.

@emnavarro02
Copy link
Author

##SSEE changed into ##WID to connect to Windows Internal Database.

Thanks!

@emnavarro02
Copy link
Author

The correct command to run against WID is sqlcmd -S np:\\.\pipe\MICROSOFT##WID\tsql\query -i <script-location>\WSUSDBMaintenance.sql.

Thanks!

@mapwiz
Copy link

mapwiz commented Oct 18, 2022

I got the following error on one of the index rebuilds:

2022-10-18 08:04:50.210 Executing: ALTER INDEX [nc_LocalUpdateID] ON [dbo].[ivwApiUpdateRevision] REBUILD WITH (FILLFACTOR = 90)
Msg 1934, Level 16, State 1, Server BLAHWSUS\MICROSOFT##WID, Line 1
ALTER INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Notice this is a non-clustered, non-unique index created on a view. IF you script this index to a query editor window, you see that it's created with QUOTED_IDENTIFIER ON. I was able to manually rebuild this index in SQL Server management studio.

After that, I ran the script again and different index popped out :

ALTER INDEX [nc_EffectiveArrivalTime] ON [dbo].[ivwApiUpdateRevision] REBUILD WITH (FILLFACTOR = 90)

I was also able to manually rebuild this index, and it was also created with QUOTED_IDENTIFIER ON. I checked a couple of the other indexes that never had a problem and they were created without setting QUOTED_IDENTIFIER.,

I ran the script again and there were no more problems.

@mike406
Copy link

mike406 commented May 6, 2024

Regarding the above error in mapwiz's comment - if you do not wish to manually correct the indexes you can modify the script like this to skip over them:

On line 100, replace the following:

    EXEC (@command);  

With:

    BEGIN TRY
        EXEC (@command); 
    END TRY
    BEGIN CATCH
        SELECT ERROR_MESSAGE() AS ErrorMessage;
    END CATCH

@cosmicgumbo
Copy link

Can you explain how you manually rebuilt the index? I'm not very familiar with SQL. I am able to run the Query in SSMS, but in my scheduled task, it throws the error like mapwiz.

Line with error:
ALTER INDEX [nc_LocalUpdateID] ON [dbo].[ivwApiUpdateRevision] REBUILD WITH (FILLFACTOR = 90)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment