locked
Overcoming the slow WSUS Clean up RRS feed

  • General discussion

  • I was struggle for a little bit during the WSUS clean up process.  For me it timed-out every time.... I knew there were lots of updates that needed to be removed from the server.

    Here is the PowerShell command I was running to do the clean-up:

    Invoke-WsusServerCleanup -DeclineSupersededUpdates -DeclineExpiredUpdates -CleanupObsoleteUpdates -CleanupUnneed

    For me the cause of the problem very slow SQL commands running in the WSUS database... which was the result of missing indexes. (By the way --- our WSUS server is running Windows Server 2016 with the latest patches, and the Database Engine is SQL Server 2016).

    Before continuing.... please note that I have made changes to our WSUS database to increase performance.  No one else approved, acknowledged, or supported this.  If you decide to try this then you do so at your own risk...... a database backup is always a good thing.  (I know in my case, I will reverse the changes whenever I need to do a WSUS update, and then add them after the update completes.)

    The command that stuck out like a sore thumb was "spDeleteUpdate".  It appears to execute once for each Windows Update, and it took over one minute to complete.  My first change was to add a clustered index to the temporary table in that stored procedure.  I changed this command: 

    DECLARE @revisionList TABLE(RevisionID INT)
    to this:
    DECLARE @revisionList TABLE(RevisionID INT Index idx_nc_RevisionID CLUSTERED)

    Just making this change improved the query execution.  What was talking 555,000 reads was now only taking 2000.  The table "@revisionList" gets used in searches later in the stored procedure and the index makes it much faster.

    Now comes the rest missing indexes which I applied directly to the tables.  I added quite of few.... and I realize some are probably not needed, but they certainly didn't hurt.  Here are the ones I added:

    CREATE INDEX [IXMF_ivwApiUpdateRevision_IsLatestRevision] ON [SUSDB].[dbo].[ivwApiUpdateRevision] ([IsLatestRevision]) INCLUDE ([RevisionID], [IsHidden], [IsLocallyPublished], [IsMandatory]);
    CREATE INDEX [IXMF_ivwApiUpdateRevision_IsLatestRevision_IsHidden] ON [SUSDB].[dbo].[ivwApiUpdateRevision] ([IsLatestRevision], [IsHidden]) INCLUDE ([LocalUpdateID], [EffectiveArrivalTime], [RevisionID]);
    CREATE INDEX [IXMF_ivwApiUpdateRevision_UpdateID_IsLatestRevision_IsHidden] ON [SUSDB].[dbo].[ivwApiUpdateRevision] ([UpdateID], [IsLatestRevision], [IsHidden]);
    CREATE INDEX [IXMF_tbDeadDeployment_TargetGroupID_TargetGroupTypeID_LastChangeNumber] ON [SUSDB].[dbo].[tbDeadDeployment] ([TargetGroupID], [TargetGroupTypeID],[LastChangeNumber]);
    CREATE INDEX [IXMF_tbDeadDeployment_TargetGroupTypeID_LastChangeNumber] ON [SUSDB].[dbo].[tbDeadDeployment] ([TargetGroupTypeID], [LastChangeNumber]) INCLUDE ([DeploymentID], [RevisionID], [TargetGroupID], [UpdateID], [RevisionNumber]);
    CREATE INDEX [IXMF_tbDeadDeployment_TargetGroupTypeID_LastChangeNumber_UpdateType] ON [SUSDB].[dbo].[tbDeadDeployment] ([TargetGroupTypeID],[LastChangeNumber], [UpdateType]) INCLUDE ([DeploymentID], [RevisionID], [TargetGroupID], [UpdateID], [RevisionNumber]);
    CREATE INDEX [IXMF_tbDeadDeployment_TargetGroupTypeID_TimeOfDeath_ActionID] ON [SUSDB].[dbo].[tbDeadDeployment] ([TargetGroupTypeID],[TimeOfDeath], [ActionID]) INCLUDE ([RevisionID]);
    CREATE INDEX [IXMF_tbDeployment_ActionID_TargetGroupTypeID] ON [SUSDB].[dbo].[tbDeployment] ([ActionID], [TargetGroupTypeID]) INCLUDE ([RevisionID]);
    CREATE INDEX [IXMF_tbDeployment_DeploymentStatus_TargetGroupTypeID_LastChangeNumber_UpdateType] ON [SUSDB].[dbo].[tbDeployment] ([DeploymentStatus], [TargetGroupTypeID],[LastChangeNumber], [UpdateType]) INCLUDE ([GoLiveTime], [RevisionID], [TargetGroupID]);
    CREATE INDEX [IXMF_tbDeployment_TargetGroupTypeID] ON [SUSDB].[dbo].[tbDeployment] ([TargetGroupTypeID]) INCLUDE ([ActionID], [RevisionID]);
    CREATE INDEX [IXMF_tbDeployment_TargetGroupTypeID_ActionID] ON [SUSDB].[dbo].[tbDeployment] ([TargetGroupTypeID],[ActionID]) INCLUDE ([RevisionID]);
    CREATE INDEX [IXMF_tbDeployment_TargetGroupTypeID_LastChangeNumber] ON [SUSDB].[dbo].[tbDeployment] ([TargetGroupTypeID],[LastChangeNumber]) INCLUDE ([DeploymentID], [RevisionID]);
    CREATE INDEX [IXMF_tbDeployment_TargetGroupTypeID_LastChangeNumber_UpdateType] ON [SUSDB].[dbo].[tbDeployment] ([TargetGroupTypeID],[LastChangeNumber], [UpdateType]) INCLUDE ([DeploymentID], [DeploymentStatus], [ActionID], [GoLiveTime], [Deadline], [DownloadPriority], [IsAssigned], [RevisionID], [TargetGroupID], [IsLeaf], [Priority], [IsFeatured], [AutoSelect], [AutoDownload], [SupersedenceBehavior]);
    CREATE INDEX [IXMF_tbDeployment_TargetGroupTypeID_UpdateType_LastChangeNumber] ON [SUSDB].[dbo].[tbDeployment] ([TargetGroupTypeID], [UpdateType],[LastChangeNumber]) INCLUDE ([DeploymentID], [ActionID], [Deadline], [RevisionID], [TargetGroupID], [Priority]);
    CREATE INDEX [IXMF_tbEventInstance_EventNamespaceID_TimeAtServer] ON [SUSDB].[dbo].[tbEventInstance] ([EventNamespaceID],[TimeAtServer]) INCLUDE ([EventOrdinalNumber]);
    CREATE INDEX [IXMF_tbFileOnServer_ActualState] ON [SUSDB].[dbo].[tbFileOnServer] ([ActualState]) INCLUDE ([FileDigest], [RowID], [TimeAddedToQueue]);
    CREATE INDEX [IXMF_tbLocalizedPropertyForRevision_LocalizedPropertyID] ON [SUSDB].[dbo].[tbLocalizedPropertyForRevision] ([LocalizedPropertyID]);
    CREATE INDEX [IXMF_tbProperty_CreationDate_ReceivedFromCreatorService] ON [SUSDB].[dbo].[tbProperty] ([CreationDate], [ReceivedFromCreatorService]) INCLUDE ([RevisionID], [PublicationState]);
    CREATE INDEX [IXMF_tbProperty_ExplicitlyDeployable_UpdateType] ON [SUSDB].[dbo].[tbProperty] ([ExplicitlyDeployable],[UpdateType]) INCLUDE ([RevisionID]);
    CREATE INDEX [IXMF_tbProperty_PublicationState] ON [SUSDB].[dbo].[tbProperty] ([PublicationState]) INCLUDE ([RevisionID]);
    CREATE INDEX [IXMF_tbProperty_PublicationState_ReceivedFromCreatorService] ON [SUSDB].[dbo].[tbProperty] ([PublicationState],[ReceivedFromCreatorService]) INCLUDE ([RevisionID], [ExplicitlyDeployable], [UpdateType]);
    CREATE INDEX [IXMF_tbRevision_IsLatestRevision] ON [SUSDB].[dbo].[tbRevision] ([IsLatestRevision]) INCLUDE ([RevisionID], [LocalUpdateID]);
    CREATE INDEX [IXMF_tbRevision_IsLeaf] ON [SUSDB].[dbo].[tbRevision] ([IsLeaf]) INCLUDE ([RevisionID], [LocalUpdateID]);
    CREATE INDEX [IXMF_tbRevision_IsMandatory] ON [SUSDB].[dbo].[tbRevision] ([IsMandatory]) INCLUDE ([LocalUpdateID], [RevisionID]);
    CREATE INDEX [IXMF_tbRevision_State] ON [SUSDB].[dbo].[tbRevision] ([State]) INCLUDE ([LocalUpdateID], [RevisionID]);
    CREATE INDEX [IXMF_tbRevisionInCategory_CategoryID_Expanded] ON [SUSDB].[dbo].[tbRevisionInCategory] ([CategoryID], [Expanded]) INCLUDE ([RevisionID]);
    CREATE INDEX [IXMF_tbRevisionInCategory_Expanded] ON [SUSDB].[dbo].[tbRevisionInCategory] ([Expanded]) INCLUDE ([RevisionID], [CategoryID]);
    CREATE INDEX [IXMF_tbRevisionSupersedesUpdate_SupersededUpdateID] ON [SUSDB].[dbo].[tbRevisionSupersedesUpdate] ([SupersededUpdateID]);
    CREATE INDEX [IXMF_tbUpdate_IsHidden] ON [SUSDB].[dbo].[tbUpdate] ([IsHidden]) INCLUDE ([LocalUpdateID], [UpdateID]);
    CREATE INDEX [IXMF_tbUpdate_IsHidden_ImportedTime] ON [SUSDB].[dbo].[tbUpdate] ([IsHidden],[ImportedTime]) INCLUDE ([LocalUpdateID], [UpdateID]);
    CREATE INDEX [IXMF_ivwApiUpdateRevision_IsLatestRevision_IsHidden_IsLocallyPublished] ON [SUSDB].[dbo].[ivwApiUpdateRevision] ([IsLatestRevision], [IsHidden], [IsLocallyPublished]);
    CREATE INDEX [IXMF_tbDeployment_UpdateType_LastChangeNumber] ON [SUSDB].[dbo].[tbDeployment] ([UpdateType],[LastChangeNumber]) INCLUDE ([RevisionID]);
    CREATE INDEX [IXMF_tbFileOnServer_ActualState_DSSRequestedDownload] ON [SUSDB].[dbo].[tbFileOnServer] ([ActualState], [DSSRequestedDownload]) INCLUDE ([FileDigest], [RowID]);
    CREATE INDEX [IXMF_tbFileOnServer_DSSRequestedDownload] ON [SUSDB].[dbo].[tbFileOnServer] ([DSSRequestedDownload]) INCLUDE ([FileDigest]);

    Note:  If you are running these in SQLCMD, make sure you run "set quoted_identifier on" first. 

    The end result: What took over a minute to execute "spDeleteUpdate" now takes a small fraction of the time.  The entire clean-up command completed successfully in a reasonable amount of time, and I got about 60 GB of disk space back.

    Hope this helps someone who has ran into a same problem.


    • Edited by Forch718 Monday, September 25, 2017 2:23 PM
    Monday, September 25, 2017 2:21 PM

