Accelerating WSUS
A freshly installed WSUS update server after a few years of operation turns into a cumbersome monster. Clients update slowly and look for updates for a long time, WindowsUpdate.log logs are full of HTTP errors, and the administrator is depressed.
Here are some tips that can speed up a server in many cases.
1. Configure IIS
2. Cleaning the base
3. Reindexing
4. Configure TempDB
Configure Internet Information Server
In the default configuration, the “WsusPool” application pool is undeservedly deprived of memory. This is the main cause of HTTP server errors in client logs.
To add memory to the process, launch the IIS snap-in and open the Advanced settings:
')
Set the limit to 0.
Base cleaning
You can run the Server Cleanup Wizard in the WSUS console. The main secret is that it should be run regularly, at least once a month.
Approved a fresh pack of updates - clean the server. If this is not done, the wizard stops working normally. After starting it hangs for several hours, after which the console crashes.
For starters, you can try to run each item separately, from top to bottom:
If this does not help, you need to perform cleaning directly in the database. To do this, you need to connect to an instance of SQL Server with Management Studio. Management Studio has become a separate product. It can be downloaded from this link .
If you are using the Windows Internal Database, you must put Management Studio on a server with WSUS. To connect to the instance, use the string:
\\.\pipe\MICROSOFT##WID\tsql\query 
To clear the base, execute 4 magic commands:
EXEC SUSDB.dbo.spDeclineExpiredUpdates;1 EXEC SUSDB.dbo.spDeclineSupersededUpdates;1 For the spCompressUpdate command , a “wrapper” is used:
USE SUSDB DECLARE @var1 INT, @curitem INT, @totaltodelete INT DECLARE @msg nvarchar(200) CREATE TABLE #results (Col1 INT) INSERT INTO #results(Col1) EXEC spGetUpdatesToCompress SET @totaltodelete = (SELECT COUNT(*) FROM #results) SELECT @curitem=1 DECLARE WC Cursor FOR SELECT Col1 FROM #results OPEN WC FETCH NEXT FROM WC INTO @var1 WHILE (@@FETCH_STATUS > -1) BEGIN SET @msg = cast(@curitem as varchar(5)) + '/' + cast(@totaltodelete as varchar(5)) + ': Compressing ' + CONVERT(varchar(10), @var1) + ' ' + cast(getdate() as varchar(30)) RAISERROR(@msg,0,1) WITH NOWAIT EXEC spCompressUpdate @localUpdateID=@var1 SET @curitem = @curitem +1 FETCH NEXT FROM WC INTO @var1 END CLOSE WC DEALLOCATE WC DROP TABLE #results The same wrapper for spDeleteUpdate :
USE SUSDB DECLARE @var1 INT, @curitem INT, @totaltodelete INT DECLARE @msg nvarchar(200) CREATE TABLE #results (Col1 INT) INSERT INTO #results(Col1) EXEC spGetObsoleteUpdatesToCleanup SET @totaltodelete = (SELECT COUNT(*) FROM #results) SELECT @curitem=1 DECLARE WC Cursor FOR SELECT Col1 FROM #results OPEN WC FETCH NEXT FROM WC INTO @var1 WHILE (@@FETCH_STATUS > -1) BEGIN SET @msg = cast(@curitem as varchar(5)) + '/' + cast(@totaltodelete as varchar(5)) + ': Deleting ' + CONVERT(varchar(10), @var1) + ' ' + cast(getdate() as varchar(30)) RAISERROR(@msg,0,1) WITH NOWAIT EXEC spDeleteUpdate @localUpdateID=@var1 SET @curitem = @curitem +1 FETCH NEXT FROM WC INTO @var1 END CLOSE WC DEALLOCATE WC DROP TABLE #results During the work of "wrappers" customers stop receiving updates. You can at any time interrupt the execution of the script without losing progress. In order to continue the process, do not forget to delete the temporary table:
DROP TABLE #results In the cleaning wizard of 5 commands, we have completed 4 of them. The "Delete computers not contacting server" command should be executed from the wizard.
Reindex database
To re-index the database, use the following script :
Reindex script
/****************************************************************************** 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 TempDB Setup
Microsoft recommends :
If the number of data processors is less than or equal to 8, it is If the number of the processors is greater than 8, it can be used as the number of logical processors. changes to the workload / code.
This recommendation should not be ignored. I stop at four files. The initial file size should be the same. After that, expectations usually disappear.
Thanks for attention. You know some more chips - share!
Source: https://habr.com/ru/post/329440/
All Articles