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