100 % CPU Utilization by SQL server 2008 r2 express edition

Question

Dear all,

Can any one please help me to find the root cause of  the issue.
Our production server is running on sql server 2008 R2 express edition.( its running on Virtual machine Windows 2008 ).
Its not consuming  100 % CPU all the time, however  some times its reaches on 90% above  CPU utilization and stays there for 2 to 3 hours, this is happening quite frequently in a day.
Our community website is running over the sql server, i never noticed any performance issue while browsing the website during high CPU usage as well.

Regards,

Itishree

Answer 1

Hallo Itishree,

are you sure that cpu consumption is from sql server?
There is a pretty good example from Manvendra Singh who demonstrates the investigation steps:

http://www.mssqltips.com/sqlservertip/2454/how-to-find-out-how-much-cpu-a-sql-server-process-is-really-using/

---

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de
SQL Server Blog (german only)

Answer 2

check for blocking

run the query in ssms

select * from sys.sysprocesses where blocked <>0

---

Ramesh Babu Vavilla MCTS,MSBI

Answer 3

Hi Itishree,

I would suggest to go through this two-part MSDN video presentation. This gives good insight on High CPU issue-

http://blogs.msdn.com/b/sqlserverfaq/archive/2012/06/11/troubleshooting-sql-server-high-cpu-part-1.aspx and

http://blogs.msdn.com/b/sqlserverfaq/archive/2012/06/11/troubleshooting-sql-server-high-cpu-part-2.aspx

-Nishad

---

Thanks, Nishad

Answer 4

Thanks Uwe,

Yes i have already tried these above steps, all i found that simple queries, our staging server has same configuration, however sql is not consuming high cpu over there. So i am suspecting its due to any other issue.

Regards,

Itishree

Answer 5

Hi Itishree.s,

First, I want to inform that Cumulative Update package 5 for SQL Server 2008 R2 which fix High CPU usage when you run a SQL Server Agent job in SQL Server 2008 or SQL Server 2008 R2 if a column of a dynamic SELECT statement has a value that is many characters long. Additionally, if we run queries frequently which transfer a large count of data between client and the database, SQL Server will take up many CPU and memory resource.
>> all i found that simple queries
I want to confirm what are the queries, how much data count do they query, and how frequently do we need to run them.

Cumulative Update package 5 for SQL Server 2008 R2:
http://support.microsoft.com/default.aspx?scid=kb;en-US;2438347

 

---

Allen Li

TechNet Community Support

Answer 6

Follow the steps in http://mssqlwiki.com/2012/10/04/troubleshooting-sql-server-high-cpu-usage/ you will resolve it

---

Thank you,

Karthick P.K |My Site|My Scribbles |Blog Space |Twitter|My Facebook Group|

www.Mssqlwiki.com

Please click the Mark as answer button and vote as helpful if this reply solves your problem

Answer 7

Hello Karthick,

Thanks for your feedback, i followed very first step, confirmed that sql only uses above 90% cpu. Now which step i need to follow, please advise.

Record ID	Date	System CPU	SQL CPU
15389	11/20/12 12:29 AM	100	89
15388	11/20/12 12:28 AM	100	93
15387	11/20/12 12:27 AM	100	91
15386	11/20/12 12:26 AM	92	76
15385	11/20/12 12:25 AM	100	92
15384	11/20/12 12:24 AM	100	94
15383	11/20/12 12:23 AM	100	91
15382	11/20/12 12:22 AM	100	94
15381	11/20/12 12:21 AM	100	90
15380	11/20/12 12:20 AM	100	92
15379	11/20/12 12:19 AM	98	89
15378	11/20/12 12:18 AM	100	90
15377	11/20/12 12:17 AM	100	88
15376	11/20/12 12:16 AM	100	94
15375	11/20/12 12:15 AM	100	91
15374	11/20/12 12:14 AM	100	92
15373	11/20/12 12:13 AM	100	92
15372	11/20/12 12:12 AM	100	94
15371	11/20/12 12:11 AM	100	91
15370	11/20/12 12:10 AM	100	93

Regards,

Itishree

Answer 8

Yes it is SQL which is driving the CPU. Can you follow the remaining steps?.

Thanks

---

Thank you,

Karthick P.K |My Site|My Scribbles |Blog Space |Twitter|My Facebook Group|

www.Mssqlwiki.com

Please click the Mark as answer button and vote as helpful if this reply solves your problem

Answer 9

Hi Karthick,

Your next step is to get the queries which are  taking lot of cpu, when SQL is running on high. However when i saw the result i got 2 to 3 queries which are actually not consuming lot cpu. So what to do next? Its really a bottleneck situation now.

Regards,

Itishree

Answer 10

Hi, please refer to the steps on the following document to optimize SQL queries:

http://msdn.microsoft.com/en-us/library/ff650689.aspx

---

Allen Li
TechNet Community Support

Answer 11

Hi Karthick,

Your next step is to get the queries which are  taking lot of cpu, when SQL is running on high. However when i saw the result i got 2 to 3 queries which are actually not consuming lot cpu. So what to do next? Its really a bottleneck situation now.

Regards,

Itishree

Go further , If you are sure SQL Queries are not consuming CPU then identify if If the system thread is consuming most of the CPU. Under

2. If the system thread is consuming most of the CPU. below link

 http://mssqlwiki.com/2012/10/04/troubleshooting-sql-server-high-cpu-usage/

---

Thank you,

Karthick P.K |My Site|My Scribbles |Blog Space |Twitter|My Facebook Group|

www.Mssqlwiki.com

Please click the Mark as answer button and vote as helpful if this reply solves your problem