January 15, 2008 at 5:17 pm
Last Thursday our System expereinced slowness that it had not experience before. No code changed. The CPU on a five minute average peaked over 95%. We were running SQL Server 2000 SP3a on a 8 Processor Unix Server. It had been that way for a long time over a year. The problem continues. We installed SP4 since there was a fix in the SP that sounded like this problem and to gain Micorsoft Support. Does any one have any ideas? This is an 6-day incident that dos not seem to have a solution. I know that's a pretty sketching description. But maybe question would help the thinking. Of course everytime we ask the answer is nothing changed.
January 15, 2008 at 8:25 pm
Did you check your disk and see whether or not they are healthy?
January 15, 2008 at 9:14 pm
Yes, but do you mean anything specific?
January 16, 2008 at 1:15 am
Any idea what was running on the server at the time? Number of connections?
I would suggest next time it happens, run perfmon and watch the following counters:
pages/sec (memory)
transfers/sec (physical disk)
sec/read (physical disk)
buffer cache hit ratio (SQL buffer manager)
transactions/sec (SQL transactions)
Also Profiler and look for the queries taking the most CPU.
Lastly, check sysprocesses and see what's running, doing what and taking what CPU.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 16, 2008 at 6:23 am
All these things have been checked. Thanks for the suggestions. Please keep 'em coming. Working with MS now and gathering their PSSDIAG traces throughout the day.
January 16, 2008 at 6:28 am
What did the perfmon counters show?
Did profiler pick up any queries with unusually high CPU values?
What's your server spec? 64bit? How much memory?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 16, 2008 at 6:36 am
Yes there have been SPs picked up in the trace as the heavy CPU users. We have added indexes and done some refactoring but these seem like incremental changes that done affect it much.
We are running Enterprise Editin on Windows 2003 SP 1. SQL Server 2000 SP4. There are 8 Processors and 16 GB RAM.
The perfmon counters around memory seem normal indicating not much paging.
January 16, 2008 at 6:58 am
32 bit? 64 bit?
What about the other perfmon counters? Are you seeing higher than normal disk usage or disk times? More transactions/sec than normal? More logins/sec than normal?
Also look at locks/sec and latches/sec
If you check sysprocesses during the slow time, are the queries running? Waiting?
If you run select * from sys.dm_os_wait_stats which of the wait stats appear highest? Do any have excessivly high signal wait time? (signal time = time between process been told it can run again and it getting a spot on a CPU)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 17, 2008 at 7:41 am
Hi
Two thoughts.
1 - select from sys.dm_os_wait_stats - may indicate where you are waiting for resources? Dont forget you may need to clear the counts -
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR); (see BOL)
2 - if you run profiler (not on the production server!) you may be able to get a handle on the worst performing sql, any recompilations etc.
good luck
January 17, 2008 at 9:39 am
We had a similar problem with one of our Java applications that was hitting the server. Are you seeing an abnormally high number of connections opening up? I had to reconfigure the maximum degree of parallelism and that fixed it. If it had been running fine before and there was no changes to any application code this might not be it.
Good luck,
Phoenix DBA
January 17, 2008 at 9:45 am
Well, this is interesting, we have reduced the Processors inolved in Parallelism from 4 to 2. The server has 8 processors. Is that similar to the configuration change you mead,
January 17, 2008 at 10:06 am
Actually I set it to 1. I normally only have about a 100 users logged in on this server during the day and my transactions per second hardly go above 50 as this is an internal departmental application. We had a process that was opening up a large number of threads and that seemed to cause CXPACKET errors if I remember correclty so I set this inline sql code coming from the app (bad, I know) to run on one processor. As we are planning to open this application up to multiple departments I may have to revisit this issue but hopefullly not. It was pain.
I hope this helps.
Ross
January 17, 2008 at 10:18 am
I have more users than that I think it is 300 or so and spread across three sites across the country with the servers in the East. We think parallelism helps us. We were seeing the CXPACKET errors as well, but after SP4 upgrade we do not. But Microsoft recommended changing it, so at 2 we get some parallelism but less CXPACKET waits.
PS we are having our first good day in a week. I am updating Statistics on Microsoft’s recommendation with FULLSCAN and that seems to be helping.
Does anyone have any "best practices" on updating stats? I have auto create auto update on. This update has been running for almost 12 hours. Very big DB 160 GB.
January 17, 2008 at 10:57 pm
I used to look after a moderately busy server (50-100 trans/sec most of the day) that had issues after moving to SP4. We did some experiments with MAXDOP, and found for the online stuff during the day 1 was best - i.e. disable parallel queries. For the overnight batch updates, 2 was best. Anything more than 2 and two threads working on the same query would deadlock each other!
In the end we left it at 1. Disabling parallel queries will mean queries take slightly longer to run, but the server can run more queries at once. in our experience with parallel queries the server spent a lot of time waiting on syncronisation anyway, the speedup was only marginal in most cases.
We found it best to enable parallel queries for OLAP systems, disable it for OLTP systems.
January 18, 2008 at 2:55 am
Hi
we are running sqlserver2000 standard edition with service pack 4.
we are encountering the cpu 100% more than 5 times a day.
we ahve checked thru perfmon.
pls look at the following counters
buffer cache hit ration-- 93 to 98
transactions/sec--4
active transactions- 50 to 60
user connections -- 50 to 60
how to scrutinise the performance of the db and also to control the cpu utilisation .
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply