My suggestion is that the Server Cleanup Wizard should be run at least once a month, and the soonest after Patch Tuesday the best (like Wednesday morning is good). If this process hasn't been run in a few months, then depending on how many updates and revisions are in the catalog, and how many computer groups are defined on the server, and how many actual Approval events were recorded against each of those updates and revisions - it's going to take some time.
THEN, it has to repeat all of that for any update *revisions* in the catalog, but filtering for only those that are not the current revision, join that list of revisions with every compuer group defined, retrieve the Approval Date for the update for each group, determine if the Approval Dates are all older than 30 days, and then DELETE that revision from the database. In order to evaluate that criteria, the query has to filter the entire catalog of updates for only those that are expired, join that list of updates with every computer group defined, retrieve the Approval Date for the update for each group (because there might be different approval dates - like between TEST and PRODUCTION groups), determine if each Approval Date is older than 30 days, and then DELETE that update from the database (which will then trigger all of the necessary index modifications that happen anytime you delete a row from an indexed table). The particular step of the SCW that causes the hang is the option " Unused updates and update revisions", and this delete decision is based upon updates that are expired and have not been approved for at least 30 days, and update revisions (other than the latest) that have not been approved for at least 30 days. > why it appears to be so inefficient I've got not idea It's not inefficient - it just takes that long to scan that much data if the SCW has not been run in a while. My WSUS server is running beautifully now. I had to do this for three separate queries, modifying three separate stored procedures.īy the time I was done, the WSUS reset process took about 10 minutes from start to finish on a 6GB database with 100GB of update files!Īfter the reset completed, I removed my indexes, columns, and put the stored procedures back the way they were. For example, I created a RowID field in the tbDeployment table and updated it to match the tbRevision table, created an index on it, then modified the stored procedure To counteract this I reduced the level of normalization between the tables. There with very little disk byte throughput. The problem lies when the query is run hundreds of thousands, if not millions of times, which causes the CPU to run up to 100% and sit Each time the query is run it only consumes about 16ms of CPU time. Actually, they are as optimized as they can be, considering they are joining across three tables WHERE tbRevision.RowID = AND tbDeployment.ActionID = 0ĪND tbDeployment.DeploymentStatus = 1 AND tbDeployment.UpdateType NOT IN ('Category', 'Detectoid')Īfter much troubleshooting with the SQL profiler, I was able to pinpoint three poorly performing queries (including the one above in my previous post). INNER JOIN tbRevision ON tbRevision.RevisionID = tbDeployment.RevisionID UPDATE tbDeployment SET DeploymentStatus = 0 FROM tbDeployment Help WSUS to run through a reset in a few minutes instead of an entire week: If we can find the answer to this, it will I'm looking to see if I can't find an index that will make this run at the intended speed. So I pulled up the worst performing queries, and found the following query keeps coming up as taking all the CPU. That usually indicates a poorly performing index. The sqlserver.exe process runs at 100% CPU with little to no bytes of disk movement. It appears that the problem stems from SQL. I started digging in to find why this is happening. That has been running for 5 days now without any indication However, cleanup doesn't delete anything. They have over 9000 files an 100GB at each site.
Running for eight days before they finished cleanup. Digging in to find out the real problem, now that's an answer! At this point, I have two WSUS servers at two locations that have been The answer "You have too many updates" is not an answer. This issue has been plaguing a lot of people.