dcsimg

Monitor SQL Server CPU Loads

May 17, 2005

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







The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers