in

SQL Server Blogs

Voices from the UK SQL Server Community

Jason Massie's SQL blog

Accessing OS performance counters from tsql

 

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

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

    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.

*Cross posted from http://statisticsio.com *

Comments

 

Less Than Dot - Blog - Awesome said:

Pingback from  Less Than Dot - Blog - Awesome

January 12, 2014 3:37 PM

About JasonMassie

Jason is a SQL Server Consultant for the professional services organization of Terremark (Formerly Data Return LLC) where he has worked for the last 8 1/2 years. Jason is an MCITP Database Administrator\Database Developer as well as an MCDBA on 7.0 and 2000. You can read his blog at http://statisticsio.com. He lives in Irving, TX. He enjoys time with his wife and three daughters as well as making electronic music. He can be reached at http://linkedin.com/in/jasonmassie or jmassie@terremark.com
Powered by Community Server (Commercial Edition), by Telligent Systems