Seems to have stabilized some. I'm going to have a close look at the indexes on the message table this weekend. I think we've got it over-indexed.
Fortunately, I always have the Profiler running and have an html page for looking at the output, sorted in order of most expensive, and though nothing stood out as really clobbering the system, the routine that fetches messages for the MailBox was showing up more frequently than I'm used to seeing it. Looked at the execution plan for it (which only helps up to a point) and decided to reindex one particular index and it seems to have helped.
There are still some remaining inherited structural problems with the db, but the worst of them were taken care of about a month ago. I still need to address the rest, though, and it looks like we're at a point where it'd be helpful to make a full reindex a weekly event.
Looks like it's back to normal now. The scary thing is that "normal" is 25-50% utilization during market hours.
It's a Dell PE4600 with a pair of Xeon 2.8's in it. I need to check to see if it can run the new 3.8's. If so, that and our planned upgrade to Windoze 2003 and SQL Server 2005 should help get a lot more headroom on that machine. And moving full-text indexing to a separate disk subsystem since FT doesn't particularly like RAID 5 or sharing with an already busy drive.
Edit: I'm always reluctant to do any dbreindexes during market hours because it usually swamps the server for a while, but it looked like a necessity this time and looks like it was worth it. Took about 2 minutes to reindex the one index. The last time I shut off IIS (so SQL could devote its full attention), a dbreindex on the message table took 25 minutes and 23 seconds.
And that's just one of many tables. Though it's by far the largest and most heavily-indexed.
AI
