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

Monitor SQL Server CPU Loads

Pre-requisite

Step 1

Step 2

Step 3

Step 4

Step 5

Step 6

Step 7

Get the Free Newsletter!

Latest Articles

SQL SORT BY Statement

Working with NULL Values in SQL

Working with SQL AND, OR, and NOT Operators

How to Use Databases With Python

Advertisers

Menu

Our Brands

Monitor SQL Server CPU Loads

Pre-requisite

Step 1

Step 2

Step 3

Step 4

Step 5

Step 6

Step 7

Related Articles

Best Online Courses to Learn SQL

Best Courses for Database Administrators

Tip 74 – Changing Cost Threshold for Parallelism

Get the Free Newsletter!

Latest Articles

SQL SORT BY Statement

Working with NULL Values in SQL

Working with SQL AND, OR, and NOT Operators

How to Use Databases With Python

Advertisers

Menu

Our Brands