SQL Server 100 CPU Utilization - One database shows high CPU usage than others

Question

We have an SQL server with about 40 different  about 1-5GB each  databases  The server is  an 8 core 2 3G CPU with 32Gigs of RAM  27Gig is pinned to SQL Server  The CPU utliziation is mostly close to 100  always and memory consumption is about 95   The problem here is the CPU which is constantly close to 100  and trying to understand the reason   I have run an initial check to see which database contributes to high CPU by using - this script but I could not substantiate in detail on whats really consuming CPU  The top query  from all DBs  only takes about 4 seconds to complete  IO is also not a bottleneck    Would Memory be the culprit here  I have checked the memory split and the OBJECT CACHE occupies about 80  of memory allocated  27G  to SQL Server  I hope that is normal provided there are lot of SPs involved  Running profiler  I do see lot of recompiles  but mostly are due to  temp table changed    deferred compile  etc and am not clear if these recompiles are a result of plans getting thrown out of cache due to memory pressure  Appreciate any thoughts

User · Accepted Answer

You can see some reports in SSMS  Right-click the instance name   reports   standard   top sessions You can see top CPU consuming sessions   This may shed some light on what SQL processes are using resources   There are a few other CPU related reports if you look around   I was going to point to some more DMVs but if you ve looked into that already I ll skip it  You can use sp BlitzCache to find the top CPU consuming queries   You can also sort by IO and other things as well   This is using DMV info which accumulates between restarts  This article looks promising  Some stackoverflow goodness from Mr  Ozar  edit  A little more advice    A query running for  only  5 seconds can be a problem   It could be using all your cores and really running 8 cores times 5 seconds - 40 seconds of  virtual  time   I like to use some DMVs to see how many executions have happened for that code to see what that 5 seconds adds up to

User · Answer

According to this article on sqlserverstudymaterial   Remember that  quot  Privileged time quot  is not based on 100  It is based on number of processors If you see 200 for sqlserver exe and the system has 8 CPU then CPU consumed by sqlserver exe is 200 out of 800  only 25    If  quot   Privileged Time quot  value is more than 30  then it s generally caused by faulty drivers or anti-virus software  In such situations make sure the BIOS and filter drives are up to date and then try disabling the anti-virus software temporarily to see the change  If  quot   User Time quot  is high then there is something consuming of SQL Server  There are several known patterns which can be caused high CPU for processes running in SQL Server including

[sql-server] SQL Server 100% CPU Utilization - One database shows high CPU usage than others

Examples related to sql-server

Examples related to sql-server-2005

Examples related to cpu-usage

Examples related to sql-server-performance