Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sandesh.ravi
Posting Yak Master
110 Posts |
 Posted - 2011-10-19 : 08:48:39
|
| Suddenly the server became slow and sql server had takem 100% CPU utilization and 8GB of memory.What could be the ideal reason?Thanks,Sandesh |
|
|
chris_cs
Posting Yak Master
223 Posts |
Posted - 2011-10-19 : 08:57:19
|
| You need to check if anything is running in SQL. You can check Activity monitor, sp_who, sp_who2 etcAlso are any jobs running?Is the CPU at 100% pretty much constantly or intermitantly?----------------------------Junior DBA learning the ropes |
 |
|
|
sandesh.ravi
Posting Yak Master
110 Posts |
Posted - 2011-10-19 : 09:03:05
|
| It constant 100%. no jobs are running.Thanks,Sandesh |
|
|
|
Cindyaz
Yak Posting Veteran
73 Posts |
Posted - 2011-10-19 : 10:17:16
|
| 1. check in task manager what is consuming CPU. Is there any program apart from SQL Server that is running?2. If you find SQL Server is utilizing the entire CPU, you will need to find out what processes are running within SQL Server.select * from sys.dm_exec_Requests where session_id>50 will give you list of processes that are currently executing.query sys.dm_exec_waiting_tasks to find out which all processes are waiting on which resource. |
|
|
|
sandesh.ravi
Posting Yak Master
110 Posts |
Posted - 2011-10-19 : 10:37:15
|
| The memory utilization is increasing. it was 8GB but now its 10GB. How to free up that?Thanks,Sandesh |
|
|
|
NifflerX
Starting Member
29 Posts |
Posted - 2011-10-21 : 12:24:29
|
| You will probably have to stop your SQL services to free up the memory, but before you do that, check on the max server memory. See this link for details: http://sqlserverperformance.wordpress.com/2008/08/06/suggested-max-memory-settings-for-sql-server-20052008-2/ [If that link isn't clear Google SQL Server max memory settings]Make sure that you have set a limit on the memory that SQL can use, and make sure it's less than what your server has. For example, if your server has 12 GB of RAM, don't allow SQL server to take more than 8-10 GB. It's possible that the reason your CPU is running at 100% is that there is no memory availble for the OS to do it's job. HTH.-NifflerX |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-10-21 : 14:29:57
|
| Optimise your queries, tune your indexes. Poorly performing SQL is the main cause of high CPU.[url]http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/[/url][url]http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/[/url]--Gail ShawSQL Server MVP |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-10-21 : 15:24:22
|
quote:
Originally posted by sandesh.ravi
The memory utilization is increasing. it was 8GB but now its 10GB. How to free up that?Thanks,Sandesh
What kind of system are you on ? 64 or 32 bit and what are the memory are settings.PBUH |
|
|
|
|
|
|
|
|
100% CPU Usage for sql server