| |
— | monitors:mssqlperfpack [2010/06/22 08:38] (current) – created - external edit 127.0.0.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. |
| |
| |