Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite
1

I am experiencing a problem with one server. It has 32 cores and seems to be well-configured in all aspects (memory, hard disk drives...). Performance Dashboard is not showing blocks or deadlocks and no waiting tasks. All queries seem to be performing well.

From time to time (every 5-6 minutes) one of the cores gets up to 100% utilization and the system's performance slows down. It lasts for some seconds.

What should I check to know what is causing this misbehaviour?

It is a SQL Server 2005 standard edition with Service Packs installed.

Thank you so much.

share|improve this question
Do you have a machine with 32 cores for SQL Server Standard? Don't remember about 2005, but 2008 R2 was limited to 4 processors (no limit in cores) and 2012 was limited to 16 cores. Are you using all cores for SQL Server or is there any other application server on that machine? – Marian Feb 12 at 13:39
Is there an increase in storage activity during those peak times as well? – Jon Seigel Feb 12 at 14:31
@Marian Yes, 32 cores, all dedicated to SQL Server. – Jaime Feb 13 at 7:23
@JonSeigel Do you refer to I/O activity? Disk activity seems to be ok and no contention has been detected. I suspected initially of tempdb, but is perfectly configured and running smoothly. I suppose some query is making a full table scan on a very high table, but performance dashboard is not complaining about any specific query. The system seems to be quite well dimensioned. I have even installed idera and the only warning I get is "Page Life Expectancy" under 300, which again leads us to page faults and bad performing queries. – Jaime Feb 13 at 7:27
Yes; I was trying to see if the peaks were correlated with database checkpoints. From your response, that doesn't sound like the case. Thanks. – Jon Seigel Feb 13 at 14:10

1 Answer

up vote 4 down vote

What process is using that CPU? You need to monitor the Process object and figure out which instance (ie.process) is driving CPU up. Also is important to distinguish between privileged (kernel) vs. user time. Also important to see if is not Interrupt Time by any chance.

The purpose of the exercise it o establish if is a hardware resource interrupt driving the CPU (I've seen this with some many network cards for instance), a user process (and which process). If you established that is SQL Server process then you can switch investigation to SQL Server specific means, at which moment it turns into SQL performance investigation well suited to Waits and Queues methodology. But if the problem is outside SQL then perhaps the best methodology to deploy is USE. And Kernrate is your friend.

share|improve this answer
Thank You. I will proceed as you say and will come back with answers to your questions. – Jaime Feb 12 at 13:22
We ha ve determined the following: When this CPU gets to 100%, the number of page faults per second also rises for SQL Server and, thus, for the whole system. This makes the System processor time to go up. So, it is the system which makes the CPU go to 100%, but it is because SQL is increasing its page faults. But, why? I don't know. SQL Server is using >64GB RAM Memory from a total of 96GB in the system. Memory use is limited for SQL to 88GB. It seems that there is no query causing this page faults as well. – Jaime Feb 12 at 15:07
If you're seeing page faults for SQL server, then something else is forcing SQL to page out memory. Try to identify what else would be using memory on the machine and/or decrease the memory allocated SQL, Also, you might consider setting lock pages in memory for sql server, though I wouldn't go that route until you figure out what else if using memory. – StrayCatDBA Feb 12 at 17:05
1  
How high is the faults/sec? Hundreds, thousands? Is it soft faults or hard faults? I assume is a 64 bit instance (at 88GB gotta be...). Any active antivirus? – Remus Rusanu Feb 12 at 17:49
@StrayCatDBA The server is dedicated to SQL Server and has more than 8GB available for the system. – Jaime Feb 13 at 7:31
show 10 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.