Monitor SQL Server CPU Loads

Tuesday May 17th 2005 by Muthusamy Anantha Kumar aka The MAK
Share:

MAK illustrates how to monitor the CPU load on multiple SQL Server machines and how to collect data in Round Robin database to produce a graph.

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


Step 8

Execute C:\RRD\CreateDB.bat. [Refer Fig 1.2]

Click for larger image

Fig 1.2

This creates an .rrd file for every processor for the servers listed in server.txt.

Step 9

Create a Schedule to run the batch file C:\RRD\monitor.bat every 5 minutes as shown below. Refer Fig 1.3 and 1.4

Click for larger image

Fig 1.3


Fig 1.4

This schedule runs every minute and updates the .rrd database file for all of the servers listed in server.txt

Step 10

Collect information for the whole day and execute the command below to generate a graph. Refer Fig 1.5

CSCRIPT Show_Graph.vbs "ATDBQA" "0" "2005-04-27 10:00 am" "2005-04-27 3:00 pm"

Fig 1.5

Start ATDBQA_CPU0.png

Step 11

Execute the command below to see the graph Refer 1.6. It opens the PNG file with the associated default application. Refer Fig 1.7


Fig 1.6


Fig 1.7

Step 12

If you want to create one graph with all of the processor loads on it, then execute the show_graph2.vbs as shown below. Refer fig 1.8

Note: show_graph2.vbs can produce a graph for 16 processors. If the number of processor exceeds 16, it will fail to create the graph.

CSCRIPT Show_Graph.vbs "ATDBQA" "2005-04-27 6:00 am" "2005-04-28 9:00 pm"

Fig 1.8

Step 13

Execute the command below to see the graph. Refer 1.9. It opens the PNG file with the associated default application. Refer Fig 2.0


Fig 1.9


Fig 2.0

Conclusion

As mentioned before, this article illustrates how to monitor the CPU load on multiple SQL Server machines. It also illustrates on how to collect data in Round Robin database to produce a graph. This helps in determining the heavily used and under utilized servers. The illustrations of this article can be applied on any windows machine with WMI.

RRDTool, developed by Tobias Oetiker, is available under GNU General Public License.

» See All Articles by Columnist MAK

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved