Monitor the CPU Usage of Your SQL Servers

April 29, 2005

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

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]

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.

'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