Accessing OS performance counters from tsql through CLR, WMI, and WQL

A cool use of WMI data from within SQL that I have found is getting to OS perfmon data. There are several ways of doing it. I would say the best practice would be CLR if you need it in real time or SSIS if you are logging to a table for reporting purposes. You can also hit it through xp_cmdshell and powershell. This is what I do when I need it quick.

In this blog, we will look at an example of doing this in CLR.  We will be using % processor time but any perform counter is available. A list is here.

DISCLAIMER: I am definitely not a CLR guru. If you know of a better way to code this, please let me know.

Here are some uses. The first two I have in production.

• A server dashboard with reporting services.
• A resource governor to conditionally execute tasks like index\stats maintenance.
• Home grown monitoring apps
• Integration with your backup to dynamically choose drive based on space.
• Insert your idea here.

So letā€™s do this.

--Create Database

create database WMITest01;

--Enable CLR

exec sp_configure 'clr enabled', 1

reconfigure with override

--Enable trust worthy computing

--This allows us to create assemblies that access resources outside of the database.

alter database WMITest01

set trustworthy  on

--This assemby allows us to use WMI in CLR functions and procs.

use wmitest01

go

CREATE ASSEMBLY [System.Management]

AUTHORIZATION [dbo]

FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Management.dll'

WITH PERMISSION_SET = UNSAFE

Now, we are going create a new VB(or C# but the sample code is VB) database project in Visual Studio 2005. Now do the following steps:

1.       Add a reference to the WMITest database

2.       Set the project properties to unsafe so we can add a reference to System.Management

3.       Add a database reference to System.Management

4.       Create a new stored procedure

5.       Replace the code with the follow code.

6.       Deploy

Imports System

Imports System.Data

Imports System.Management

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Public Class ProcTimeStoredProcedures

    Public Shared Sub pnetWMIProcTime()

        Dim searcher As New ManagementObjectSearcher( _

            "root\CIMV2", _

            "select PercentProcessorTime from Win32_PerfFormattedData_PerfOS_Processor where name = '_Total'")

        For Each queryObj As ManagementObject In searcher.Get()

            Dim record As New SqlDataRecord( _

            New SqlMetaData("PercentProcessorTime ", SqlDbType.VarChar, 100))

            SqlContext.Pipe.SendResultsStart(record)

            record.SetString(0, queryObj("PercentProcessorTime"))

            SqlContext.Pipe.SendResultsRow(record)

        Next

        SqlContext.Pipe.SendResultsEnd()

    End Sub

End Class

So now we can run pnetWMIProcTime and know what the current CPU usage is from within SQL.

email it! |   |   |   |  | 

Permalink     2 Comments  

Rate this Post: 12345
COMMENTS:

Interesting - do you have to register the assembly as UNSAFE?

I ask be I've had one experience with supporting a 3rd party application that used CLR. All of their assemblies were UNSAFE, and this led to a cluster failing over ever 30 minutes, due to memory issues.
Eventually, while they re-coded the application, we reduced SQL memory from 6GB of 8GB, to 3 GB. That finally gave the server stability, even though it affected performance. Still, and application that is failing over can hardly be considered performant, eh?

posted @ Monday, February 25, 2008 5:29 PM by Wanderer

---

Yah, this is definitely not something to run in your OLTP app. I would keep it as an administrative tool. You can also use xp_cmdshell and powershell. I have a sample here:
http://statisticsio.com/Home/tabid/36/articleType/ArticleView/articleId/13/NTSSUG-Presentation-on-WMI.aspx

In SQL 2008, there are better ways :)

posted @ Monday, February 25, 2008 8:59 PM by JasonMassie

---

Name (required)

Email (required)

Website

Notify me of followup comments via e-mail

Simple BBCode can be used like [URL]...[/URL] and [B]