All replies

  • I'm curious if you created the same ones I created and that my script creates (just in a different SQL method - I'll be examining these closely after some sleep).

    Have a peek at my Adamj Clean-WSUS script. It is the last WSUS Script you will ever need!

    http://community.spiceworks.com/scripts/show/2998-adamj-clean-wsus

    What it does:

    1. Add WSUS Index Optimization to the database to increase the speed of many database operations in WSUS by approximately 1000-1500 times faster.
    2. Remove all Drivers from the WSUS Database (Default; Optional).
    3. Shrink your WSUSContent folder's size by declining multiple types of updates including by default any superseded updates, preview updates, expired updates, Itanium updates, and beta updates. Optional extras: Language Packs, IE7, IE8, IE9, IE10, Embedded, NonEnglishUpdates, ComputerUpdates32bit, WinXP.
    4. Remove declined updates from the WSUS Database.
    5. Clean out all the synchronization logs that have built up over time (configurable, with the default keeping the last 14 days of logs).
    6. Compress Update Revisions.
    7. Remove Obsolete Updates.
    8. Computer Object Cleanup (configurable, with the default of deleting computer objects that have not synced within 30 days).
    9. Application Pool Memory Configuration to display the current private memory limit and easily set it to any configurable amount including 0 for unlimited. This is a manual execution only.
    10. Checks to see if you have a dirty database, and if you do, fixes it. This is primarily for Server 2012 WSUS, and is a manual execution only.
    11. Run the Recommended SQL database Maintenance script on the actual SQL database.
    12. Run the Server Cleanup Wizard.

    It will email the report out to you or save it to a file, or both.

    Although the script is lengthy, it has been made to be super easy to setup and use so don't over think it. There are some prerequisites and instructions at the top of the script. After installing the prerequisites and configuring the variables for your environment (email settings only if you are accepting all the defaults), simply run:

    .\Clean-WSUS.ps1 -FirstRun

    If you wish to view or increase the Application Pool Memory Configuration, or run the Dirty Database Check, you must run it with the required switch. See Get-Help .\Clean-WSUS.ps1 -Examples

    If you're having trouble, there's also a -HelpMe option that will create a log so you can send it to me for support.


    Adam Marshall, MCSE: Security
    http://www.adamj.org

    Wednesday, September 27, 2017 4:05 AM
  • Very nice.... that script is quite intense! 

    Without checking, I would imagine the indexes are similar.... all I did was use the "Missing Index" feature and removed the duplicate suggestions.  (In many cases the index columns were the same, but the "included" columns where different.)

    I also see it takes care of the private memory. In my case I also needed to disable the "Rapid Fail Protection" after the default installation.  This is the script I'm using for that:

    &"c:\Windows\System32\inetsrv\appcmd.exe" set apppool /apppool.name:wsuspool /recycling.periodicRestart.privateMemory:"0"
    &"c:\Windows\System32\inetsrv\appcmd.exe" set apppool /apppool.name:wsuspool /failure.rapidFailProtection:"false"

    It's nice "not" to struggle with this anymore!!!!

    Wednesday, September 27, 2017 8:22 PM
  • I think if your WSUS is running on the Windows Internal Database (WID), you can't change any SPs or add indexes. Doing so will cause a signature error.

    Wednesday, July 11, 2018 5:08 PM