SQL Server 2008 100% CPU but not using a fraction of the memory it can

Suddenly our SQL server is using 100% CPU but only using a fraction of the memory it can use (16 GB available).

We're using web edition and allocated a maximum amount of ram.

Like i say this has just suddenly happened without us changing anything.

Need some ideas desperately as it's crippling us

Answers


Please do not be tricked by the memory usage shown in task manager - it cannot see what SQL server is really using. You want to be looking at:

SELECT * FROM sys.dm_os_sys_memory DOSM

in particular the system_memory_state_desc column will tell you if you have memory pressure.

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/sproc/function been changed (again without your knowledge)?

I would check these things before going further.


I'd read over this article and make sure you have done everything required. I know you probably think you have but double check just to be sure...


Need Your Help

Rotating landscape to portrait iPad does not change the scaling of a web page

javascript css ipad

Initially it's showing fine when watching the page in portrait mode and it's fine when changing orientation from portrait to landscape but while changing from landscape to portrait, the web page go...

Gimp vs Inkscape vs Fireworks for website development?

css templates web adobe inkscape

InkScape users, can you recommend Inkscape for website-template development. I have to learn a tool for website templating, to create layout and export slices, one of my friend is suggesting firewo...