Microsoft SQL Server Performance Counter Pack

Author Neil Franken
Compatibility Xymon 4.2+
Requirements MSSQL Windows VBS
Download None
Last Update 2010-06-18

This VBS Script uses the WMI Interface to read SQL related Performance Counters. These counters can be used by DBA's to establish a baseline for performance as well as provide a early indication of possible hardware upgrades such as the need for more memory etc. These are the performance counters that are monitored:

  • Buffer Cache Hit Ratio
  • Page Life Expectancy
  • Checkpoint Pages/Sec
  • Lazy Writes/Sec
  • Locks Timeouts/sec
  • Number of Deadlocks/sec
  • Page Splits/sec
  • Active Transactions
  • Transactions/sec
  • Logins/sec
  • Logout/sec
  • Active Temp Tables
  • Number of User Connections
  • Cursor Request/sec

This is not a complete list of counters but presents a good set for a baseline. However please keep checking back for updates as I enhance the script over time.

Client side

Place the script on the SQL Server you want to monitor and add a appropriate section to the BBWin.cfg externals section.

Typically it would be configured like this. <load value=“cscript c:\VBSScripts\mssqlperfpack.vbs” timer=“5m” />

Once this is added to the cfg file it should create a sql file in the tmp directory of BBwin for upload. If this is the case it is working.

Server side

While the Xymon server does not require any installation you can add the following to the hobbitserver.cfg to create a graph with some of the more important values on.

1) Adding NCV Definition to the hobbitserver.cfg file
  • Add sql=ncv to the TEST2RRD section of the hobbitserver.cfg file.
  • Add the following definition to the hobbitserver.cfg

NCV_sql=“BufferCacheHitRatio:GAUGE,PageLifeExpectancy:GAUGE ,CheckpointPagesSec:GAUGE ,LazyWritesSec:GAUGE ,LocksTimeoutssec:GAUGE ,NumberofDeadlocksse:GAUGE ,PageSplitssec:GAUGE ,ActiveTransactions:GAUGE ,Transactionssec:GAUGE ,Loginssec:GAUGE ,ActiveTempTables:GAUGE ,NumberofUserConnect:GAUGE ,CursorRequestsec:GAUGE ,Logoutsec:GAUGE”

  • Restart Xymon
2) Adding Graph Definition to the hobbitgraph.cfg file
  • Add the following to the file.

Show Graph Definition ⇲

