Differences

This shows you the differences between two versions of the page.

Link to this comparison view

monitors:mssqlperfpack [2010/06/22 08:38] (current)
Line 1: Line 1:
 +====== Microsoft SQL Server Performance Counter Pack ======
 +
 +^ Author | [[ neil_franken@yahoo.com | Neil Franken]] |
 +^ Compatibility | Xymon 4.2+ |
 +^ Requirements | MSSQL Windows VBS|
 +^ Download | None |
 +^ Last Update | 2010-06-18 |
 +
 +===== Description =====
 +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.
 +
 +
 +
 +
 +===== Installation =====
 +=== 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.
 +<hidden onHidden="​Show Graph Definition ⇲" onVisible="​Hide Graph Definition ⇱">​
 +<​code>​
 +  [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
 +</​code>​
 +</​hidden>​
 + 
 +You should now see the graph after a couple of minutes of collecting data.
 +
 + 
 +
 +
 +===== Source =====
 +mssqlperfpack.vbs
 +
 +==== mssqlperfpack.vbs====
 +
 +<hidden onHidden="​Show Code ⇲" onVisible="​Hide Code ⇱">​
 +<​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
 +</​code>​
 +</​hidden>​
 +
 +===== Known  Bugs and Issues =====
 +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
 +
 +===== To Do =====
 +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.
 +
 +
 +===== Credits =====
 +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.
 +
 +===== Changelog =====
 +
 +  * **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)