May 11, 2007 at 8:57 am
OK, this is an odd one. Hoping to get some new ideas!
Our company Intranet site has its content in a SQL Server database. Lately people have been complaining that the site takes a while to load occasionally. Sure enough, approximately 10% of the time I launch the site in IE, it will take >60 seconds to load, the other 90% of the time it takes <1 second. Sometimes it happens more frequently than others, but in general it's about 1 in 10.
Looking at the SQL Server, when the problem occurs, the sqlsrvr process pegs processor utilization at 100% while the site is loading. As soon as the page finishes loading, the CPU goes back to idle. Looking at other performance counters (pages/sec, page faults/sec, disk queue length, disk sec/transfer) there's nothing suspicious. Aside from 100% CPU utilization the server is practically idle. I ran a profiler trace and caught it while performance was good and when performance was bad. In both cases, the same activity was occurring, in the same order. Each query has the same CPU, read and write cost whether performance is good or bad. The only difference is that the duration of each query is longer and the queries are more spread out, meaning that when performance is good, there was just a few ms between queries, when performance was bad, it was a second or more between queries.
So it seems that something else, perhaps some system process, is tying up the CPU. From perfmon, I've identified that Thread-0 for the sqlsrvr process is the one doing the hogging. Generally, lower-numbered threads/processes/spids tend to be system related, but I'm not sure in this case.
Other info:
SQL 2000 standard SP4
Windows 2000 standard
2GB RAM
dual-processor
A strange thing about the processors though. There used to be two instances running on this server, and a previous contractor had restricted each instance to using a single CPU. I just realized this yesterday (I'm new here), and since the other instance is no longer running, I've re-enabled use of the other CPU, but I won't be able to restart the service for it to take effect until this weekend. If nothing else, if it is some system process, perhaps having the other CPU for user requests will help things.
Thanks in advance for your ideas!
May 15, 2007 at 7:40 am
No one replied, but in case anyone read this and was wondering how it was going...
Well, we rebooted the system over the weekend, so SQL is now using both processors. I watched it all day yesterday and the problem never recurred. Utilization never got over 30% per CPU. Whether it just needed to be bounced or if the switch to making both CPUs available fixed things, I don't know. But it seems OK now.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply