Differences

no way to compare when less than two revisions

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

@@ 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.