How to Monitor for High CPU utilization in SQL Server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Geoff Albin » How to Monitor for High CPU utilization in...

59 posts, Page 5 of 6

«««2 3 4 5 6 »»

How to Monitor for High CPU utilization in SQL Server

Rate Topic

Display Mode

Topic Options

Author

Message

Geoff A

Posted Thursday, November 18, 2010 6:01 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:01 AM
Points: 485, Visits: 1,566

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.

Post #1022812

Boxer

Posted Thursday, November 18, 2010 6:29 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, May 16, 2012 5:17 AM
Points: 11, Visits: 23

Jeff Moden: Yes, we can

Post #1022839

Jeff Moden

Posted Thursday, November 18, 2010 7:52 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:21 PM
Points: 32,893, Visits: 26,765

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! Smile

--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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1023259

BobSaint

Posted Friday, November 19, 2010 10:01 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:01 AM
Points: 15, Visits: 180

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.

Post #1023668

Geoff A

Posted Friday, November 19, 2010 1:46 PM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:01 AM
Points: 485, Visits: 1,566

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)

Post #1023802

BobSaint

Posted Friday, November 19, 2010 2:14 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:01 AM
Points: 15, Visits: 180

Geoff - that is awesome!! Very nice presentation - and it covers all the prime touch points near and dear to the DBA at a glance. My curiosity is bristled - data cached in a local database? for how long? User-settable thresholds on all counters? User-settable sample rates? Alerts when thresholds exceeded (SQL or UI)? All hooks through WMI? Can you track other stuff like auto-grows, errors, failed authentications?

To be honest, I have yet to dabble in PS, although it seems like all the rage for scripting these days.

Again - very nice job.

Post #1023822

Geoff A

Posted Friday, November 19, 2010 3:23 PM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:01 AM
Points: 485, Visits: 1,566

BobSaint (11/19/2010)

its a small local DB.
its all configurable.
alerts are not a part of this project.
almost all is through WMI except for user connections.

alerting I handle differently, which is another article i still owe to Steve..... (part two of this article... http://www.sqlservercentral.com/articles/Monitoring/71390/

Post #1023851