Find Current CPU Usage- Find the SQl using most of CPU

Question

Find Current CPU Usage- Find the SQl using most of CPU

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
July 22, 2010 at 9:53 am

#148265

I have a very simple question. I need to find the current SQL statement which is using most of the CPU. I have come across many scripts but none of them suits my the requirements. Here is my situation, i see CPU spike..usage is 100%. I can run sp_who2 but that would give me CPU usage accumulated so far by a process. I would appreciate if someone can just give me a script which can tell me which process is currently using most of the CPU, also please give some tips based on the script to analyse. Thanks !!

Viewing 15 posts - 1 through 15 (of 16 total)

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

Dan.Humphries SSChampion Points: 12125 More actions · Answer 1

I would highly recommnd installing the performance dashboard from Microsoft if you have not already. you could easily see all the information you need from there.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 2

i do have dash board reports.....i am looking at report for avg cpu...but i need some suggestion on how to analyze because its pulling the data from yesterday also...if you were given this task how would you do it? please advice

Adiga One Orange Chip Points: 27284 More actions · Answer 3

You can also use Process Explorer to track which SPID is consuming CPU. I have explained it here[/url]

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 4

thanks Pradeep. I did read your article, it was helpful. Pradeep i do see different procs using most of the CPU but how would i know which sql inside the proc is causing this issue. Pradeep mentioned below are few questions please try to answer:

i) From that tool i see many threads...does it mean that each procedure if using one thread?

This is what i thought of approach, please advice on this

i) i would make a note of the proc which is taking most CPU over span of 10 mins and then would just tell the developer that this is one of the reasons for CPU spike?

i would appreciate if you can provide your email id..thank you so much..

Adiga One Orange Chip Points: 27284 More actions · Answer 5

In sysprocesses, one SPID can have multiple OS threads (KPID). Yes, the SPID for which one of the threads is consuming more CPU is the cause for CPU spike.

You can monitor which threads are use during that period and identify the SPID causing CPU issues.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 6

thanks....does that mean that if a procedure is running would it have more than one thread?

rew-370421 Hall of Fame Points: 3640 More actions · Answer 7

Pradeep

Did you successfully use PROCESS EXPLORER on SQL 2005, or just SQL 2000?

I was NOT able to successfully find the offending SPID via the TID Query I found in PROCESS EXPLORER.

select SPID from sysprocesses where kpid=11744

"11744" is the TID value in PROCESS EXPLORER.

My query came back empty. SO I wasn't able to do this query: DBCC INPUTBUFFER(SPID)

...thanks

Adiga One Orange Chip Points: 27284 More actions · Answer 8

It worked for me on all versions of SQL server and both 32 bit and 64 bit. The TID that you were trying to use in select * from sys.processes would have terminated by the time you executed the query?

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

rew-370421 Hall of Fame Points: 3640 More actions · Answer 9

Thanks for your reply, Pradeep.

According to PROCESS EXPLORER (Properties/Threads) I should be looking for TID=61852

And this process has been running for some time, so it did not disappear before I had to time to run the query.

I did "select * from sysprocesses where kpid=61852" and it came back empty.

I then did "select * from sysprocesses" to display everything. There are NO entries in sysprocesses that are 5 digits?

Please help!

3120

3060

3040

3036

2900

2888

2876

2860

2560

2548

2448

0

-3280

-3688

-4020

ben.rosato SSCommitted Points: 1929 More actions · Answer 10

ben.rosato

SSCommitted

Points: 1929

September 28, 2010 at 11:00 am

#1228539

How about using profiler.

rew-370421 Hall of Fame Points: 3640 More actions · Answer 11

I can check out PROFILER as well, which is new to me.

ben.rosato SSCommitted Points: 1929 More actions · Answer 12

rew-370421 (9/28/2010)
I can check out PROFILER as well, which is new to me.

I would make it a point to get very familiar with it.

Chris Houghton SSCrazy Points: 2557 More actions · Answer 13

Profiler is an essential tool in the DBAs box of tricks. Be careful though. I would advise against running profiler against a production server as it can impact performance. You'd be better creating Server Side traces using profiler and then switiching them on and off on a schedule using SQL Server agent jobs.

Regards

rew-370421 Hall of Fame Points: 3640 More actions · Answer 14

Thanks, Chris.

I will start reading up on PROFILER since this process does not work on SQL2005.