7

We are having a SQL Server R2 Web Edition database, based on Windows Server 2008 R2 with a strong CPU and 32GB RAM DDR3.

Our database works very hard (we run around 600,000 queries in 1 minute) and its CPU is going crazy (25% to 90%).

I was looking after slow queries, found some, fixed them, some other I just canceled to check if it will help, but it didnt, CPU is still very high.

I was wondering what else could it be? I am not a DBA, but I am working with a very experiense one, he create all the indexes and re-configured the DB, but CPU is still very high, and this DBA (with more than 20 year of experiense) is kind of hopeless and dont really know how to find the problem.

I was wondering, could it be caching problem? like that queries are not cached?

Anyone know what can it be? what could we do? how can we investigate it?

Maybe its just the high amount of queries?

I'd love to get every small piece of information you can give!

| improve this question | |
  • 6
    Why is high CPU unusual for a server taking 10,000 queries per second? – usr Jun 14 '12 at 13:57
  • I have no idea, is it usual? I am not a DBA, as I said. if its the amount of queries, if I'll upgrade our DB, it might help? to SQL Standard or Enterprise – m0fo Jun 14 '12 at 14:04
  • I will advice you guys to check the IO during transaction first before going further. – user9646 Jun 25 '12 at 18:55
8

I would recommend starting with a methodology like Waits and Queues. But at 10k queries per second you should expect some CPU to burn. Whether it's more than it should be is really hard to tell just from the info you gave. The methodology linked will at least help you find bottlenecks.

The biggest bang for the buck would probably be... not to send 10k queries a second. Cache in the client. Look at How StackOverflow Scales with SQL Server and see how SE caches as much as possible in the web tier layer before hitting the server.

Just in case, do a couple of DBCC SQLPERF ('SPINLOCKSTATS') and see if anything stands out.

| improve this answer | |
2

High CPU usage could be one of a few other problems:

  1. Has an index been dropped (without your knowledge)?
  2. Do you have indexes at all?
  3. Have you recently seen higher usage of the system (more users/more data)?
  4. Has the system recently been restarted (thus emptying cache and causing re-compiles for queries)?
  5. Has a query/stored procedure/function been changed (again without your knowledge)?

I would check these things before going further.

| improve this answer | |
0

http://www.petri.co.il/pagefile_optimization.htm

This will surly help you. This page shows how to utilize virtual memory and Page file optimaization.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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