January 6, 2005 at 12:13 pm
I have a SQL2KSP3a database(<1GB) running on a 4x3GHz physical CPU with
4GB of ram. Windows Server 2003 with hyper-threading turn on.
There are ~420 .Net users/cxns (fat client, no web/app servers) with
connection pooling and ~1 trx/sec. The database growth is neglegeable
and actually is not even relevent which I will explain in a minute.
99% of the trxs are from one SP that does a select. The resultsets are
relatively small as well 1~100 rows. Yes I have tuned it with index
tuning wizard as well, changed the SQL memory configurations, changed MAXDOP, etc....
My problem is this...
The first day after a reboot, the server runs 6%CPU during peak hours.
During the non-peak hours until the next day something apparently
happens. The next day (2nd day after a reboot), it jumps to 40%CPU
during peak hours. The server will continue to run at 40%CPU during
peak hours until the next reboot. This phenomenon has been occurring
for 6 months or more and the traffic on the server is the same for day
1 as it is for day 2,3,4,... This database was on another server with
100+ dbs and exibited the same behavior, thus bringing that server to
its knees, and thus we had to move it to the server in question with no
other dbs.
I have googled my eyes out, Microsoft site, white papers, perfmon,
SQLDiag, PSSDiag, execution plans, index tuning wizard, and the list
goes on! I currently have a case open with Microsoft that has been
open for months now. I could give more information, but I would be here for days.
(I know that the subject line for this thread is rather odd, but I don't know what else to call it.)
Any ideas are greatly appreciated!
Thanks in advance!
JL
January 7, 2005 at 5:59 am
Hi Josep,h
Sounds like you have been having a pretty torrid time! One of our production servers was having a similar issue with CPU usage running high during normal usage for no apparent reason. ie no changes to sp's made indexing fine etc. We tracked down a couple of MS articles that helped us.
http://support.microsoft.com/default.aspx?scid=kb;en-us;835732
http://support.microsoft.com/kb/841382
This was Windows 2000 server so may not be applicable but it looks like you're try anything at the moment!
Good luck
January 7, 2005 at 6:28 am
Have you checked the withs Event Viewer logs? If not please do so. I spent several days fighting some guys over data access I was doing on a call switch where the CPU went high when my processes touched it. They went thru and changed several things (HD, CPU, Memory, Motherboard) when I finally asked them that question, and it turned out to be the Admin Card for the server. So please if you haven't checked the logs for something screaming do so first.
January 7, 2005 at 8:11 am
Thanks for the replies!
I will ask the Microsoft Tech about the MS04-011: Security Update for Microsoft Windows. One patch that I have applied is FIX: Intermittent query slowdowns and corresponding high CPU utilization. http://support.microsoft.com/default.aspx?scid=kb;en-us;835864#kb1 It was one of those that you had to call MS to get. The Tech said that it didn't exactly match my problem, and indeed it did not fix it.
No errors in the System, Application, or SQL logs.
Some more info that I posted elsewhere... Tempdb is not running out of space. I have actually set tempdb to 1GB with 10% auto-grow. No OpenXML. The connections correlate to the number of users logged in at any given time. The DROPCLEANBUFFERS & FREEPROCCAHCE was the first thing that Microsoft had me do. No luck. I have used profiler many times with this. I have even done a spid to thread correlation with not luck in finding any rogue processes. Microsoft has run the "Read 80" tool also; it apparently creates an aggregate report on the PSSDiag output.
Another piece to the puzzle is that I have just applied some indexes recommended by the index tuning wizard. I did some testing and found that indeed the indexes did help, but I have been holding off applying them to production as to not disturb my baseline. My thinking is that even if the indexes did help, it would not explain the problem at hand. Since I have applied the indexes, the CPU is now running at 12% consistently. It hasn't been rebooted since then, but SQL Server has been restarted. It doesn't appear that the new indexes have fixed the problem because theoretically with the indexes applied, it should run below the 6%.
Thanks again for the replies!
JL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply