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

Question

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

Answer 1

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...

Answer 2

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.