====== 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. 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. [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. ===== Source ===== mssqlperfpack.vbs ==== mssqlperfpack.vbs==== ' 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 & "
" 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 ===== 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.