Hide Graph Definition ⇱

  [sql]
  TITLE SQL Server Performance And Health
  -u 1.0
  YAXIS Number
  DEF:BufferCacheHitRatio=sql.rrd:BufferCacheHitRatio:AVERAGE
  DEF:PageLifeExpectancy=sql.rrd:PageLifeExpectancy:AVERAGE
  DEF:CheckpointPagesSec=sql.rrd:CheckpointPagesSec:AVERAGE
  DEF:LazyWritesSec=sql.rrd:LazyWritesSec:AVERAGE
  DEF:LocksTimeoutssec=sql.rrd:LocksTimeoutssec:AVERAGE
  DEF:NumberofDeadlocksse=sql.rrd:NumberofDeadlocksse:AVERAGE
  DEF:PageSplitssec=sql.rrd:PageSplitssec:AVERAGE
  DEF:ActiveTransactions=sql.rrd:ActiveTransactions:AVERAGE
  DEF:Transactionssec=sql.rrd:Transactionssec:AVERAGE
  DEF:ActiveTempTables=sql.rrd:ActiveTempTables:AVERAGE
  DEF:Loginssec=sql.rrd:Loginssec:AVERAGE
  DEF:Logoutsec=sql.rrd:Logoutsec:AVERAGE
  DEF:NumberofUserConnect=sql.rrd:NumberofUserConnect:AVERAGE
  DEF:CursorRequestsec=sql.rrd:CursorRequestsec:AVERAGE
  VDEF:BufferCacheHitRatiomax=BufferCacheHitRatio,MAXIMUM
  VDEF:BufferCacheHitRatioavg=BufferCacheHitRatio,AVERAGE
  VDEF:BufferCacheHitRatiomin=BufferCacheHitRatio,MINIMUM
  VDEF:BufferCacheHitRatiolast=BufferCacheHitRatio,LAST
  VDEF:PageLifeExpectancymax=PageLifeExpectancy,MAXIMUM
  VDEF:PageLifeExpectancyavg=PageLifeExpectancy,AVERAGE
  VDEF:PageLifeExpectancymin=PageLifeExpectancy,MINIMUM
  VDEF:PageLifeExpectancylast=PageLifeExpectancy,LAST
  VDEF:CheckpointPagesSecmax=CheckpointPagesSec,MAXIMUM
  VDEF:CheckpointPagesSecavg=CheckpointPagesSec,AVERAGE
  VDEF:CheckpointPagesSecmin=CheckpointPagesSec,MINIMUM
  VDEF:CheckpointPagesSeclast=CheckpointPagesSec,LAST
  VDEF:LazyWritesSecmax=LazyWritesSec,MAXIMUM
  VDEF:LazyWritesSecavg=LazyWritesSec,AVERAGE
  VDEF:LazyWritesSecmin=LazyWritesSec,MINIMUM
  VDEF:LazyWritesSeclast=LazyWritesSec,LAST
  VDEF:NumberofDeadlockssemax=NumberofDeadlocksse,MAXIMUM
  VDEF:NumberofDeadlocksseavg=NumberofDeadlocksse,AVERAGE
  VDEF:NumberofDeadlockssemin=NumberofDeadlocksse,MINIMUM
  VDEF:NumberofDeadlocksselast=NumberofDeadlocksse,LAST
  VDEF:LocksTimeoutssecmax=LocksTimeoutssec,MAXIMUM
  VDEF:LocksTimeoutssecavg=LocksTimeoutssec,AVERAGE
  VDEF:LocksTimeoutssecmin=LocksTimeoutssec,MINIMUM
  VDEF:LocksTimeoutsseclast=LocksTimeoutssec,LAST
  VDEF:NumberofDeadlockmax=NumberofDeadlocksse,MAXIMUM
  VDEF:NumberofDeadlockavg=NumberofDeadlocksse,AVERAGE
  VDEF:NumberofDeadlockmin=NumberofDeadlocksse,MINIMUM
  VDEF:NumberofDeadlocklast=NumberofDeadlocksse,LAST
  VDEF:PageSplitssecmax=PageSplitssec,MAXIMUM
  VDEF:PageSplitssecavg=PageSplitssec,AVERAGE
  VDEF:PageSplitssecmin=PageSplitssec,MINIMUM
  VDEF:PageSplitsseclast=PageSplitssec,LAST
  VDEF:ActiveTransactionsmax=ActiveTransactions,MAXIMUM
  VDEF:ActiveTransactionsavg=ActiveTransactions,AVERAGE
  VDEF:ActiveTransactionsmin=ActiveTransactions,MINIMUM
  VDEF:ActiveTransactionslast=ActiveTransactions,LAST
  VDEF:Transactionssecmax=Transactionssec,MAXIMUM
  VDEF:Transactionssecavg=Transactionssec,AVERAGE
  VDEF:Transactionssecmin=Transactionssec,MINIMUM
  VDEF:Transactionsseclast=Transactionssec,LAST
  VDEF:ActiveTempTablesmax=ActiveTempTables,MAXIMUM
  VDEF:ActiveTempTablesavg=ActiveTempTables,AVERAGE
  VDEF:ActiveTempTablesmin=ActiveTempTables,MINIMUM
  VDEF:ActiveTempTableslast=ActiveTempTables,LAST
  VDEF:Loginssecmax=Loginssec,MAXIMUM
  VDEF:Loginssecavg=Loginssec,AVERAGE
  VDEF:Loginssecmin=Loginssec,MINIMUM
  VDEF:Loginsseclast=Loginssec,LAST
  VDEF:Logoutsecmax=Logoutsec,MAXIMUM
  VDEF:Logoutsecavg=Logoutsec,AVERAGE
  VDEF:Logoutsecmin=Logoutsec,MINIMUM
  VDEF:Logoutseclast=Logoutsec,LAST
  VDEF:NumberofUserConnectmax=NumberofUserConnect,MAXIMUM
  VDEF:NumberofUserConnectavg=NumberofUserConnect,AVERAGE
  VDEF:NumberofUserConnectmin=NumberofUserConnect,MINIMUM
  VDEF:NumberofUserConnectlast=NumberofUserConnect,LAST
  VDEF:CursorRequestsecmax=CursorRequestsec,MAXIMUM
  VDEF:CursorRequestsecavg=CursorRequestsec,AVERAGE
  VDEF:CursorRequestsecmin=CursorRequestsec,MINIMUM
  VDEF:CursorRequestseclast=CursorRequestsec,LAST
  COMMENT:            
  COMMENT:\t\t\tMaximum  
  COMMENT:\tAverage 
  COMMENT:\tMinimum 
  COMMENT:\tCurrent \l
  LINE2:BufferCacheHitRatio#000000:Buffer Cache Hit Ratio 
  GPRINT:BufferCacheHitRatiomax:\t%6.2lf %S
  GPRINT:BufferCacheHitRatioavg:\t%6.2lf %S
  GPRINT:BufferCacheHitRatiomin:\t%6.2lf %S
  GPRINT:BufferCacheHitRatiolast:\t%6.2lf %S\l
  LINE2:LazyWritesSec#b527d4:Lazy Writes/Sec        
  GPRINT:LazyWritesSecmax:\t%6.0lf %S
  GPRINT:LazyWritesSecavg:\t%6.0lf %S
  GPRINT:LazyWritesSecmin:\t%6.0lf %S
  GPRINT:LazyWritesSeclast:\t%6.0lf %S\l
  LINE2:NumberofDeadlocksse#ff6c00:Deadlocks/Sec          
  GPRINT:NumberofDeadlockmax:\t%6.0lf %S
  GPRINT:NumberofDeadlockavg:\t%6.0lf %S
  GPRINT:NumberofDeadlockmin:\t%6.0lf %S
  GPRINT:NumberofDeadlocklast:\t%6.0lf %S\l
  LINE2:ActiveTransactions#12ff00:Active Transactions    
  GPRINT:ActiveTransactionsmax:\t%6.0lf %S
  GPRINT:ActiveTransactionsavg:\t%6.0lf %S
  GPRINT:ActiveTransactionsmin:\t%6.0lf %S
  GPRINT:ActiveTransactionslast:\t%6.0lf %S\l
  LINE2:ActiveTempTables#0000e9:Active Temp Tables     
  GPRINT:ActiveTempTablesmax:\t%6.0lf %S
  GPRINT:ActiveTempTablesavg:\t%6.0lf %S
  GPRINT:ActiveTempTablesmin:\t%6.0lf %S
  GPRINT:ActiveTempTableslast:\t%6.0lf %S\l
  LINE2:NumberofUserConnect#ff0000:Users Connected        
  GPRINT:NumberofUserConnectmax:\t%6.0lf %S
  GPRINT:NumberofUserConnectavg:\t%6.0lf %S
  GPRINT:NumberofUserConnectmin:\t%6.0lf %S
  GPRINT:NumberofUserConnectlast:\t%6.0lf %S\l
  #LINE2:PageLifeExpectancy#b527d4:Page Life Expectancy   
  COMMENT:Data Not On Graph\l
  COMMENT:Page Life Expectancy     
  GPRINT:PageLifeExpectancymax:\t%6.0lf %S
  GPRINT:PageLifeExpectancyavg:\t%6.0lf %S
  GPRINT:PageLifeExpectancymin:\t%6.0lf %S
  GPRINT:PageLifeExpectancylast:\t%6.0lf %S\l
  #LINE2:Transactionssec#1ebdf8:Transactions/Sec       
  COMMENT:Transactions/Sec         
  GPRINT:Transactionssecmax:\t%6.0lf %S
  GPRINT:Transactionssecavg:\t%6.0lf %S
  GPRINT:Transactionssecmin:\t%6.0lf %S
  GPRINT:Transactionsseclast:\t%6.0lf %S\l
  #LINE2:LocksTimeoutssec#00FF66:Lock Timeout/Sec       
  COMMENT:Lock Timeout/Sec         
  GPRINT:LocksTimeoutssecmax:\t%6.0lf %S
  GPRINT:LocksTimeoutssecavg:\t%6.0lf %S
  GPRINT:LocksTimeoutssecmin:\t%6.0lf %S
  GPRINT:LocksTimeoutsseclast:\t%6.0lf %S\l
  #LINE2:CheckpointPagesSec#1ebdf8:Checkpoint Pages/Sec   
  COMMENT:Checkpoint Pages/Sec     
  GPRINT:CheckpointPagesSecmax:\t%6.0lf %S
  GPRINT:CheckpointPagesSecavg:\t%6.0lf %S
  GPRINT:CheckpointPagesSecmin:\t%6.0lf %S
  GPRINT:CheckpointPagesSeclast:\t%6.0lf %S\l
  #LINE2:PageSplitssec#00ffd2:Page Splits/Sec        
  COMMENT:Page Splits/Sec          
  GPRINT:PageSplitssecmax:\t%6.0lf %S
  GPRINT:PageSplitssecavg:\t%6.0lf %S
  GPRINT:PageSplitssecmin:\t%6.0lf %S
  GPRINT:PageSplitsseclast:\t%6.0lf %S\l
  #LINE2:Loginssec#0060ff:Login/Sec
  COMMENT:Logins/Sec               
  GPRINT:Loginssecmax:\t%6.0lf %S
  GPRINT:Loginssecavg:\t%6.0lf %S
  GPRINT:Loginssecmin:\t%6.0lf %S
  GPRINT:Loginsseclast:\t%6.0lf %S\l
  #LINE2:Logoutsec#a1b4d4:Logouts/Sec            
  COMMENT:Logout/Sec               
  GPRINT:Logoutsecmax:\t%6.0lf %S
  GPRINT:Logoutsecavg:\t%6.0lf %S
  GPRINT:Logoutsecmin:\t%6.0lf %S
  GPRINT:Logoutseclast:\t%6.0lf %S\l
  #LINE2:CursorRequestsec#4aa2f3:Cursor Request/Sec     
  COMMENT:Cursor Request/Sec       
  GPRINT:CursorRequestsecmax:\t%6.0lf %S
  GPRINT:CursorRequestsecavg:\t%6.0lf %S
  GPRINT:CursorRequestsecmin:\t%6.0lf %S
  GPRINT:CursorRequestseclast:\t%6.0lf %S\l

