Reading PerfMon Counters in T-SQL with CLR

  • Article

A collegue of mine asked me if it is possible to read out PerfMon counters that are not in the sys.dm_os_performance_counters DMV. I searched everywhere but the only possibility I could think of is by using a CLR user defined function.

So I wrote an assembly (I'm still a VB programmer, I never bothered to learn C#. And I'm never going to as long as VB.Net exists J ):

 

The code is fairly simple:

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

 

Partial
Public
Class UserDefinedFunctions

    <Microsoft.SqlServer.Server.SqlFunction()> _

    Public
Shared
Function ReadPerfMonCounter(ByVal Category As
String, ByVal Counter As
String, ByVal Instance As
String) As SqlString

        '******************************************************************************

        '* Disclaimer: I'm a lousy programmer!!!!                                     *

        '* ****************************************************************************

 

        Dim sTemp As
String

        Try

            Dim perfCounter As
New System.Diagnostics.PerformanceCounter(Category, Counter, Instance, "localhost")

 

            'Somehow it won't return a result on the first NextValue call.

            'Never mind: we just call it 2 times :).

            sTemp = perfCounter.NextValue().ToString

 

            'And wait a little while, but now she's ready to go.

            Threading.Thread.Sleep(1000)

 

            Return
New SqlString(perfCounter.NextValue.ToString)

        Catch ex As Exception

            Return
New SqlString(ex.Message)

        End
Try

 

    End
Function

End
Class

 

The only problem I had was the problem that the NextValue method of the perfCounter didn't return anything but 0 on its first call. That's why I call it 2 times now.

Once compiled you can deploy this assembly on your server. If you don't have .Net but want the assembly, just mail me.

I deployed it to the AdventureWorksTest database, which is a copy of the regular AdventureWorks database.

 

If you deploy it make sure your database has TRUSTWORTHY ON because I didn't sign the assembly. This assembly needs unrestricted access. That's unsafe on SQL 2005.

 

When you have done all that you can use a select statement like:

SELECT
AdventureWorksTest.dbo.ReadPerfMonCounter('PhysicalDisk','% Idle Time','0 C: D: E:');

 

That's all. Error handling could be better by the way. When you make a typo in your counter you'll wait forever instead of getting an error.

If you don't like the unsafe setting on the assembly you could transfer that part to a class library and reference it from your .NET stored procedure. Plenty opportunities left….

I didn't bother on execution context and all that. On my machine the service account is a local admin. You might need some impersonification code if not.

 

Regards,

Menzo