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
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:
- Has an index been dropped (without your knowledge)?
- Do you have indexes at all?
- Have you recently seen higher usage of the system (more users/more data)?
- Has the system recently been restarted (thus emptying cache and causing re-compiles for queries)?
- 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...