You should now see the graph after a couple of minutes of collecting data.

mssqlperfpack.vbs

mssqlperfpack.vbs

Show Code ⇲

Hide Code ⇱

' SQL DBA Performance Pack Script Beta.
' Author: Neil Franken
' email: neil_franken@yahoo.com
' Description: 
' This script collects some SQL related performance counters. It is based and inspired by the 
' bbsql-server script that was written 03/2002 by Jason Benassi and modified by Bart Gillis.
'
' The information is used to get a perfomance baseline and monitor over time what performance 
' tweaks can be made to your SQL server. It will be updated and become more comprehensive over time
' so please check in every once in a while for a update.
' You are welcome to use, modify, mangle and do what you please with this script just please
' keep me in the loop about bugs,updates and or improvements.
' 
' IMPORTANT NOTES:
' Before running this script please do the following. Open a command prompt on the SQL server where the script
' runs. Run the following command wmiadap/f.
' Open the services manager screen and restart/start the WMI performance adapter service. This service must be running
' to read the performance counters via the WMI interface.
'
'
' KNOWN ISSUE:
' I encountered a problem where the CPU was 64 Bit but the installed OS was 32Bit. This caused the registry read to fail.
' If you have a 32 Bit OS running on a 64 bit system yuo will have to change the script to point to the BBWin\tmp folder 
' as the script will attempt to read the value from a registry key that dont exist. Also comment out the ProcessorCheck function.
'


