' 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