Monitor SQL Server CPU Loads

This article illustrates
how to monitor the CPU load on SQL Server and Windows machines using Windows
Management Instrumentation and VBScript to collect data in the Round Robin
database and then produce graphs using the Round Robin Tool.

Usually SNMP is used to
monitor and analyze CPU loads. This article illustrates how to obtain CPU load
information using WMI.

Pre-requisite

a. 
Create folder C:\RRD

b. 
Download rrd.lib, rrdtool.exe
and rrd_cgi.exe from http://people.ee.ethz.ch/~oetiker/webtools/rrdtool/

RRDTool
is licensed under GNU.

c. 
Make sure the WMI service is
running on the server and client.

d. 
Ensure the login used to run
the given script has enough permission to make WMI calls.

e. 
Make sure Scheduler service is
running on the machine where you want to schedule the job.

Note: This article uses RRDtool 1.0.49 version for Windows

Step 1

Create C:\RRD\Server.txt
and list all of the servers on which you want to monitor CPU load. [Refer Fig
1.0]



Fig 1.0

Step 2

Create C:\RRD\CreateRRD_DB.vbs.
Download CreateRRD_DB.vbs.

‘Objective: TO create one RRD database for every processor for a given server name
‘Created by:MAK
‘Date: Apr 23, 2005
‘Usage: cscript //b //nologo createrrd_db.vbs atdbqa

Set WshShell = WScript.CreateObject(“WScript.Shell”)

Set objArgs = WScript.Arguments
Computer=objArgs(0)

Set procset =
GetObject(“winmgmts:{impersonationLevel=impersonate}!\\” & Computer
& “\root\cimv2”).InstancesOf (“Win32_Processor”)

for each System in ProcSet
query =”rrdtool create ” & Computer &”_”& system.deviceid &”.rrd
–start ” & UDate(getutc(now())) & ”
–step 300 DS:LOAD1:GAUGE:600:-1:100 RRA:AVERAGE:0.5:1:1200″
wscript.echo query

Return = WshShell.Run(Query, 1)

next

function UDate(oldDate)
UDate = DateDiff(“s”, “01/01/1970 00:00:00”, oldDate)
end function

function getutc(mydate)

od = mydate
set oShell = CreateObject(“WScript.Shell”)
atb = “HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias”
offsetMin = oShell.RegRead(atb)
nd = dateadd(“n”, offsetMin, od)
wscript.echo nd
‘Response.Write(“Current = ” & od & “<br>UTC = ” & nd)
getutc= nd
end function

Step 3

Create
C:\RRD\CreateDB.bat. Download CreateDB.bat.


REM Type: Batch File
REM Created by: MAK
REM Contact: mak_999@yahoo.com
REM Create Round Robin database for all the server listed in server.txt

for /f “tokens=1,2,3” %%i in
(C:\rrd\server.txt)
do C:\WINNT\system32\cscript.exe //b //nologo c:\rrd\createrrd_db.vbs %%i

Step 4


‘Objective: Update an RRD database with current CPULoad information of the server
‘Created by: MAK
‘Date Apr 23, 2005
‘Usage: cscript //b //nologo Updaterrd_db.vbs atdbqa

Set WshShell = WScript.CreateObject(“WScript.Shell”)

Set objArgs = WScript.Arguments
Computer=objArgs(0)
Set procset =
GetObject(“winmgmts:{impersonationLevel=impersonate}!\\”
& Computer & “\root\cimv2”).InstancesOf
(“Win32_Processor”)

for each System in ProcSet
query =”rrdtool update “&computer& “_” & system.deviceid & “.rrd ”
& UDate(getutc(now())) &”:” &system.LoadPercentage
Return = WshShell.Run(Query, 1)
next

function UDate(oldDate)
UDate = DateDiff(“s”, “01/01/1970 00:00:00”, oldDate)
end function

function getutc(mydate)

od = mydate
set oShell = CreateObject(“WScript.Shell”)
atb = “HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias”
offsetMin = oShell.RegRead(atb)
nd = dateadd(“n”, offsetMin, od)
wscript.echo nd
‘Response.Write(“Current = ” & od & “<br>UTC = ” & nd)
getutc= nd
end function

Step 5

Create C:\RRD\ monitorload.bat. Download MonitorLoad.bat.


REM Type: Batch File
REM Created by: MAK
REM Contact: mak_999@yahoo.com
REM Create Round Robin database for all the server listed in server.txt

for /f “tokens=1,2,3” %%i in
(C:\rrd\server.txt)
do C:\WINNT\system32\cscript.exe //b //nologo c:\rrd\Updaterrd_db.vbs %%i

Step 6

Create
C:\RRD\Show_Graph.VBS. Download Show_Graph.vbs.


‘Objective: To generate the PNG graph file for the given RRD database
‘Created by: MAK
‘Date: Apr 23, 2005
‘Usage: cscript show_graph.vbs “ServerName” “Processor#” “From Date” “To Date”
‘Example: cscript show_graph.vbs “ATDBQA” “0” “2005-04-27 10:00 am” “2005-04-27 3:00 pm”
Set WshShell = WScript.CreateObject(“WScript.Shell”)