' Version 0.5 Released: 18/06/2010
'

strAlarmState = "green"
strTestName = "sql" 'this Is the filename i.e. column name in xymon.






strOutput = ""
Set ws = WScript.CreateObject("WScript.Shell")
Set ws = WScript.CreateObject("WScript.Shell")
ProcessorCheck
If Proc = "x86" Then
	'            extPath = "c:\"
	extPath = ws.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\BBWin\tmppath") 'Registry key that stores the ext script path on 32bit Architecture
'	WScript.Echo Proc
Else
	'            extPath = "c:\"  
	extPath = ws.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\BBWin\tmppath") 'Registry key that stores the ext script path on 64bit Architecture
'	WScript.Echo Proc
End If

' Usage of the CheckValue,CheckReserveValue Functions:
' CheckValue takes the Performance Counter value,a description which appears on Xymon,the warning value and the Alarm value and does a check for values above the warn and alarm thresholds.
' CheckReverseValue takes the Performance Counter value,a description which appears on Xymon,the warning value and the Alarm value and does a check for values below the warn and alarm thresholds.
'
' Usage of the GetWMPercent and GetWMIValue functions:
' Both these functions takes the following parameters
'	1) The WMI Class Name i.e. Win32_PerfFormattedData_MSSQLSERVER_SQLServerBuffermanager this is a collection of performance counter instances related to the class.
'	2) Instance for the peformance counter. Some performance counters can be specific to a instance i.e. we could have performance counters
'      For the masterdb, the pubs db and so on. If you want to monitor a specific database you can do so by specifying the name. If you want 
'	   to monitor the entire server use the _Total instance. Other performance counters are spcific to the entire install such as Buffercachehitratio
'	   and you have to specify the @ for these to work. If you need some clarification use windows performance monitor to see the instance names for a counter
'	   if they are available.
'	3) The actuall counter name you want to get for the instance specified in parameter 2.
' The GetWMPercent function has a additional paramater called strBaseObject this is used in the calculations to get the percentage value for the counter.
'
'Handy List Of SQL Performance Counter Classes.
'____________________________________________________________
'"Win32_PerfFormattedData_MSSQLSERVER_SQLServerAccessMethods"
'"Win32_PerfRawData_MSSQLSERVER_SQLServerAccessMethods"
'"Win32_PerfFormattedData_MSSQLSERVER_SQLServerBackupDevice"
'"Win32_PerfRawData_MSSQLSERVER_SQLServerBackupDevice"
'"Win32_PerfFormattedData_MSSQLSERVER_SQLServerBrokerActivation"
'"Win32_PerfRawData_MSSQLSERVER_SQLServerBrokerActivation"
'"Win32_PerfFormattedData_MSSQLSERVER_SQLServerBrokerDBMTransport"
'"Win32_PerfRawData_MSSQLSERVER_SQLServerBrokerDBMTransport"
'"Win32_PerfFormattedData_MSSQLSERVER_SQLServerBrokerStatistics"
'"Win32_PerfRawData_MSSQLSERVER_SQLServerBrokerStatistics"
'"Win32_PerfFormattedData_MSSQLSERVER_SQLServerBufferManager"
'"Win32_PerfRawData_MSSQLSERVER_SQLServerBufferManager"
'"Win32_PerfFormattedData_MSSQLSERVER_SQLServerBufferNode"
'"Win32_PerfRawData_MSSQLSERVER_SQLServerBufferNode"
'"Win32_PerfFormattedData_MSSQLSERVER_SQLServerBufferPartition"
'"Win32_PerfRawData_MSSQLSERVER_SQLServerBufferPartition"
'"Win32_PerfFormattedData_MSSQLSERVER_SQLServerCatalogMetadata"
'"Win32_PerfRawData_MSSQLSERVER_SQLServerCatalogMetadata"
'"Win32_PerfFormattedData_MSSQLSERVER_SQLServerCLR"
'"Win32_PerfRawData_MSSQLSERVER_SQLServerCLR"
'"Win32_PerfFormattedData_MSSQLSERVER_SQLServerCursorManagerbyType"
'"Win32_PerfRawData_MSSQLSERVER_SQLServerCursorManagerbyType"
'"Win32_PerfFormattedData_MSSQLSERVER_SQLServerCursorManagerTotal"
'"Win32_PerfRawData_MSSQLSERVER_SQLServerCursorManagerTotal"
'"Win32_PerfFormattedData_MSSQLSERVER_SQLServerDatabaseMirroring"
'"Win32_PerfRawData_MSSQLSERVER_SQLServerDatabaseMirroring"
'"Win32_PerfFormattedData_MSSQLSERVER_SQLServerDatabases"
'"Win32_PerfRawData_MSSQLSERVER_SQLServerDatabases"
'"Win32_PerfFormattedData_MSSQLSERVER_SQLServerExecStatistics"
'"Win32_PerfRawData_MSSQLSERVER_SQLServerExecStatistics"
'"Win32_PerfFormattedData_MSSQLSERVER_SQLServerGeneralStatistics"
'"Win32_PerfRawData_MSSQLSERVER_SQLServerGeneralStatistics"
'"Win32_PerfFormattedData_MSSQLSERVER_SQLServerLatches"
'"Win32_PerfRawData_MSSQLSERVER_SQLServerLatches"
'"Win32_PerfFormattedData_MSSQLSERVER_SQLServerLocks"
'"Win32_PerfRawData_MSSQLSERVER_SQLServerLocks"
'"Win32_PerfFormattedData_MSSQLSERVER_SQLServerMemoryManager"
'"Win32_PerfRawData_MSSQLSERVER_SQLServerMemoryManager"
'"Win32_PerfFormattedData_MSSQLSERVER_SQLServerPlanCache"
'"Win32_PerfRawData_MSSQLSERVER_SQLServerPlanCache"
'"Win32_PerfFormattedData_MSSQLSERVER_SQLServerReplicationAgents"
'"Win32_PerfRawData_MSSQLSERVER_SQLServerReplicationAgents"
'"Win32_PerfFormattedData_MSSQLSERVER_SQLServerReplicationDist"
'"Win32_PerfRawData_MSSQLSERVER_SQLServerReplicationDist"
'"Win32_PerfFormattedData_MSSQLSERVER_SQLServerReplicationLogreader"
'"Win32_PerfRawData_MSSQLSERVER_SQLServerReplicationLogreader"
'"Win32_PerfFormattedData_MSSQLSERVER_SQLServerReplicationMerge"
'"Win32_PerfRawData_MSSQLSERVER_SQLServerReplicationMerge"
'"Win32_PerfFormattedData_MSSQLSERVER_SQLServerReplicationSnapshot"
'"Win32_PerfRawData_MSSQLSERVER_SQLServerReplicationSnapshot"
'"Win32_PerfFormattedData_MSSQLSERVER_SQLServerSQLErrors"
'"Win32_PerfRawData_MSSQLSERVER_SQLServerSQLErrors"
'"Win32_PerfFormattedData_MSSQLSERVER_SQLServerSQLStatistics"
'"Win32_PerfRawData_MSSQLSERVER_SQLServerSQLStatistics"
'"Win32_PerfFormattedData_MSSQLSERVER_SQLServerTransactions"
'"Win32_PerfRawData_MSSQLSERVER_SQLServerTransactions"
'"Win32_PerfFormattedData_MSSQLSERVER_SQLServerUserSettable"
'"Win32_PerfRawData_MSSQLSERVER_SQLServerUserSettable"
'"Win32_PerfFormattedData_MSSQLSERVER_SQLServerWaitStatistics"
'"Win32_PerfRawData_MSSQLSERVER_SQLServerWaitStatistics"
'"Win32_PerfFormattedData_MSSQLServerOLAPService_MSAS2005Cache"
'"Win32_PerfRawData_MSSQLServerOLAPService_MSAS2005Cache"
'"Win32_PerfFormattedData_MSSQLServerOLAPService_MSAS2005Connection"
'"Win32_PerfRawData_MSSQLServerOLAPService_MSAS2005Connection"
'"Win32_PerfFormattedData_MSSQLServerOLAPService_MSAS2005DataMiningModelProcessing"
'"Win32_PerfRawData_MSSQLServerOLAPService_MSAS2005DataMiningModelProcessing"
'"Win32_PerfFormattedData_MSSQLServerOLAPService_MSAS2005DataMiningPrediction"
'"Win32_PerfRawData_MSSQLServerOLAPService_MSAS2005DataMiningPrediction"
'"Win32_PerfFormattedData_MSSQLServerOLAPService_MSAS2005Locks"
'"Win32_PerfRawData_MSSQLServerOLAPService_MSAS2005Locks"
'"Win32_PerfFormattedData_MSSQLServerOLAPService_MSAS2005MDX"
'"Win32_PerfRawData_MSSQLServerOLAPService_MSAS2005MDX"
'"Win32_PerfFormattedData_MSSQLServerOLAPService_MSAS2005Memory"
'"Win32_PerfRawData_MSSQLServerOLAPService_MSAS2005Memory"
'"Win32_PerfFormattedData_MSSQLServerOLAPService_MSAS2005ProactiveCaching"
'"Win32_PerfRawData_MSSQLServerOLAPService_MSAS2005ProactiveCaching"
'"Win32_PerfFormattedData_MSSQLServerOLAPService_MSAS2005ProcAggregations"
'"Win32_PerfRawData_MSSQLServerOLAPService_MSAS2005ProcAggregations"
'"Win32_PerfFormattedData_MSSQLServerOLAPService_MSAS2005Processing"
'"Win32_PerfRawData_MSSQLServerOLAPService_MSAS2005Processing"
'"Win32_PerfFormattedData_MSSQLServerOLAPService_MSAS2005ProcIndexes"
'"Win32_PerfRawData_MSSQLServerOLAPService_MSAS2005ProcIndexes"
'"Win32_PerfFormattedData_MSSQLServerOLAPService_MSAS2005StorageEngineQuery"
'"Win32_PerfRawData_MSSQLServerOLAPService_MSAS2005StorageEngineQuery"
'"Win32_PerfFormattedData_MSSQLServerOLAPService_MSAS2005Threads"
'"Win32_PerfRawData_MSSQLServerOLAPService_MSAS2005Threads"
'"Win32_PerfFormattedData_SQLSERVERAGENT_SQLAgentAlerts"
'"Win32_PerfRawData_SQLSERVERAGENT_SQLAgentAlerts"
'"Win32_PerfFormattedData_SQLSERVERAGENT_SQLAgentJobs"
'"Win32_PerfRawData_SQLSERVERAGENT_SQLAgentJobs"
'"Win32_PerfFormattedData_SQLSERVERAGENT_SQLAgentJobSteps"
'"Win32_PerfRawData_SQLSERVERAGENT_SQLAgentJobSteps"
'"Win32_PerfFormattedData_SQLSERVERAGENT_SQLAgentStatistics"
'"Win32_PerfRawData_SQLSERVERAGENT_SQLAgentStatistics"


