Hey everyone I wanted to post some code I had sometime back when on how I pulled SQL stats from a SQL Server using VB.NET and ASP.NET. The code that will be in this post is actually part of a class that handles the SQL stats, but you can incorporate it however you would like. There are a few things required for this.
1. This is written in .NET 2.0
2. You need to add Imports System.Diagnostics to your class or aspx page
3. You also have to have the rights to pull system stats from your SQL Server. What this means is that you either need to enable Indentity Impersonate on the web application and make sure whoever uses the app has rights, or run the web application under an Application Pool in IIS that has access to the SQL Servers.
4. You'll see a variable in this class Me._Server. Replace this with the server name you are trying to pull stats from or create a variable names _Server as a String
5. Add a reference to the COM object Microsoft SQLDMO Object Library
Ok so here is some code that should get you by for a simple SQL Monitoring tool. Again you can use this wherever you want in your app. I had mine in a seperate class.
======================================================
Private Function getSQLStatus() As String
Dim objSrv As New SQLDMO.SQLServer
objSrv.LoginSecure = True
objSrv.Connect(Me._Server)
Dim tmpStatus As String = String.Empty
Select Case objSrv.Status
Case Is = SQLDMO.SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Continuing
tmpStatus = "Continuing"
Case Is = SQLDMO.SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Paused
tmpStatus = "Paused"
Case Is = SQLDMO.SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Pausing
tmpStatus = "Pausing"
Case Is = SQLDMO.SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Running
tmpStatus = "Running"
Case Is = SQLDMO.SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Starting
tmpStatus = "Starting"
Case Is = SQLDMO.SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Stopped
tmpStatus = "Stopped"
Case Is = SQLDMO.SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Stopping
tmpStatus = "Stopping"
Case Is = SQLDMO.SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Unknown
tmpStatus = "Unknown"
End Select
objSrv.Close()
Return tmpStatus
End Function
Private Function getSQLVersion() As String
Dim objSrv As New SQLDMO.SQLServer
objSrv.LoginSecure = True
objSrv.Connect(Me._Server)
Dim tmpString As String = objSrv.VersionString.Substring(0, 39)
objSrv.Close()
Return tmpString
End Function
Private Function getSQLDBCount() As String
Dim tmpDBCount As String
Dim objSrv As New SQLDMO.SQLServer
objSrv.LoginSecure = True
objSrv.Connect(Me._Server)
tmpDBCount = objSrv.Databases.Count
objSrv.Close()
Return tmpDBCount
End Function
Private Function getSQLCurrCons() As String
Dim tmpConns As String
Dim tmpVal As New PerformanceCounter("SQLServer:General Statistics", "User Connections", Nothing, Me._Server)
tmpConns = tmpVal.RawValue
Return tmpConns
End Function
Private Function getSQLActiveTransSec() As String
Dim sample1 As CounterSample
Dim sample2 As CounterSample
Dim tmpTrans2 As Double
Dim cntVal As New PerformanceCounter("SQLServer:Databases", "Transactions/sec", "_Total", Me._Server)
sample1 = cntVal.NextSample()
System.Threading.Thread.Sleep(1000)
sample2 = cntVal.NextSample()
tmpTrans2 = CounterSample.Calculate(sample1, sample2)
tmpTrans2 = System.Math.Round(tmpTrans2, 0)
Return tmpTrans2.ToString()
End Function
Private Function getSQLMemGrants() As String
Dim cntVal As New PerformanceCounter("SQLServer:Memory Manager", "Memory Grants Pending", Nothing, Me._Server)
Return cntVal.NextValue()
End Function
Private Function getSQLTotalMem() As String
Dim cntVal As New PerformanceCounter("SQLServer:Memory Manager", "Total Server Memory (KB)", Nothing, Me._Server)
Return FormatNumber(((UInt64.Parse(cntVal.NextValue.ToString()) / 1024)), 0)
End Function
Private Function getSQLAvgLockWaitTime() As String
Dim cntVal As New PerformanceCounter("SQLServer:Locks", "Average Wait Time (ms)", "_Total", Me._Server)
Return FormatNumber(UInt64.Parse(cntVal.NextValue.ToString()), 0)
End Function
Private Function getSQLBufferCacheRatio() As String
Dim cntVal As New PerformanceCounter("SQLServer:Buffer Manager", "Buffer cache hit ratio", Nothing, Me._Server)
Return FormatNumber(cntVal.NextValue(), 0)
End Function
Private Function getSQLLocks_Sec() As String
Dim cntVal As New PerformanceCounter("SQLServer:Locks", "Lock Waits/sec", "_Total", Me._Server)
Return cntVal.NextValue()
End Function
==================================================
Again hope this helps those that need to gather sql stats in a quick manner or need to monitor a sql server for stat purposes / high overview of usage.
Be the first to rate this post
- Currently 0/5 Stars.
- 1
- 2
- 3
- 4
- 5