Re: WSUS Server Extremely Slow
Lawrence Garvin (MVP)
clients?
(btw.. that's a LOT of hardware for a 2,000 user WSUS server!!!)
"John M. Polston" <JohnMP...@discussions.microsoft.com> wrote in message
news:93C15A27-30CE-4CD5...@microsoft.com...
> After this Tuesday's patches my WSUS server is running extremely slow and
> accessing the WSUS Admin. site to administer it has been hit and miss with
> more misses. The SQL Database is on it's own seperate RAID 5 array with
> 26GB
> of free space and it seems as if the drive is constantly thrashing however
> I
> have stopped the DB and defragged the drive and it didn't help. We are
> running around 2000 clients, the server is a 4-way CPU box with 4GB of RAM
> and it's only purpose is WSUS. The current memory usage floats between
> 1.6GB
> and 2.2GB. I have checked the Process usage and nothing really is running
> away with it. Has anyone else noticed this similar issue?
>
> Thanks, John Polston
John M. Polston
and it's never given me any problems.
The main thing I am seeing is that SQL 2000 with SP4 I have loaded on the
system and using is just bogging down bad. The Admin interface cannot hardly
load. The main page loads about 50% of the time but when I goto the Updates
page the SQL query just times out and I get the Error dialog with multiple
SQL entries. I have Stopped SQL and defragged the array the DB is on and I
have also ran the SQL Integrity Checks and Optimizations within the SQL
Enterprise Console with no improvement in speed. The server is an old HP
LXr8000 with 4-Xeon 550MHz processors which may be limiting if it was a
single or dual processor but SQL is utilizing all 4 processors. The SQL
(SUSDB) database is around 2.9GB in size with about a 200MB log file. I am
not sure what these numbers are supposed to be. The RAID controller I am
running is a HP NetRAID 3si running to an external array which stores the
WSUS Data files and the SQL Database. I have been working on this for two
days straight with no improvement. I really don't want to have to rebuild
the server but this may be my only choice, however I don't want to have to do
this one year down the road when it starts happening again. So that is why I
would like to find out what is wrong now and resolve it. Any help would be
appreciated.
Thanks,
JP
John
You should really change your detection interval back to the default of
22 hours. There really is no benefit to having it at 2 hours except a
fast growing tblEventInstance table that can cause a 'laggy' feeling at
the admin console.
If you look back at some of Lawrence's previous posts, he explains why
this is.
If you change your detection frequency, then truncate tblEventInstance,
that should speed up your admin console.
John M. Polston
What is the detailed process to truncate the "tblEventInstance"? I am no
SQL expert so any help you can give on this would really be appreciated.
Thanks,
JP
John
You have to use Query Analyzer (available on the SQL install CD) to
access the SUSDB database. Then in the SQL pane, type 'TRUNCATE TABLE
tbEventInstance' and press F5.
Think about your detection frequency and how often you approve patches.
Usually, I approve patches once a month (when they usually come out).
I have critical and security updates being approved automatically to
help with compliance with our college-wide security program.
So, at least for me, I don't see a good reason to use more bandwidth and
grow my DB fast just to catch updates a little sooner.
John
> John, this totally fixed my problem. After running the truncation, I
> rebooted and everything came back to life. I will be modifying my GPO
> settings for detection frequency to 22 hours as suggested. Wish I had know
> about this 3 days ago. Is there any reason I couldn't set this up as a job
> to run every month or so? If so, what data is getting deleted, i.e. is it
> data that would be normally found on a report? I am running a daily report
> for my upper management that dumps it out to a CSV file and automatically
> e-mails it but wasn't sure if this procedure would cause issues with that
> data.
>
> Thanks again for your help on this. It was life saver and a weekend saver
> if you catch my drift.
>
> JP
Glad I could help.
The database will truncate that table every so often (Lawrence can jump
in with the exact numbers, I can't recall off hand) on its own.
You aren't losing and real reportable data, just information on the
reporting session. Take a look at the result of this query and you'll
see exactly what's in that table:
'SELECT * FROM tbEventInstance'
John M. Polston
take it that the Update Services Service takes care of this truncation? If
so it would be nice if this was a modifiable parameter because after doing my
manual truncation today the server seems faster than it ever has, especially
approving patches. I also did a Decline on a couple of patches and it
responded very quickly to this and I have had many problems in Declining
exired updates where their status will not change once I hit the Decline
button, the server would just seem to stall.
JP
Lawrence Garvin
regular basis.
The default pruning algorithm is to purge the oldest 1,000 events every 12
hours.
This is where your 2000 systems performing detections every 2 hours
contributed to the problem. You were generating a minimum of 24,000 events
every 12 hours, but the system was only removing 1,000, a net increase of
23,000 events every 12 hours. At that rate, it only takes about three weeks
to grow that table in excess of one million rows.
This is the reason why the max supported client load for a single server is
15,000 clients (the performance limitations of a dual-server environment are
much higher). At that load, the clients generate about 30,000 events a day,
and regular maintenance would be required in that circumstance.
Luckily, that scenario is not that common, as there are very few
organization that can actually put 15,000 clients on a single server pair,
as network bandwidth becomes the limiting factor long before client load on
the server does, and so, in reality, larger organizations have multiple
servers servicing a few thousand (or much less) clients each.
At the default detection interval of 22 hours, with 2000 clients, you'll
generate about 4000 (perhaps a bit more depending on system
powerup/powerdown cycles) events a day, of which 2000 will be purged. At
2000 net events added per day, it'll take about a year and a half to clog
that table up.
On a Win2000/MSDE system with 500 clients, the table will essentially remain
empty, in perpetuity, because the table is purged almost at the same rate
that the events are created.
"John M. Polston" <JohnMP...@discussions.microsoft.com> wrote in message
news:DF44A7C7-7F04-45F0...@microsoft.com...
John M. Polston
1) So even at 22hour detection interval, the DB would become sluggish after
1.5 years?
2) Is there a Optimization string I can add to the regular SQL Optimiztion
job that I have running every Sunday afternoon?
3) Would it just be ok to run the truncate every couple of months by hand?
JP
Lawrence Garvin
> Thanks for the detailed explanation Lawrence. A couple of questions.
> 1) So even at 22hour detection interval, the DB would become sluggish
> after
> 1.5 years?
Yes.... given current programmed maintenance activity.
> 2) Is there a Optimization string I can add to the regular SQL
> Optimiztion
> job that I have running every Sunday afternoon?
Not that I'm aware of.
> 3) Would it just be ok to run the truncate every couple of months by
> hand?
It would be unnecessary, but I'm not aware of any significant harm that
would be caused by doing so.
Note: There is an awareness about the complications coming from this
situation. I suspect when the original purge algorithms were written, the
WSUS team was thinking in terms of WSUS being deployed primarily in small to
mid-size organizations (or sites) -- which, truly, -is- the target market
for WSUS. So the maintenance algorithms are appropriately sized for a <1000
PC organization.
The fact that today's hardware is so much more powerful (than even in
2003/2004 when the design specs for WSUS were being written -- note that the
min req for WSUS is a P3/750 (that's a 100Mhz bus) with a 512MB RAM. Today's
entry level machines are 4x to 5x more powerful than the min spec system. --
makes it quite feasible to put a few thousand clients on an entry level box,
and several thousand clients on a front-end/back-end server pair. The
maintenance algorithms just need to be modified, and I suspect that will
happen in the service pack.
Torgeir Bakken (MVP)
Microsoft have created a Knowledge base article about this
tbEventInstance table issue:
http://support.microsoft.com/kb/909131
If you read the article, you will also see that Microsoft also have
created a hotfix for this issue (you will need to call MS PSS to
obtain it).
If I am not mistaken, SP1 for WSUS will handle this better...
John M. Polston wrote:
> Thanks John, Yes it would be nice if Lawrence could answer this question. I
> take it that the Update Services Service takes care of this truncation? If
> so it would be nice if this was a modifiable parameter because after doing my
> manual truncation today the server seems faster than it ever has, especially
> approving patches. I also did a Decline on a couple of patches and it
> responded very quickly to this and I have had many problems in Declining
> exired updates where their status will not change once I hit the Decline
> button, the server would just seem to stall.
>
--
torgeir, Microsoft MVP Scripting, Porsgrunn Norway
Administration scripting examples and an ONLINE version of
the 1328 page Scripting Guide:
http://www.microsoft.com/technet/scriptcenter/default.mspx
Lawrence Garvin (MVP)
and for such a simple issue with such a simple resolution... <sigh>
"Torgeir Bakken (MVP)" <Torgeir.B...@hydro.com> wrote in message
news:ewkaRYhN...@TK2MSFTNGP14.phx.gbl...
John M. Polston
I called and got the Hotfix e-mailed to me and I installed it. I also added
the truncate to my automated SQL Optimization job that runs every week on
Sunday. I tested and it ran fine as well. So I guess I am covered on both
sides until SP1 comes out. I also set my GPO detection interval back to 22
hours for my PC's but left my Servers at 6 hours. BTW, I had my PC's at 6
hour intervals rather than the original 2 hours I stated.
JP