Monitor the CPU Usage of Your SQL Servers

Friday Apr 29th 2005 by Muthusamy Anantha Kumar aka The MAK
Share:

Learn how to take advantage of WMI and VBScript to monitor CPU Utilization of SQL Servers and to find heavy and under utilized 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

Step 4

Using Windows scheduler, schedule this VBScript to be executed every 5 minutes, 7 days a week. [Refer Fig 1.2 and 1.3]

Click for larger image

Fig 1.2

Click for larger image

Fig 1.3

When the Script runs for the first time, it takes the CPU inventory of all the servers listed in the table "Servers," if the inventory was not previously taken. [Refer Fig 1.4]

Click for larger image

Fig 1.4

Subsequent runs will collect information on the CPULoad table from all of the servers. [Refer Fig 1.5]


Fig 1.5

Once you collect information for weeks or months, you can run queries to those below to find the peak usage time and the heavily used servers and under utilized servers.

Query 1

Average CPU load on the servers from 12:00 am to 8:am during weekdays.

Select Servername, CPU, avg(cast (Loadpercentage as decimal(10,3))) as [Load%]
from cpuload where datepart(hour,time_stamp)>=0 and 
datepart(hour,time_stamp)<=8 and datepart(weekday,time_stamp) >=2
and datepart(weekday,time_stamp) <=6
group by Servername, CPU order by Servername,CPU

Query 2

Average CPU load on the servers from 8:00 am to 4:00 pm during weekdays.

Select Servername, CPU, avg(cast (Loadpercentage as decimal(10,3))) as [Load%]
from cpuload where datepart(hour,time_stamp)>=8 and 
datepart(hour,time_stamp)<=16 and 
datepart(weekday,time_stamp) >=2
and datepart(weekday,time_stamp) <=6
group by Servername, CPU order by Servername,CPU

Query 3

Average CPU load on the servers from 4:00 pm to 12:00 am during weekdays.

Select Servername, CPU, avg(cast (Loadpercentage as decimal(10,3))) as [Load%]
from cpuload where datepart(hour,time_stamp)>=16 and 
datepart(hour,time_stamp)<=24 
and datepart(weekday,time_stamp) >=2
and datepart(weekday,time_stamp) <=6
group by Servername, CPU order by Servername,CPU

Query 4

Average CPU load on the servers from 12:00 am to 8:00 am, 8:00 am to 4:00pm and from 4:00 pm to 12:00 am during weekdays.

Select A.Servername, A.CPU, A.LoadPercentage as [12 am to 8 am],
B.LoadPercentage as [8 am to 4 pm],
C.LoadPercentage as [4 pm to 12 am] from
(
select Servername, CPU, avg(cast (Loadpercentage as decimal(10,3))) as LoadPercentage
from cpuload where datepart(hour,time_stamp)>=0 and 
datepart(hour,time_stamp)<=8 and datepart(weekday,time_stamp) >=2
and datepart(weekday,time_stamp) <=6
group by Servername, CPU ) as A,

(select Servername, CPU, avg(cast (Loadpercentage as decimal(10,3))) as LoadPercentage
from cpuload where datepart(hour,time_stamp)>=8 and 
datepart(hour,time_stamp)<=16 and 
datepart(weekday,time_stamp) >=2
and datepart(weekday,time_stamp) <=6
group by Servername, CPU ) as B,
(
select Servername, CPU, avg(cast (Loadpercentage as decimal(10,3))) as LoadPercentage
from cpuload where datepart(hour,time_stamp)>=16 and 
datepart(hour,time_stamp)<=24 
and datepart(weekday,time_stamp) >=2
and datepart(weekday,time_stamp) <=6
group by Servername, CPU ) as C
where A.servername=b.servername and A.servername=c.servername
and A.cpu=b.cpu and A.cpu=c.cpu

order by A.Servername, A.CPU

--result [Refer Fig 1.6]


Fig 1.6

Using the collected data you could plot graphs using Microsoft Excel sheets or publish the graph on the web using Round Robin database tools.

Conclusion

This article demonstrates the CPU Utilization of any SQL Server box. This article took advantage of WMI and VBScript in order to find the under utilized and heavily utilized Servers. This article can also be used for other Windows boxes.

» See All Articles by Columnist MAK

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