November 18, 2010
reference: http://www.sqlservercentral.com/articles/CPU/71388 / This is an article on how to monitor SQL Agent calls vbScript server SQL server CPU utilization a case. Can notify the system when the machine DB. Very useful, if there is a problem in the system can call the sql profiler similar tracking program to automatically grab what sql implementation, analysis of the problem for more help. How to Monitor for High CPU utilization in SQL Server
By Geoff Albin , 2010/11 / 17
How to Monitor CPU usage over time
Have you ever had a SQL Server become unresponsive? Did your ever take a phone call from a user telling you that their application is down? Of course you have! We are DBA and we are always on the front line of application troubleshooting. Most companies or clients accept what I like to call “reactive troubleshooting”. Reactive troubleshooting is figuring out what is wrong after the application is down. However, I prefer to use “proactive troubleshooting”. It is always better to know what is going to happen before it actually happens. Unless your company has shelled out big bucks for 3rd party tools that will help you with your “proactive troubleshooting”, you as a DBA are going to be on your own to create process that does this for you. Having a server crash because the CPU is hung at 100% is sometimes difficult if not impossible to troubleshoot. Once the server is down, the counters are usually gone. Of course, you could set up Performance Counters to export their data overtime, but that means you are going to have to wait for another crash to be able to troubleshoot. Not something I like to explain to management. I would like to share with you how I monitor CPU usaage overtime and alert me to trouble when it is occuring. I have done this for over 10 years on hundreds of SQL Servers from Windows 2000 and SQL 2000 to Windows 2008 R2 x64 and SQL 2008 R2 x64. High Level Overview
The way I monitor CPU usage over time involves 3 seperate steps. We place a small Visual Basic Script file on the server to monitor.
We create a SQL Server Agent job to run that small VB script program.
We create a SQL Server Agent Alert to listen to what the VB script does.
Assumption
There are two assumptions I will make. One is that you have DB Mail setup and working with a valid Operator. The other is that your account that runs the SQL Server Agent has the ability to use SQLCMD to connect to your instance with Windows authentication. If your SQL Agent cannot do this you will need to modify the VB script. If you need help with that, drop me an email. Step 1
I have always used VB scripts to help me do things SQL Server cannot do alone. I understand there are many other ways to do things, but I am a bit long in the tooth and this is what I know. The VB script that we use looks like this; On Error Resume Next HighMark = 90 0 Percent Violation = 0 counter for the number of times CPU hits the high mark Set objService = GetObject (“Winmgmts: {impersonationlevel = impersonat e}! \ Root \ Cimv2″) heck it 10 times within 1 minutes and if it consistently above the high mark, raise alert i = 0 Do While i <1 Set objInstance1 = objService.Get ("Win32_PerfRawData_PerfOS_Processor. Name = Total quot;) N1 = objInstance1.PercentProcessorTime D1 = objInstance1.TimeStamp_Sys100NS leep for 2 seconds = 2000 ms This sleep is to capture the two time stamps WScript.Sleep (2000) Set perf_instance2 = objService.get ("Win32_PerfRawData_PerfOS_Processor. Name = Total quot;) N2 = perf_instance2.PercentProcessorTime D2 = perf_instance2.TimeStamp_Sys100NS PercentProcessorTime = (1 - ((N2 - N1) / (D2-D1))) * 100 If PercentProcessorTime > HighMark Then Violation = Violation 1 If Violation = 10 Then RaiseError “CPU utilization is higher than 90 Pct for the last minute”, “1″ Violation = 0 End If Else Violation = 0 End If leep for 4 seconds = 4000 ms his sleep is to raise alert only if the CPU high is constant, all 10 times during the check peariod WScript.Sleep (4000) Loop Sub RaiseError (msg, sev) Set objShell = CreateObject (“WScript.Shell”) query = ” raiserror (” ” quot; msg ” ” “, ” sev “, ” ” 1) with log “progStr =” sqlcmd-E-q ” ” “” ” query ” “” “objShell.exec (progStr) End Sub
In order to use that script, you just have copy and paste the code into a file with a. vbs extension. Remember that in Windows, in order to save that file with something like notepad, you have to choose “Save as type: All Files”. The default behavior is to use a. txt extension. That of course will not work. I have always named mine CPUpct.vbs Hey, what is this VBS script doing? Let break it down. On Error Resume Next HighMark = 90 0 Percent Violation = 0 counter for the number of times CPU hits the high mark Set objService = GetObject (“Winmgmts: {impersonationlevel = impersonat e}! \ Root \ Cimv2 “) heck it 10 times within 1 minutes and if it consistantly above the high mark, raise alert
HighMark = 90 means the highest you want to see your CPU run at is 90% . You can set that number to anything you would like. If you feel 65% is too high, set the 90 to 65. Violation = 0 is a starting point for the number of violation that occur during the while loop. You do not need to edit this value. i = 0 Do While i <1 Set objInstance1 = objService.Get ("Win32_PerfRawData_PerfOS_Processor. Name = Total quot;) N1 = objInstance1.PercentProcessorTime D1 = objInstance1.TimeStamp_Sys100NS leep for 2 seconds = 2000 ms This sleep is to capture the two time stamps WScript.Sleep (2000) Set perf_instance2 = objService.get ("Win32_PerfRawData_PerfOS_Processor. Name = Total quot;) N2 = perf_instance2.PercentProcessorTime D2 = perf_instance2.TimeStamp_Sys100NS PercentProcessorTime = (1 - ((N2 - N1) / (D2-D1))) * 100 If PercentProcessorTime> HighMark Then Violation = Violation 1 If Violation = 10 Then RaiseError “CPU utilization is higher than 90 Pct for the last minute”, “1″ Violation = 0 End If Else Violation = 0 End If leep for 4 seconds = 4000 ms This sleep is to raise alert only if the CPU high is constant, all 10 times during the check peariod WScript.Sleep (4000) Loop

