Monitor performance counter with WMI and SQL Server.
WMI is very important to use as central monitoring task. I have created performance monitoring which checks Disk Queue Legth, Disk Read/Sec, Disk Write/Sec, CPU Usage, Memory Usage, Disk Read Bytes/Sec, Disk Write Bytes/Sec, ASP Request/Sec. Create a table “PerformanceCounter” to store all information.
USE [Adventureworks2012]
GO
/****** Object: Table [dbo].[PerformanceCounter] Script Date: 05/27/2013 12:35:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PerformanceCounter](
[ServerName] [varchar](20) NULL,
[CounterName] [varchar](100) NULL,
[CounterValue] [varchar](100) NULL,
[CreatedDate] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[PerformanceCounter] ADD DEFAULT (getdate()) FOR [CreatedDate]
GO
Open Notepad and save below code with “PerformanceCount.vbs”.
Option Explicit
On Error Goto 0
Dim strSrv, strQuery
Dim arrylist(9)
arrylist(0) = ("Server1")
arrylist(1) = ("Server2")
arrylist(2) = ("Server3")
arrylist(3) = ("Server4")
arrylist(4) = ("Server5")
arrylist(5) = ("Server6")
arrylist(6) = ("Server7")
arrylist(7) = ("Server8")
arrylist(8) = ("Server9")
arrylist(9) = ("Server10")
Const DB_CONNECT_STRING = "Provider=SQLOLEDB.1;Data Source=localmachinename ;Initial Catalog=AdventureWorks2012;user id ='sa';password='password'"
Dim myConn, myCommand
For Each strSrv in arrylist
On Error Resume Next
Call GetDiskQueueLegth(StrSrv)
Call GetDiskReadperSec(StrSrv)
Call GetDiskWritesPerSec(StrSrv)
Call GetCPUUsage(StrSrv)
Call GetMemory(strSrv)
Call GetDiskWriteBytesPerSec(strSrv)
Call GetDiskReadBytesPerSec(strSrv)
Call GetRequestPerSec(strSrv)
Next
Function GetMemory(strSrv)
'On Error Resume Next
Dim objWMIService, Item, Proc
strQuery = "select Name,Pagelifeexpectancy from Win32_PerfRawData_MSSQLSERVER_SQLServerBufferManager"
Set objWMIService = GetObject("winmgmts:\\" & StrSrv & "\root\cimv2")
Set Item = objWMIService.ExecQuery(strQuery,,48)
For Each Proc In Item
Dim myConn,myCommand
'wscript.echo Proc.Name
'wscript.echo Proc.Pagelifeexpectancy
If Proc.Pagelifeexpectancy < 300 Then
Set myConn = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command" )
myConn.Open DB_CONNECT_STRING
Set myCommand.ActiveConnection = myConn
myCommand.CommandText = "Insert into PerformanceCounter values('" & StrSrv & "','Memory PLE','" & Proc.Pagelifeexpectancy & "',getdate())"
myCommand.Execute
myConn.Close
End If
Next
End Function
Function GetCPUUsage(strSrv)
Dim objWMIService,objRefresher,colItems,Proc,CPUUsage,Cnt
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strSrv & "\root\cimv2")
set objRefresher = CreateObject("WbemScripting.SWbemRefresher")
Set colItems = objRefresher.AddEnum (objWMIService, "Win32_Processor").objectSet
objRefresher.Refresh
CPUUsage = 0
Cnt = 0
For Each Proc in colItems
If Proc.LoadPercentage Then
CPUUsage = CPUUsage + Proc.LoadPercentage
'objRefresher.Refresh
Cnt = Cnt + 1
End If
Next
If Cnt > 0 and Cint(CPUUsage/cnt) > 90 Then
Set myConn = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command" )
myConn.Open DB_CONNECT_STRING
Set myCommand.ActiveConnection = myConn
myCommand.CommandText = "Insert into PerformanceCounter values('" & StrSrv & "','CPU Usage','" & Cint(CPUUsage/cnt) & "',getdate())"
myCommand.Execute
myConn.Close
End If
End Function
Function GetDiskQueueLegth(strSrv)
Dim objWMIService, Item, Proc
strQuery = "select Name,AvgDiskQueueLength from win32_perfformatteddata_perfdisk_physicaldisk"
Set objWMIService = GetObject("winmgmts:\\" & StrSrv & "\root\cimv2")
Set Item = objWMIService.ExecQuery(strQuery,,48)
For Each Proc In Item
Dim myConn,myCommand
'wscript.echo Proc.Name
'wscript.echo Proc.AvgDiskQueueLength
If Proc.Name = "_Total" and Proc.AvgDiskQueueLength > 2 Then
Set myConn = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command" )
myConn.Open DB_CONNECT_STRING
Set myCommand.ActiveConnection = myConn
myCommand.CommandText = "Insert into PerformanceCounter values('" & StrSrv & "','I/O Queue','" & Proc.AvgDiskQueueLength & "',getdate())"
myCommand.Execute
myConn.Close
End If
Next
End Function
Function GetDiskWritesPerSec(strSrv)
Dim objWMIService,objRefresher,colItems,Proc
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strSrv & "\root\cimv2")
set objRefresher = CreateObject("WbemScripting.SWbemRefresher")
Set colItems = objRefresher.AddEnum _
(objWMIService, "Win32_PerfFormattedData_PerfDisk_PhysicalDisk").objectSet
objRefresher.Refresh
For Each Proc in colItems
If Proc.name = "_Total" and Proc.DiskWritesPerSec Then
IF Cint(1000/Proc.DiskWritesPerSec) > 15 Then
Set myConn = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command" )
myConn.Open DB_CONNECT_STRING
Set myCommand.ActiveConnection = myConn
myCommand.CommandText = "Insert into PerformanceCounter values('" & StrSrv & "','MS/Write','" & Cint(1000/Proc.DiskWritesPerSec) & "',getdate())"
myCommand.Execute
myConn.Close
objRefresher.Refresh
End If
End If
Next
End Function
Function GetDiskReadperSec(strSrv)
Dim objWMIService,objRefresher,colItems,Proc
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strSrv & "\root\cimv2")
set objRefresher = CreateObject("WbemScripting.SWbemRefresher")
Set colItems = objRefresher.AddEnum _
(objWMIService, "Win32_PerfFormattedData_PerfDisk_PhysicalDisk").objectSet
objRefresher.Refresh
For Each Proc in colItems
If Proc.name = "_Total" and Proc.DiskReadsPerSec Then
If Cint(1000/Proc.DiskReadsPerSec) > 15 Then
Set myConn = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command" )
myConn.Open DB_CONNECT_STRING
Set myCommand.ActiveConnection = myConn
myCommand.CommandText = "Insert into PerformanceCounter values('" & StrSrv & "','MS/Read','" & Cint(1000/Proc.DiskReadsPerSec) & "',getdate())"
myCommand.Execute
myConn.Close
objRefresher.Refresh
End If
End If
Next
End Function
Function GetDiskReadBytesPerSec(strSrv)
Dim objWMIService, Item, Proc
strQuery = "select Name,DiskReadBytesPerSec from win32_perfformatteddata_perfdisk_physicaldisk"
Set objWMIService = GetObject("winmgmts:\\" & StrSrv & "\root\cimv2")
Set Item = objWMIService.ExecQuery(strQuery,,48)
For Each Proc In Item
Dim myConn,myCommand
'wscript.echo Proc.Name
'wscript.echo Proc.AvgDiskQueueLength
If Proc.Name = "_Total" Then
Set myConn = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command" )
myConn.Open DB_CONNECT_STRING
Set myCommand.ActiveConnection = myConn
myCommand.CommandText = "Insert into PerformanceCounter values('" & StrSrv & "','RGB/Sec','" & Round((Proc.DiskReadBytesPerSec/1024/1024/1024),3) & "',getdate())"
myCommand.Execute
myConn.Close
End If
Next
End Function
Function GetDiskWriteBytesPerSec(strSrv)
Dim objWMIService, Item, Proc
strQuery = "select Name,DiskWriteBytesPerSec from win32_perfformatteddata_perfdisk_physicaldisk"
Set objWMIService = GetObject("winmgmts:\\" & StrSrv & "\root\cimv2")
Set Item = objWMIService.ExecQuery(strQuery,,48)
For Each Proc In Item
Dim myConn,myCommand
'wscript.echo Proc.Name
'wscript.echo Proc.AvgDiskQueueLength
If Proc.Name = "_Total" Then
Set myConn = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command" )
myConn.Open DB_CONNECT_STRING
Set myCommand.ActiveConnection = myConn
myCommand.CommandText = "Insert into PerformanceCounter values('" & StrSrv & "','WGB/Sec','" & Round((Proc.DiskWriteBytesPerSec/1024/1024/1024),3) & "',getdate())"
myCommand.Execute
myConn.Close
End If
Next
End Function
Function GetRequestPerSec(strSrv)
'On Error Resume Next
Dim objWMIService, Item, Proc
strQuery = "select Name,RequestsPerSec from Win32_PerfFormattedData_ASPNET_ASPNETApplications"
Set objWMIService = GetObject("winmgmts:\\" & StrSrv & "\root\cimv2")
Set Item = objWMIService.ExecQuery(strQuery,,48)
For Each Proc In Item
Dim myConn,myCommand
If Proc.Name = "_Total" and (strSrv = "Server9" or strSrv = "Server14")Then
Set myConn = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command" )
myConn.Open DB_CONNECT_STRING
Set myCommand.ActiveConnection = myConn
myCommand.CommandText = "Insert into PerformanceCounter values('" & StrSrv & "','Req/Sec','" & Proc.RequestsPerSec & "',getdate())"
myCommand.Execute
myConn.Close
End IF
Next
End Function
Regards,
Nirav Gajjar