Set objArgs = WScript.Arguments
servername=objArgs(0)
CPU=objArgs(1)
fromtime=UDATE(getutc(objArgs(2)))
totime=UDATE(getutc(objArgs(3)))

query =”rrdtool graph “& servername &”_cpu”& CPU &”.png
–start ” & fromtime &”
–end “& totime &” DEF_myspeed=”& servername &”_
cpu”&cpu&”.rrd:LOAD1:AVERAGE LINE2:myspeed#FF0000
–alt-autoscale-max -a PNG -h400 -w800 -l0 -u100 -v CPU_Load_Percentage_of_CPU”& CPU&”
-t CPU_Utilization_on_” & Servername & ” ”
wscript.echo Query
Return = WshShell.Run(Query, 1)
Query =”Start “&servername &”_cpu”& CPU &”.png”
wscript.echo “You can see the graph by typing the following command in command shell ” & Query
‘Return = WshShell.Run(Query, 1)

function UDate(oldDate)
UDate = DateDiff(“s”, “01/01/1970 00:00:00”, oldDate)
end function

function getutc(mydate)

od = mydate
set oShell = CreateObject(“WScript.Shell”)
atb = “HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias”
offsetMin = oShell.RegRead(atb)
nd = dateadd(“n”, offsetMin, od)
wscript.echo nd
‘Response.Write(“Current = ” & od & “<br>UTC = ” & nd)
getutc= nd
end function

Note: You need the
following files for this article to work. Refer Fig 1.1



Fig 1.1

Step 7

Create C:\RRD\Show_Graph2.VBS.
Download Show_Graph2.vbs.


‘Objective: To generate the PNG graph file for the given RRD database
‘Created by: MAK
‘Date: Apr 23, 2005
‘Usage: cscript show_graph.vbs “ServerName” “Processor#” “From Date” “To Date”
‘Example: cscript show_graph2.vbs “ATDBQA” “2005-04-27 10:00 am” “2005-04-27 3:00 pm”

Dim Mycolor(16)
dim count
Mycolor(0) = “#FF0000” ‘Red
Mycolor(1) = “#0000A0” ‘Dark Blue
Mycolor(2) = “#FF00FF” ‘Pink
Mycolor(3) = “#000000” ‘Black
Mycolor(4) = “#00FFFF” ‘Turquoise
Mycolor(5) = “#0000FF” ‘Light Blue
Mycolor(6) = “#FF0080” ‘Light Purple
Mycolor(7) = “#800080” ‘Dark Purple
Mycolor(8) = “#FFFF00” ‘Yellow
Mycolor(9) = “#00FF00” ‘Pastel Green
Mycolor(10) = “#808080” ‘Dark Grey
Mycolor(11) = “#FF8040” ‘Orange
Mycolor(12) = “#804000” ‘Brown
Mycolor(13) = “#800000” ‘Burgundy
Mycolor(14) = “#808000” ‘Forest Green
Mycolor(15) = “#408080” ‘Grass Green
count=0

Set WshShell = WScript.CreateObject(“WScript.Shell”)

Set objArgs = WScript.Arguments
servername=objArgs(0)
fromtime=UDATE(getutc(objArgs(1)))
totime=UDATE(getutc(objArgs(2)))

query =”rrdtool graph “& servername &”_cpu.png –start ” & fromtime &” –end “& totime

Set procset = GetObject(“winmgmts:{impersonationLevel=impersonate}!\\” & ServerName
& “\root\cimv2”).InstancesOf (“Win32_Processor”)

for each System in ProcSet

filename = servername & “_” & system.deviceid
varname= “speed” & system.deviceid
string1=”Average_”& system.deviceid
query = query & ” DEF:” & varname & “=”
& filename & “.rrd:LOAD1:AVERAGE LINE3:”
& varname &mycolor(count) &”:”& string1& ” ”
count=count+1
next
‘Return = WshShell.Run(Query, 1)

query =query & ”
–alt-autoscale-max -a PNG -h400 -w800 -l0 -u100 -v CPU_Load_Percentage_of_CPU
-t CPU_Utilization_on_” & Servername

wscript.echo Query
Return = WshShell.Run(Query, 1)

Query =”Start “&servername &”_cpu” &”.png”
wscript.echo “You can see the graph by typing the following command in command shell ” & Query
‘Return = WshShell.Run(Query, 1)

function UDate(oldDate)
UDate = DateDiff(“s”, “01/01/1970 00:00:00”, oldDate)
end function

function getutc(mydate)

od = mydate
set oShell = CreateObject(“WScript.Shell”)
atb = “HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias”
offsetMin = oShell.RegRead(atb)
nd = dateadd(“n”, offsetMin, od)
wscript.echo nd
‘Response.Write(“Current = ” & od & “<br>UTC = ” & nd)
getutc= nd
end function

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles