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
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
