Enhancing WSUS database cleanup performance SQL script

There have been many reports of slow and sluggish clean up and removal of expired updates. An old WSUS database can include 1000’s of updates that need to be removed.

Turns out that adding two non-clustered indexes on SUSDB can DRAMTICALLY improve removal of expired updates.

This is a fine example of a community solution.

Some background reading:

Wsus Delete Obsolete Updates – where the key is the addition of the added indexes to SUSDB.

Fixing WSUS – When the Best Defense is a Good Offense – Johan does an excellent job of documenting the many things folks have tried, with varying degrees of success.

Finally, an awesome solution that includes creating the Indexes needed if they do not exist. Heavily commented. Copy and paste the following into SQL Server Management Studio.

Authored by Benjamin Reynolds (Microsoft) – be sure to follow him on Twitter.

USE [SUSDB];

DECLARE @BatchSize int = NULL; -- NULL = do all;  <<-- Control batches here

SET NOCOUNT ON;

-- Check to see if the delete indexes exist; if not don't try to run the script:
DECLARE @IndexRetry bit = 1;
IndexRetry:
IF INDEXPROPERTY(OBJECT_ID(N'tbRevisionSupersedesUpdate'),N'IX_tbRevisionSupersedesUpdate',N'IndexID') IS NULL
   OR INDEXPROPERTY(OBJECT_ID(N'tbLocalizedPropertyForRevision'),N'IX_tbLocalizedPropertyForRevision',N'IndexID') IS NULL
GOTO NeedIndexes;

-- Create tables/variables:
DROP TABLE IF EXISTS #Results; -- This will only work on newer versions of SQL Server 2016+
DECLARE  @UpdateId int
        ,@CurUpdate int
        ,@TotalToDelete int
        ,@Msg varchar(2000);
CREATE TABLE #Results (RowNum int IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL, UpdateId int NOT NULL);
INSERT INTO #Results (UpdateId)
EXECUTE dbo.spGetObsoleteUpdatesToCleanup;

-- If a batch size was provided update the table so we only take care of that many items during this run:
IF @BatchSize IS NOT NULL
DELETE #Results
 WHERE RowNum > @BatchSize;

-- Assign working variables:
SELECT @TotalToDelete = MAX(RowNum)
  FROM #Results;
-- 
SELECT @Msg = 'Total Updates to Delete: '+CONVERT(varchar(10),@TotalToDelete);
RAISERROR (@Msg,0,1) WITH NOWAIT;

-- Create the loop to delete the updates one at a time:
WHILE EXISTS (SELECT * FROM #Results)
BEGIN
    -- Grab the "current" item:
    SELECT  TOP 1 @CurUpdate = RowNum
           ,@UpdateId = UpdateId
      FROM #Results
     ORDER BY RowNum;
    
    -- Provide some info during the script runtime:
    SELECT @Msg = CONVERT(varchar(30),GETDATE(),20) + ': Deleting ' + CONVERT(varchar(5),@CurUpdate) + '/' + CONVERT(varchar(5),@TotalToDelete) + ' = ' + CONVERT(varchar(10), @UpdateId);
    RAISERROR(@Msg,0,1) WITH NOWAIT;
    
    -- Delete the current update from the DB:
    EXECUTE dbo.spDeleteUpdate @localUpdateID = @UpdateId;
    
    -- Delete the current update from the table so we can get the next item:
    DELETE #Results
     WHERE RowNum = @CurUpdate;
END;
GOTO EndScript;

NeedIndexes:
-- If the indexes don't exist we'll try to create them and start over or end if we already tried once:
IF @IndexRetry = 0
BEGIN
    PRINT N'Indexes Required to run this script do not exist! Create them and re-run for optimal performance!';
    GOTO EndScript;
END;
ELSE
BEGIN
    IF INDEXPROPERTY(OBJECT_ID(N'tbRevisionSupersedesUpdate'),N'IX_tbRevisionSupersedesUpdate',N'IndexID') IS NULL
    BEGIN
        SELECT @Msg = CONVERT(varchar(30),GETDATE(),20) + ': Index "IX_tbRevisionSupersedesUpdate" does not exist; Creating it...';
        RAISERROR(@Msg,0,1) WITH NOWAIT;
        EXECUTE (N'USE [SUSDB]; CREATE NONCLUSTERED INDEX IX_tbRevisionSupersedesUpdate ON dbo.tbRevisionSupersedesUpdate(SupersededUpdateID);');
        SELECT @Msg = CONVERT(varchar(30),GETDATE(),20) + ': ..."IX_tbRevisionSupersedesUpdate" created.';
        RAISERROR(@Msg,0,1) WITH NOWAIT;
    END;
    IF INDEXPROPERTY(OBJECT_ID(N'tbLocalizedPropertyForRevision'),N'IX_tbLocalizedPropertyForRevision',N'IndexID') IS NULL
    BEGIN
        SELECT @Msg = CONVERT(varchar(30),GETDATE(),20) + ': Index "IX_tbLocalizedPropertyForRevision" does not exist; Creating it...';
        RAISERROR(@Msg,0,1) WITH NOWAIT;
        EXECUTE (N'USE [SUSDB]; CREATE NONCLUSTERED INDEX IX_tbLocalizedPropertyForRevision ON dbo.tbLocalizedPropertyForRevision(LocalizedPropertyID);');
        SELECT @Msg = CONVERT(varchar(30),GETDATE(),20) + ': ..."IX_tbLocalizedPropertyForRevision" created.';
        RAISERROR(@Msg,0,1) WITH NOWAIT;
    END;
    
    SET @IndexRetry = 0;
    GOTO IndexRetry;
END;

EndScript:
DROP TABLE IF EXISTS #Results;

Advertisements
This entry was posted in SCCM, SQL Server, Uncategorized, WSUS. Bookmark the permalink.

9 Responses to Enhancing WSUS database cleanup performance SQL script

  1. Pingback: Enhancing WSUS database cleanup performance SQL script — Steve Thompson [MVP] – Being Configuration Manager Administrator

  2. Ryan Plume says:

    How can this be made to work with WSUS SQL Servers that aren’t 2016+?

    • ConfigMgrMVP says:

      You’ll need to remark the lines that perform the existence checks for the Indexes objects, prior to creating them. If time permits I’ll document this… or, you upgrade to SQL 2016 🙂

  3. Ryan Plume says:

    So comment out “DROP TABLE IF EXISTS #Results;” or does it need to be replaced with something else? You suggest lines (plural) but I’m not quite sure which other ones may need consideration. Thanks for your help, our PFE has talked this up quite a bit!

    • ConfigMgrMVP says:

      Yes that one:
      DROP TABLE IF EXISTS #Results; — This will only work on newer versions of SQL Server

  4. Markus Klocker says:

    Will this work on WID as well?

  5. Pingback: Maintaining the WSUS Catalog by Declining Updates for Better Update Scanning – Justin Chalfant's TechNet Blog

  6. Pingback: Enhancing WSUS database cleanup performance SQL script | System Center

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.