Why is WSUS
Maintenance Important?
As WSUS stores
metadata, update content, and client communication information, the WSUS
database can grow over time. If left unmanaged, this can lead to:
- Slower performance for both WSUS console operations and
client update installations.
- Excessive disk usage, leading to storage issues.
- Database fragmentation, which reduces the speed of queries
and operations.
To avoid these
issues, regular maintenance is necessary.
1. Reindexing
the WSUS Database
Reindexing helps
optimize the performance of the WSUS database by reorganizing and rebuilding
indexes. This improves query performance, making it faster to retrieve update
information.
Steps to
Reindex the WSUS Database:
- Connect to SQL Server: Open SQL Server Management Studio
(SSMS) and connect to the SQL Server instance hosting the WSUS
database (SUSDB).
- Rebuild Indexes:
- Once connected to the database, you
can run the following script to rebuild all indexes in the SUSDB:
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
This will update statistics for the WSUS
database and rebuild the indexes, improving performance.
2. Updating
Database Statistics
Updating
statistics in the WSUS database helps SQL Server determine the most efficient
way to execute queries. Over time, as data in the database changes, SQL queries
can become less efficient if the statistics are outdated.
Steps to Update
Database Statistics:
- Run the Update Statistics Command:
- You can update statistics for the
entire WSUS database by running:
--Update all
statistics
PRINT 'Updating
all statistics.' + convert(nvarchar, getdate(), 121)
EXEC
sp_updatestats
PRINT 'Done
updating statistics.' + convert(nvarchar, getdate(), 121)
GO
This command
updates the statistics for all tables in the database, improving the query
execution plan used by SQL Server.
- Schedule Regular Updates:
- To keep the database running
efficiently, it’s a good practice to schedule regular updates to
statistics. You can automate this by creating a SQL job to run the sp_updatestats
procedure at regular intervals.
3. Viewing and
Managing Updates in WSUS
One of the key
tasks in WSUS maintenance is managing the updates available for clients.
Regularly reviewing, approving, and declining updates can help ensure that only
relevant and required updates are distributed to clients, reducing unnecessary
bandwidth and storage usage.
Check Decline,
superseed & ununsed update count
select
--(Select count
(*) 'Total Updates' from vwMinimalUpdate ) 'Total Updates',
--(Select count
(*) 'Live updates' from vwMinimalUpdate
where declined=0) as 'Live Updates',
--(Select count
(*) 'Superseded' from vwMinimalUpdate
where IsSuperseded =1) as 'Superseded',
--(Select count
(*) 'Superseded But NoDeclined' from
vwMinimalUpdate where IsSuperseded =1 and declined=0) as 'Superseded but not
declined',
(Select count (*)
'Declined' from vwMinimalUpdate where
declined=1) as 'Declined',
(Select count (*)
'Superseded & Declined' from vwMinimalUpdate where IsSuperseded =1 and
declined=1) 'Superseded & Declined'
Query to
Decline update superseded 90 Days
DECLARE
@thresholdDays INT = 90 -- Specify the
number of days between today and the release date for which the superseded
updates must not be declined.
DECLARE @testRun
BIT = 0 -- Set this to 1 to test
without declining anything.
-- There shouldn't
be any need to modify anything after this.
DECLARE @uid
UNIQUEIDENTIFIER
DECLARE @title
NVARCHAR(500)
DECLARE @date
DATETIME
DECLARE @userName
NVARCHAR(100) = SYSTEM_USER
DECLARE @count INT
= 0
DECLARE DU CURSOR
FOR
SELECT MU.UpdateID, U.DefaultTitle,
U.CreationDate FROM vwMinimalUpdate MU
JOIN PUBLIC_VIEWS.vUpdate U ON
MU.UpdateID = U.UpdateId
WHERE MU.IsSuperseded = 1 AND
MU.Declined = 0 AND MU.IsLatestRevision = 1
AND MU.CreationDate <
DATEADD(dd,-@thresholdDays,GETDATE())
ORDER BY MU.CreationDate
PRINT 'Declining
superseded updates older than ' + CONVERT(NVARCHAR(5), @thresholdDays) + '
days.' + CHAR(10)
OPEN DU
FETCH NEXT FROM DU
INTO @uid, @title, @date
WHILE
(@@FETCH_STATUS > - 1)
BEGIN
SET @count = @count + 1
PRINT 'Declining update ' +
CONVERT(NVARCHAR(50), @uid) + ' (Creation Date ' + CONVERT(NVARCHAR(50), @date)
+ ') - ' + @title + ' ...'
IF @testRun = 0
EXEC spDeclineUpdate @updateID =
@uid, @adminName = @userName, @failIfReplica = 1
FETCH NEXT FROM DU INTO @uid, @title,
@date
END
CLOSE DU
DEALLOCATE DU
PRINT CHAR(10) +
'Attempted to decline ' + CONVERT(NVARCHAR(10), @count) + ' updates.'
WSUS delete old
update
DECLARE @UpdateID
VARCHAR(50)
DECLARE @msg
nvarchar(100)
DECLARE Curs
CURSOR FOR
SELECT UpdateID FROM
[SUSDB].[PUBLIC_VIEWS].[vUpdate] where IsDeclined=1
OPEN Curs FETCH
NEXT FROM Curs into @UpdateID
WHILE
@@FETCH_STATUS = 0
BEGIN
SET @msg =
'Deleting' + CONVERT(varchar(100), @UpdateID)
RAISERROR(@msg,0,1)
WITH NOWAIT
exec
spDeleteUpdateByUpdateID @UpdateID FETCH NEXT FROM Curs into @UpdateID
END
CLOSE Curs
DEALLOCATE Curs
Conclusion
Regular
maintenance of your WSUS environment is essential for optimal performance and
reliability. By reindexing the database, updating statistics, managing update
approvals, and cleaning up old records, you can ensure that WSUS remains
efficient and capable of distributing updates in a timely manner.
Automating these
tasks using SQL jobs or PowerShell scripts will help keep WSUS running smoothly
without requiring constant manual intervention.