' SQL Server Test starts here
strOutput = strOutput & vbCrLf & "SQL Server Information:" & vbCrLf
'Buffer Cache Hit Ration
strOutput = strOutput & CheckReverseValue(GetWMIValue("Win32_PerfFormattedData_MSSQLSERVER_SQLServerBufferManager","@","Buffercachehitratio"),"Buffer Cache Hit Ratio",90,85)

strOutput = strOutput & CheckReverseValue(GetWMIValue("Win32_PerfFormattedData_MSSQLSERVER_SQLServerBuffermanager","@","Pagelifeexpectancy"),"Page Life Expectancy",10, 2)
strOutput = strOutput & CheckValue(GetWMIValue("Win32_PerfFormattedData_MSSQLSERVER_SQLServerBuffermanager","@","CheckpointpagesPersec"),"Checkpoint Pages/Sec",100, 200)
strOutput = strOutput & CheckValue(GetWMIValue("Win32_PerfFormattedData_MSSQLSERVER_SQLServerBuffermanager","@","LazywritesPersec"),"Lazy Writes/Sec",100, 200)

'Lock Information
strOutput = strOutput & CheckValue(GetWMIValue("Win32_PerfFormattedData_MSSQLSERVER_SQLServerLocks.Name","""_Total""","LockTimeoutsPersec"),"Locks Timeouts/sec",50, 100)
strOutput = strOutput & CheckValue(GetWMIValue("Win32_PerfFormattedData_MSSQLSERVER_SQLServerLocks.Name","""_Total""","NumberofDeadlocksPersec"),"Number of Deadlocks/sec",2, 5)

