CPU Usage 100%

Question

CPU Usage 100%

jayoub

SSCertifiable

Points: 6991
More actions
June 26, 2016 at 6:01 pm

#314669

I sometimes find one of my SQL server CPU usage pegged at 100% and I don’t know what to do to stop it. When I use Perfmon or task manager I can see that SQL server is using all of the CPU, but what in SQL Server is causing this.
It’s a Windows Server 2008 with SQL Server 2008R2 all x64
What is the best thing to do when this happens?
What views or tables will tell me what processes are causing the High CPU usage?
Can I kill something to bring it back down?
I used the sys.processes view and I can see what database and what process is using the most CPU, but not sure what to do. Lately I have been leaving it alone until finally CPU usage goes back down and the team lead has restarted the instance to get CPU usage back to normal.
We suspect a COTS application is causing this issue, so we call the techs that run that software. They tell us "Yes we ran something, but it has been done for hours now"
Any direction would be helpful.
Jeff

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 1

jayoub (6/26/2016)
I sometimes find one of my SQL server CPU usage pegged at 100% and I don’t know what to do to stop it. When I use Perfmon or task manager I can see that SQL server is using all of the CPU, but what in SQL Server is causing this.
It’s a Windows Server 2008 with SQL Server 2008R2 all x64
What is the best thing to do when this happens?
What views or tables will tell me what processes are causing the High CPU usage?
Can I kill something to bring it back down?
I used the sys.processes view and I can see what database and what process is using the most CPU, but not sure what to do. Lately I have been leaving it alone until finally CPU usage goes back down and the team lead has restarted the instance to get CPU usage back to normal.
We suspect a COTS application is causing this issue, so we call the techs that run that software. They tell us "Yes we ran something, but it has been done for hours now"
Any direction would be helpful.

Just a quick thought. You might be able to quickly find out next time you see the 100% usage. As soon as it stops, open the Object Explorer in SSMS (press the {f8} key to get there if not already open), right click on the instance, select {Reports}, {Standard Reports}, {Performance - Top Queries by Average CPU}. It might show up there unless you have something worse over a period of time.

You can also lookup Adam Machanic's "sp_WhoIsActive". I've not personally used it (I wrote my own a while back) but it would be a big help in finding the culprit while it's in action.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

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

jayoub SSCertifiable Points: 6991 More actions · Answer 3

I looked at the simple talk article by Gail Shaw and it was great. I actually set it up on the test environment and will eventually on production..

I do have one question about this process.

Will the trace include queries that are already running or just queries that happen after to have started the trace?

My reasoning is this

If I have a query that is causing high CPU usage and I go ahead and start the trace. Will the trace show the troubling query or will is only show things that have begun after the trace has started.

please let me know when you have a chance.

My feeling is that I will show all active queries because I did notice the stuff I had working in SSMS in the trace when I opened it.

Your help is appreciated .

Jeff

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 4

jayoub (7/4/2016)
I looked at the simple talk article by Gail Shaw and it was great. I actually set it up on the test environment and will eventually on production..
I do have one question about this process.
Will the trace include queries that are already running or just queries that happen after to have started the trace?
My reasoning is this
If I have a query that is causing high CPU usage and I go ahead and start the trace. Will the trace show the troubling query or will is only show things that have begun after the trace has started.
please let me know when you have a chance.
My feeling is that I will show all active queries because I did notice the stuff I had working in SSMS in the trace when I opened it.
Your help is appreciated .

I recommend you do an experiment BEFORE the next episode. It's not so hard. You know the job schedule. Pick something that's long running and, after it starts, do your test and see if it shows up. The key here is that if you're going to fight a fire, you need to practice fighting fires. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jayoub SSCertifiable Points: 6991 More actions · Answer 5

Good Idea. Thanks for the reply

I will give it a try.

Jeff