BorderLayoutBoxedLayoutOpenLayoutMaximum textMedium textSmall text


Register
Tuesday, March 09, 2010
MyStreamMinimize
Print  

A generic WMI perfmon CLR procedure

Posted by Jason on Saturday, May 17, 2008 to SQL Server 2008, SQL Server 2005, WMI, CLR
1693 Views | 4 Comments | Article Rating

I am working on my demo's for TechED and I wrote this proc and I thought I would share. Well, updated an old proc I previously posted. It allows you to pass a computer name, a WQL query and a perfmon counter. It must only return a single value although it could easily be modified to return a record set.

You have to do the following in SQL before you deploy:

 

--Enable CLR
exec sp_configure 'clr enabled', 1
reconfigure with override
 
--Enable Trustworthy computing
--This allows us to create unsupported assemblies
alter database DemoDB
set trustworthyon
 
use master
go
GRANT unsafe ASSEMBLY TO [WIN2K3R2EE\Administrator]
ALTER AUTHORIZATION on database::demodb to [WIN2K3R2EE\Administrator]
 
--This assembly allows us to use WMI in CLR functions and procs.
use DemoDB
go
CREATE ASSEMBLY [System.Management]
AUTHORIZATION [dbo]
FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Management.dll'
WITH PERMISSION_SET = UNSAFE

Copy and add this code to a VB database project stored proc in VS2005\2008.

 Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Management
Imports Microsoft.SqlServer.Server
 
 
Partial Public Class StoredProcedures
 _

Public Shared Sub pnetWMI(ByVal sComputer As String, ByVal sWQL As String _

, ByVal sCounter As String)

Try
 
 
 Dim searcher As New ManagementObjectSearcher( _
 "\\" & sComputer & "\root\CIMV2", _
                sWQL)
 For Each queryObj As ManagementObject In searcher.Get()
 
 Dim record As New SqlDataRecord( _
 New SqlMetaData(sCounter, SqlDbType.VarChar, 100))
 
                SqlContext.Pipe.SendResultsStart(record)
 
                record.SetString(0, queryObj(sCounter))
                SqlContext.Pipe.SendResultsRow(record)
 Next
            SqlContext.Pipe.SendResultsEnd()
Catch ex As Exception
 Dim sp As SqlPipe = SqlContext.Pipe()
            sp.Send(ex.Message)
End Try
End Sub
End Class
In visual studio project properties, add a database connection to your db, add a reference to system.management, set to unsafe and deploy.
 
Here are a few sample executions but of course and any perfmon counter should work:

exec pnetWMI 'localhost', 'select AvgDiskQueueLength from Win32_PerfFormattedData_PerfDisk_PhysicalDisk where name = ''_Total''', 'AvgDiskQueueLength'

exec pnetWMI 'localhost', 'select PercentProcessorTime from Win32_PerfFormattedData_PerfOS_Processor where name = ''_Total''', 'PercentProcessorTime'

email it! |   |   |   |  | 
Permalink     4 Comments  

Rate this Post:
COMMENTS:

posted @ Saturday, May 17, 2008 3:25 PM by DotNetKicks.com


pqA0o2 tjmlofmxqnbt, wxfhoxuxylkm, [link=http://edsuvdjhuvfu.com/]edsuvdjhuvfu[/link], http://tfbypjqkvyww.com/

posted @ Tuesday, October 28, 2008 4:26 PM by irreruplda


Comments from the following blog entry: http://www.urlrecorder.com/vs2005

posted @ Friday, October 31, 2008 10:35 AM


This works great, but over time it starts failing with a "Server buffers are full and data cannot be accepted" message. The only way I have found to fix that condition is to reboot the server. Have you run into this issue before? Is there a way to prevent this?

posted @ Wednesday, June 03, 2009 11:02 AM by Don Ferguson


Name (required)

Email (required)

Website


Simple BBCode can be used like [url=http://example.com]Example[/url] and [B]

Copyright 2006 by Statistics IO, My SQL Server Blog