Monitor the CPU Usage of Your SQL Servers

This article demonstrates the CPU Utilization of any SQL
Server box by taking advantage of WMI and VBScript in order to find under
utilized Servers. It is important to know which Servers use more CPU and during
what period of time, to help the SQL Server database administrator to load
balance.

Pre-requisite

1. 
Make sure you
can execute VBScript on the machine where you are running this VbScript.

2. 
Make sure the
WMI service is running on both the workstation and the server that you are
trying to get the CPU load.

3. 
Make sure Task
Scheduler service is running on the machine where you are scheduling this
VBScript.

Step 1

Create the database required for CPU Monitoring.

Note: Please add your server names to the
"Servers" table.


Use master
Go
Create database ServerCPUUsage
go
use ServerCPUUsage
go
use master
go
sp_addlogin ‘cpuusageuser’,’cpuusageuser’,’ServerCPUUsage’
go
use ServerCPUUsage
go
sp_adduser cpuusageuser
go
sp_addrolemember ‘db_datareader’,’cpuusageuser’
go
sp_addrolemember ‘db_datawriter’,’cpuusageuser’
go
Create table Servers (id int identity(1,1), ServerName varchar(128))
go
–Please add your server names here.
insert into Servers (Servername) select ‘SQL’
insert into Servers (Servername) select ‘Claire’
insert into Servers (Servername) select ‘Stargate’
insert into Servers (Servername) select ‘Boomer’
go
Create table ServerCPUInventory
(ServerName varchar(128),
Architecture int,
Caption varchar(256),
Manufacturer varchar(256),
Cpu varchar(10),
CpuStatus int,
CurrentClockSpeed int,
MaxClockSpeed int,
LoadPercentage int,
time_stamp datetime constraint def_timestamp1 default getdate()
)
go
create table CPULoad
(ServerName varchar(128), CPU varchar(10),
Loadpercentage int,
time_stamp datetime constraint def_timestamp default getdate())

–Architechture
/*
0 x86
1 MIPS
2 Alpha
3 PowerPC
6 Intel Itanium Processor Family (IPF)
–CPU Status
0 Unknown
1 CPU Enabled
2 CPU Disabled by User via BIOS Setup
3 CPU Disabled By BIOS (POST Error)
4 CPU is Idle
5 Reserved
6 Reserved
7 Other

*/

Download cpu_usage.sql

Step 2

Create folder C:\CPU_Usage [Refer Fig 1.0]



Fig 1.0

Step 3

Copy or download the code for the CPU_Usage.VBS
script and paste it under C:\CPU_usage Folder. [Refer Fig 1.1]

Note: Please change the connection string in the VBScript to point to your
server and database.



Fig 1.1


‘Objective: To find the load of all the CPU on a server
‘Created by: MAK
‘Created Date: Apr 21, 2005

‘ADODB connection
Dim AdCn
Dim AdRec
Dim i, SQL
dim adrec2

Set AdCn = CreateObject(“ADODB.Connection”)
Set AdRec = CreateObject(“ADODB.Recordset”)
Set AdRec2 = CreateObject(“ADODB.Recordset”)

‘Update COnnection string with you servername, databasename, login and password.
connstring = “Provider=SQLOLEDB.1;Data Source=SQL;Initial Catalog=ServerCPUUsage;user id =
‘cpuusageuser’;password=’cpuusageuser’ ”

AdCn.Open =connstring

SQL=”Select distinct A.Servername as InventoryServername,LoadServerName =case when b.servername
is NULL then ‘No Such Server’ end from Servers A left outer join ServerCPUInventory B on
A.servername = B.ServerName”

AdRec.Open SQL, AdCn,1,1

while not Adrec.EOF
Computer = ltrim(rtrim(Adrec(“InventoryServername”)))
Computer2 = ltrim(rtrim(Adrec(“LoadServerName”)))
Set WshNtwk = WScript.CreateObject(“WScript.Network”)
Set procset = GetObject(“winmgmts:{impersonationLevel=impersonate}!\\” & Computer &
“\root\cimv2”).InstancesOf (“Win32_Processor”)
checkedCPU = WshNtwk.ComputerName

‘Set ProcSet = GetObject(“winmgmts:”).InstancesOf (“Win32_Processor”)

‘Wscript.echo “Computer” & “,”& “Architecture” & “,”& “Caption” & “,”& “Manufacturer” & “,”&
“Cpu#” &”,”&”CpuStatus” & “,”& “CurrentClockSpeed” & “,”& “MaxClockSpeed” & “,”&
“LoadPercentage”

for each System in ProcSet
‘Wscript.echo “Computer Name =” & Computer
‘Wscript.echo “Architecture =” & system.Architecture
‘Wscript.echo “Caption=” & system.Caption
‘Wscript.echo “Manufacturer=” & system.Manufacturer
‘Wscript.echo “CPU Status=” & system.CpuStatus
‘Wscript.echo “Current Clock Speed=” & system.CurrentClockSpeed
‘Wscript.echo “Maximum Clock Speed=” & system.MaxClockSpeed
‘Wscript.echo “Load % =” & system.LoadPercentage
‘Wscript.echo Computer & “,”& system.Architecture & “,”& system.Caption & “,”& system.deviceid
&”,”& system.Manufacturer & “,”& system.CpuStatus & “,”& system.CurrentClockSpeed & “,”&
system.MaxClockSpeed & “,”& system.LoadPercentage

if computer2=”No Such Server” then
SQL=”insert into ServerCPUInventory
(ServerName,Architecture,Caption,Manufacturer,Cpu,CpuStatus,CurrentClockSpeed,MaxClockSpeed,Load
Percentage) select ‘” & COmputer &”‘,” & system.Architecture &”,'”&system.Caption
&”‘,'”&system.Manufacturer& “‘,'”&
system.deviceid&”‘,”&system.cpustatus&”,”&system.CurrentClockSpeed & “,”& system.MaxClockSpeed &
“,”& system.LoadPercentage
else
SQL=”insert into CPULoad(Servername,cpu, Loadpercentage) select ‘” & COmputer &”‘,'”&
system.deviceid& “‘,” &system.Loadpercentage
end if
‘wscript.echo SQL
AdRec2.Open SQL, AdCn,1,1

next

Adrec.movenext
wend

Latest Articles