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.
Here are some uses. The first two I have in production.
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 *