'Page Splits Per second
strOutput = strOutput & CheckValue(GetWMIValue("Win32_PerfFormattedData_MSSQLSERVER_SQLServerAccessMethods","@","PageSplitsPersec"),"Page Splits/sec",100, 300)
'SQL Server Databases
strOutput = strOutput & CheckValue(GetWMIValue("Win32_PerfFormattedData_MSSQLSERVER_SQLServerDatabases.Name","""_Total""","ActiveTransactions"),"Active Transactions",1000, 3000)
strOutput = strOutput & CheckValue(GetWMIValue("Win32_PerfFormattedData_MSSQLSERVER_SQLServerDatabases.Name","""_Total""","TransactionsPersec"),"Transactions/sec",1000, 5000)
'SQL General Statistics
strOutput = strOutput & CheckValue(GetWMIValue("Win32_PerfFormattedData_MSSQLSERVER_SQLServerGeneralStatistics","@","LoginsPersec"),"Logins/sec",100, 300)
strOutput = strOutput & CheckValue(GetWMIValue("Win32_PerfFormattedData_MSSQLSERVER_SQLServerGeneralStatistics","@","LogoutsPersec"),"Logout/sec",100, 300)
strOutput = strOutput & CheckValue(GetWMIValue("Win32_PerfFormattedData_MSSQLSERVER_SQLServerGeneralStatistics","@","ActiveTempTables"),"Active Temp Tables",500, 1500)
strOutput = strOutput & CheckValue(GetWMIValue("Win32_PerfRawData_MSSQLSERVER_SQLServerGeneralStatistics","@","UserConnections"),"Number of User Connections",100, 200)
'SQL Cursor Information
strOutput = strOutput & CheckValue(GetWMIValue("Win32_PerfFormattedData_MSSQLSERVER_SQLServerCursorManagerbyType.Name","""_Total""","CursorRequestsPersec"),"Cursor Request/sec",500, 1500)



