4

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.

5
  • 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, 2013 at 13:39
  • Is there an increase in storage activity during those peak times as well?
    – Jon Seigel
    Feb 12, 2013 at 14:31
  • @Marian Yes, 32 cores, all dedicated to SQL Server.
    – Jaime
    Feb 13, 2013 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, 2013 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, 2013 at 14:10

1 Answer 1

4

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.

15
  • Thank You. I will proceed as you say and will come back with answers to your questions.
    – Jaime
    Feb 12, 2013 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, 2013 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. Feb 12, 2013 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? Feb 12, 2013 at 17:49
  • @StrayCatDBA The server is dedicated to SQL Server and has more than 8GB available for the system.
    – Jaime
    Feb 13, 2013 at 7:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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