Above is where all the fun happens. We need to first set 4 variables to run a formula. The variables are; N1 = the first percentage of processor time. D1 = is the first time stamp.
N2 = the second percentage of processor time. D2 = this is the second time stamp.
If at 8:42:01 the CPU was at 94% and at 8:42:03 the CPU was at 97%, the variable would look like this. N1 = 4879911796875
N2 = 4879912578125
D1 = 129315337212597240
D2 = 129315337233378623 Now we run the formula, PercentProcessorTime = (1 – ((4879912578125-4879911796875) / (12931533723337 8000-129315337212597000))) * 100 That means our PercentProcessorTime = 96.24056 which is greater than the HighMark. Since it is greater than our HighMark, we set the variable Violation = 1. Once the variable Violation = 10, we move onto the next step. If the variable PercentProcessorTime falls below the HighMark at anytime during the while loop, the variable Violation will drop back down to 0. Since 1 loop takes 6 seconds and the PercentProcessorTime variable must be greater than the HighMark 10 times in a row we know that the CPU was above the HighMark for at least 1 minute. OK, I am a little lost on the math up there???
Microsoft does not provide a lot of documentation on how or what the values ??really represent. If you are really interested in what is going on, let me show you. Copy and paste the code below into a seperate file. To keep it simple, lets just name it test.vbs. Remember to make sure it has the proper vbs extension. If you are not intereseted in what is going on, you can just skip ahead to Step 2. On Error Resume Next Set objService = GetObject (“Winmgmts: {impersonationlevel = impersonat e}! \ Root \ Cimv2″) i = 0 Do While i <1 Set objInstance1 = objService.Get (“Win32_PerfRawData_PerfOS_Processor. Name = Total quot;) N1 = objInstance1. PercentProcessorTime D1 = objInstance1.TimeStamp_Sys100NS leep for 2 seconds = 2000 ms This sleep is to capture the two time stamps WScript.Sleep (2000) Set perf_instance2 = objService.get (“Win32_PerfRawData_PerfOS_Processor. Name = Total quot;) N2 = perf_instance2 . PercentProcessorTime D2 = perf_instance2.TimeStamp_Sys100NS PercentProcessorTime = (1 – ((N2 – N1) / (D2-D1))) * 100 WScript.Echo “N1 =”