' Write the file for BB
WriteFile extPath, strTestName, strAlarmState, strOutput

'===========================================================
' FUNCTIONS and SUBS start here



Sub ProcessorCheck ()
	Dim  WshShell, WshSysEnv
	
	Set WshShell = WScript.CreateObject("WScript.Shell")
	Set WshSysEnv = WshShell.Environment("SYSTEM")
	Proc = WshSysEnv("PROCESSOR_ARCHITECTURE")
End Sub

' This is used to get a percentage value from WMI. It requires the value and the base objects.
' It then returns the percentage
Function GetWMIPercent(strCollection,strInstance,strObject,strBaseObject)
	Set counterCollection = GetObject("winmgmts:" & strCollection & "=" & strInstance)
	For Each cntproperty In counterCollection.properties_
		If cntproperty.name = strObject Then
			iObjectValue = CDbl(cntproperty)
		ElseIf cntproperty.name = strBaseObject Then
			iObjectBaseValue =CDbl(cntproperty)
		End If
	Next
	If iObjectBaseValue = 0 Then
		GetWMIPercent = "N/A"
	Else
		GetWMIPercent = Round(CDbl(iObjectValue) / CDbl(iObjectBaseValue) * CDbl(100),0)
	End If
End Function

' This is used to pull a value from WMI.
Function GetWMIValue(strCollection,strInstance,strObject)
	Set counterCollection = GetObject("winmgmts:" & strCollection & "=" & strInstance)
	For Each cntproperty In counterCollection.properties_
		If cntproperty.name = strObject Then
			iObjectValue = CDbl(cntproperty)
		End If
	Next
	GetWMIValue = CDbl(iObjectValue)
End Function

' This is used to check the actual value against the warning and alarm.
Function CheckValue(iObjectValue,strObjectDesc,iWarnValue,iAlarmValue)
	If iWarnValue > iAlarmValue Then
		CheckValue = "&red" & " " & strObjectDesc & ":" & vbTab & "Object is Misconfigured"
		If strAlarmState <> "red" Then
			strAlarmState = "red"
		End If
	Else
		If CDbl(iObjectValue) > CDbl(iWarnValue) Then
			If  CDbl(iObjectValue) >  CDbl(iAlarmValue) Then
				CheckValue = "&red" & " " & strObjectDesc & ":" & vbTab & iObjectValue & vbCrLf
				SetAlarmStatus "red"
			Else
				CheckValue = "&yellow" & " " & strObjectDesc & ":" & vbTab & iObjectValue & vbCrLf
				'CheckValue = "&yellow" &  strObjectDesc &  vbTab & iObjectValue & vbCrLf
				SetAlarmStatus "yellow"
			End If
		Else
			CheckValue = "&green" & " " & strObjectDesc & ":" & vbTab & iObjectValue & vbCrLf
			'CheckValue = "&green"  & strObjectDesc & vbTab & iObjectValue & vbCrLf
		End If
	End If
