How to Monitor for High CPU utilization in SQL Server

Question

Post reply

How to Monitor for High CPU utilization in SQL Server

Viewing 15 posts - 31 through 45 (of 59 total)

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

john_jakob Mr or Mrs. 500 Points: 587 More actions · Answer 1

Not sure this is a totally accurate indicator of utilization, but might be "in the ballpark":

-- Low-Budget Way to Get CPU Utilization

DECLARE @CPUStart INT,

@CPUEnd INT,

@IdleStart INT,

@IdleEnd INT,

@TotalStart INT,

@TotalEnd INT;

-- Make First Measurement

SELECT @CPUStart = @@CPU_BUSY,

@IdleStart = @@IDLE;

-- Delay 1 Seconds

WAITFOR DELAY '000:00:01';

-- Make Second Measurement

SELECT @CPUEnd = @@CPU_BUSY,

@IdleEnd = @@IDLE;

-- Compute Totals

SELECT @TotalStart = (@IdleStart + @CPUStart);

SELECT @TotalEnd = (@IdleEnd + @CPUEnd);

-- Compute Delta

DECLARE @DeltaCPU FLOAT;

SELECT @DeltaCPU = 100.0 * (@CPUEnd - @CPUStart) / (@TotalEnd - @TotalStart);

SELECT CONVERT(DECIMAL(12, 2), @DeltaCPU) AS 'PercentCPU';

Boxer SSC-Addicted Points: 495 More actions · Answer 2

SSC-Enthusiastic: Becoz thats where we observe the max utilization and we can do max optimization!!

David Betteridge Hall of Fame Points: 3264 More actions · Answer 3

My only concern with using 3rd party monitoring tools is that they could suddenly present you with a load of measurements/alerts which you don't fully understand!

We are currently building up our list of checks one at a time (real-time alert + nightly reports). That way we fully understand what each counter means and what is typlical for our environment.

That said, I've evaluated a selection of the tools (redgate, quest etc) and they do look great. I expect we will end up investing.

Good article.

Thanks

David

dukedba Valued Member Points: 62 More actions · Answer 4

Why is it so complicated? You should use resources available in MS SQL server to monitor bottle neck: CPU, Hard drive, Memory, and Network. Use Perfmon from windows. Set up with counter logs to monitor counters.

http://www.youtube.com/watch?v=3yEEzqDE5qI&feature=related

dukedba Valued Member Points: 62 More actions · Answer 5

Why is it so complicated? You should use resources available in MS SQL server to monitor bottle neck: CPU, Hard drive, Memory, and Network. Use Perfmon from windows. Set up with counter logs to monitor counters.

http://www.youtube.com/watch?v=3yEEzqDE5qI&feature=related

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 6

dukedba (11/17/2010)
Why is it so complicated? You should use resources available in MS SQL server to monitor bottle neck: CPU, Hard drive, Memory, and Network. Use Perfmon from windows. Set up with counter logs to monitor counters.
http://www.youtube.com/watch?v=3yEEzqDE5qI&feature=related

Can you get Perfmon to send an email notification on an event like when the CPU has been at 90% for a minute or more?

--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)

pdeokar SSC Journeyman Points: 76 More actions · Answer 7

hi sir,

It is very good. but tell me more some idea to decreas cpu utilization..

crazy4sql SSCoach Points: 19590 More actions · Answer 8

I might be wrong,

but want to know that how the alert is connecting your job or knowing the threshold limit of cpu usage which you mentioned in vbs.

----------
Ashish

Geoff A SSChampion Points: 11511 More actions · Answer 9

hi crazy4sql,

the connection between the vb script and the alert is the raiserror command. when the error hits the sql server error log, the sql server alert service sees it and fires the email.

thanks for reading.

crazy4sql SSCoach Points: 19590 More actions · Answer 10

i see....

I tried to play with it...have configured vbs as CPU utilisation as 1% but not getting any alert....

so i am thinking of 2 reason:-

1)server not constantly more than 2%. It might be coming down and going up very frequently.

2) I am wrong and need more modification in vbs.

----------
Ashish

Geoff A SSChampion Points: 11511 More actions · Answer 11

crazy4sql ,

the server must be over the threshold for 10 consecutive loops.

if you want to test, use something to cause your CPU to run over the threshold for at least 1 minute.

Boxer SSC-Addicted Points: 495 More actions · Answer 12

Boxer

SSC-Addicted

Points: 495

November 18, 2010 at 6:29 am

#1252457

Jeff Moden: Yes, we can

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 13

Boxer (11/18/2010)
Jeff Moden: Yes, we can

Cool! I'll have to give it a try. In the meantime, someone should write an article on that! 🙂

--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)

BobSaint SSC Enthusiast Points: 175 More actions · Answer 14

Geoff - nice article, thanks for posting.

One thing I wanted to mention on security, if the account that SQL Agent runs with is denied access to run a command line script (CmdExec), a workaround would be to create a Proxy for CmdExec and then map an account that does have rights to run this job.

Although I have never tried this with SQL 2000, I have with 2005+ and the documentation seems to imply you can do it: http://support.microsoft.com/kb/890775

This keeps all security concerns happy because it elevates permissions to run the command line (VBScript in your case) only for a specific job or jobs.

Overall a very resourceful solution. I've been working on a "roll my own" solution to monitor several parameters of my servers through WMI hooks for some time now - disk free%, CPU Utilization, memory, I/O, etc. Who knows when I'll ever get it completed/tested but if I do I'll be sure to share!

Cheers.

Geoff A SSChampion Points: 11511 More actions · Answer 15

BobSaint,

yes, I am hard at work learning what powershell and SSRS can do.... one day, when it is all finished I will write an article on how I created this SQL Server Dashboard. (for FREE)