Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»

How to Monitor for High CPU utilization in SQL Server Expand / Collapse
Author
Message
Posted Thursday, November 18, 2010 6:01 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 10:18 AM
Points: 514, Visits: 1,727
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
Posted Thursday, November 18, 2010 6:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 16, 2012 5:17 AM
Points: 11, Visits: 23
Jeff Moden: Yes, we can
Post #1022839
Posted Thursday, November 18, 2010 7:52 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:51 PM
Points: 36,959, Visits: 31,469
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1023259
Posted Friday, November 19, 2010 10:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 1:13 PM
Points: 15, Visits: 258
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
Posted Friday, November 19, 2010 1:46 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 10:18 AM
Points: 514, Visits: 1,727
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
Posted Friday, November 19, 2010 2:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 1:13 PM
Points: 15, Visits: 258
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
Posted Friday, November 19, 2010 3:23 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 10:18 AM
Points: 514, Visits: 1,727
BobSaint (11/19/2010)
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.


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
Posted Saturday, November 20, 2010 1:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:51 PM
Points: 36,959, Visits: 31,469
I can't wait for an article on the dashboard.

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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1023898
Posted Tuesday, January 4, 2011 9:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 9:24 AM
Points: 205, Visits: 1,352
editted - removed question
Post #1042515
Posted Thursday, January 13, 2011 6:19 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 1:33 PM
Points: 37, Visits: 264
Great article Geoff, very useful thanks for sharing!

Thanks,
Kimberly Killian
Sr. DBA / DB Engineer
www.sitedataview.com
Follow me on Twitter
Follow me on Facebook
Post #1047684
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse