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

By Muthusamy Anantha Kumar aka The MAK

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]

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]

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



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


















Thanks for your registration, follow us on our social networks to keep up-to-date