End Function

Function ShowDriveList
	Dim fso, d, dc, s, n
	Set fso = CreateObject("Scripting.FileSystemObject")
	Set dc = fso.Drives
	For Each d In dc
		n = ""
		s = s & d.DriveLetter & " - "
		If d.DriveType = 3 Then
			n = d.ShareName
		ElseIf d.IsReady Then
			n = d.VolumeName
		Else
			n = "[Drive not ready]"
		End If
		s = s & n & "<BR>"
	Next
	ShowDriveList = s
End Function
' This is used to check the actual value against the warning and alarm.
' This one the alarm will be a lower value than the warning. (Values Decrease rather than increase)
Function CheckReverseValue(iObjectValue,strObjectDesc,iWarnValue,iAlarmValue)
	If  CDbl(iWarnValue) <  CDbl(iAlarmValue) Then
		CheckReverseValue = "&red" & " " & strObjectDesc & ":" & vbTab & "Object is Misconfigured"
		If strAlarmState <> "red" Then
			strAlarmState = "red"
		End If
	Else
		If  CDbl(iObjectValue) <  CDbl(iWarnValue) Then
			If  CDbl(iObjectValue) <  CDbl(iAlarmValue) Then
				CheckReverseValue = "&red" & " " & strObjectDesc & ":" & vbTab & iObjectValue & vbCrLf
				SetAlarmStatus "red"
			Else
				CheckReverseValue = "&yellow" & " " & strObjectDesc & ":" & vbTab & iObjectValue & vbCrLf
				SetAlarmStatus "yellow"
			End If
		Else
			CheckReverseValue = "&green" & " " & strObjectDesc & ":" & vbTab & iObjectValue & vbCrLf
		End If
	End If
End Function


' This is called to set the overall alarm status.
Sub SetAlarmStatus(strnewAlarmState)
	If strnewAlarmState = "red" Then
		strAlarmState = strnewAlarmState
	ElseIf strnewAlarmState = "yellow" Then
		If strAlarmState <> "red" Then
			strAlarmState = strnewAlarmState
		End If
	End If
End Sub


' This SUB is used for outputting the file to the external's directory in bb
Sub WriteFile(strExtPath, strTestName, strAlarmState, strOutput)
	Set fso = CreateObject("Scripting.FileSystemObject")
	strOutput = strAlarmState & " " & Date & " " & Time & vbCrLf & vbCrLf & strOutput & vbCrLf
	Set f = fso.OpenTextFile(strExtPath & "\" & strTestName , 8 , True)
	f.Write strOutput
	f.Close
	Set fso = Nothing
End Sub

Two issues that can potentially cause problems:

  • Running a 32 Bit Windows on a 64 Bit CPU(It happens apparently). This causes the script to look for the HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\BBWin\tmppath registry key. This does not exist in a 32 windows version and the script will bomb. To fix comment out the Processor check and hard code(yuck) the bath to the BBwin\tmp folder.
  • When running the script it throws a null exception which looks something like C:\Program Files (x86)\BBWin\ext\SQLNewWay.vbs(103, 2) (null). To fix this problem open a command prompt and execute the following command wmiadap/f. Open the administrative tools under control panel and run the windows services manager. Right at the bottom of the list there is a WMI Performance Adapter service. Restart the service if started, if the service is not running please start it.
  • SQL 2008 There seems to be a problem with some of the counter specifically Buffercachehitratio. This was fixed in the release on 2010-06-21

Well this pack can be more comprehensive. In this inital release I hope to spark some debate under the DBA's on the list. My goal is to have a performance pack to help in identifying performance related issues and also provide us with information to provide to the accountants when we need more hardware.

I borrowed some code from: Bart Gillis Jason Benassi

Most of the other stuff Neil Franken

As special thanks to Walter Van Loon for being brave enough to step up and test the script with his DBA's.

  • 2010-06-18
    • Initial release
  • 2010-06-21
    • Updated the code to work with SQL 2008 Instances as well.
    • Using Win32_PerfFormattedData_MSSQLSERVER_SQLServerBufferManager class to read values.
    • Code contributed by Walter Van Loon.
  • monitors/mssqlperfpack.txt
  • Last modified: 2010/06/22 08:38
  • (external edit)