Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Apr 29, 2005

Monitor the CPU Usage of Your SQL Servers

By Muthusamy Anantha Kumar aka The MAK

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


MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM