HIgh CPU usage by SQLserver.exe

Question

HIgh CPU usage by SQLserver.exe

Dhruva_51

SSChampion

Points: 10521
More actions
March 25, 2013 at 1:49 pm

#291380

HI All,
We have moved one of our 2005 database to 2008 R2 windows server- with 16GB RAM and 4CPU's.
The database size is of 2.7GB.
I see that the CPU utilisation by this Single database is very high and reaching to 100% most of the times. Sometimes the website crashes and CPU drop down.
How can i trace and find the exact problem with that database.
I have done- Rebuild of indexes/Update stats, Queries are going for parallelism- so changed the MAx degree of parallelism to 2.
Can any one please suggest how can i trace which session is consuming more CPU. Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

First thing, set max degree of parallelism back to default or to the number of cores per NUMA node. Parallelism is a good thing. If queries are paralleling inappropriately, fix the queries. You probably want to increase Cost threshold for parallelism too.

Chapter 4: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dhruva_51 SSChampion Points: 10521 More actions · Answer 2

Hi Gila,

I have set cost threshold of parallelism to 15 based on subtree cost of the queries.

how can i find particular spid in sql server which is consuming more cpu?

PID in task manager is just showing sqlservice only.

Thanks

Andrew G SSChampion Points: 12809 More actions · Answer 3

You're best bet is using sp_whoisactive v11.11 by Adam Machanic.

http://sqlblog.com/blogs/adam_machanic/archive/tags/who+is+active/default.aspx

That will give you a quick view of the resources in use by each SPID

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Is this a newer physical server that you have SQL installed on?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

muthyala_51 (3/25/2013)
how can i find particular spid in sql server which is consuming more cpu?

Please read the book chapter I linked, it explains that and more.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 6

For the most part it looks like the application is the root cause for the issue. try running the below query to identify expensive queries and the then decide if you can modify the query or need to use other tools like resource governor etc to manage the workload.

Check to see if waiting task count is high also it is normal for the cpu to spike a bit after migration becuase of recompiles. However if your seeing the issue constantly then there could be a CPU bottle neck. I assume you have more proccesing power now than you did earlier.

-- Script 4

-- Top 3 CPU-sapping queries for which plans exist in the cache

SELECT TOP 3

total_worker_time ,

execution_count ,

total_worker_time / execution_count AS [Avg CPU Time] ,

CASE WHEN deqs.statement_start_offset = 0

AND deqs.statement_end_offset = -1

THEN '-- see objectText column--'

ELSE '-- query --' + CHAR(13) + CHAR(10)

+ SUBSTRING(execText.text, deqs.statement_start_offset / 2,

( ( CASE WHEN deqs.statement_end_offset = -1

THEN DATALENGTH(execText.text)

ELSE deqs.statement_end_offset

END ) - deqs.statement_start_offset ) / 2)

END AS queryText

FROM sys.dm_exec_query_stats deqs

CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText

ORDER BY deqs.total_worker_time DESC ;

-- Taken form DMV starter pack

Jayanth Kurup[/url]

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

1) did you update ALL statistics with a FULL SCAN?

2) I would simply use profiler to capture rpc batch completed and tsql batch completed events and find the big hitters. Tune these. I would also consider running a trace to disk and using Qure from DBSophic to do aggregate trace analysis. The thing(s) you need to tune most are not necessarily the ones that run the longest or use the most resources individually...

3) it is possible that someone altered the schema in any way - i.e. dropped some indexes?

4) I also see this routinely at clients these days: did you perchance upgrade to a much better IO subsystem? Getting data into the CPUs faster can cause them to actually start earning their keep. 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Along the lines of the upgrade - this sounds a lot like an issue caused by having the power save features enabled in BIOS. New boxes have it on by default. If this was an upgrade to a new box - that would be a high contender.

The schema change is also another excellent suggestion by Kevin. I had a client recently deploy some changes and a piece was missed. This caused the application to generate some terrible queries (yeah I know) that in turn ran very slowly until the missing piece was identified.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events