BorderLayoutBoxedLayoutOpenLayoutMaximum textMedium textSmall text


Register
Thursday, March 11, 2010
 
MyStreamMinimize
Print  

Entries for the 'WMI' Category

My Wish for SQL Server 11

Posted by Jason Massie Click to IM Jason Massie on Saturday, January 24, 2009 at 8:48 AM to tsql, WMI
737 Views | 2 Comments | Article Rating

I have been working on code to auto trace anyone who logs in under the SA role that is not a job or system process. It would be filtered by spid and auto stop when they logout. The only thing I could come up with is two WMI alerts, two stored procedures and two jobs. It is a craptastic monstrosity at best.

It would be easier if their was a logout DDL trigger but I think a trace definition as a property of a login or role would be a better solution. Think of it as the “big brother” checkbox for the JR DBA.

I have filed a connect here. Vote if you would find this useful.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=404808

email it! |   |   | 

ExecuteWQL() in Policy Based Management

Posted by Jason Massie Click to IM Jason Massie on Monday, November 10, 2008 at 10:47 AM to SQL Server 2008, WMI
860 Views | 1 Comments | Article Rating

I always look for ways to access OS data from within SQL Server. As a DBA, this helps you make better decisions. In the Policy Based Management(PBM) realm, imagine a policy being run against all SQL Servers that checks for ERROR events in the event logs for the past 24 hours. Your morning report could include any servers having issues. Another scenario might be a one time report that checks the firmware revision of your raid controller. A WQL query and a couple clicks then you have a list of boxes that do not have the latest firmware. When you are managing a lot of SQL boxes, this could be powerful and time saving. You could also combine with ExecuteSQL() for more complex scripts.

Here is what is in BOL on ExecuteWQL():

ExecuteWQL()

Function signature: Variant ExecuteWQL ( string returnType , string namespace , string wql )

Function description: Executes the WQL script against the namespace that is provided. Select statement can contain only a single return column. If more than one column is provided, error will be thrown.

Arguments and return type definition

  • returnType - Specifies the return type of data that is returned by the WQL. The valid literals are Numeric, String,Bool, DateTime, Array, and Guid.
  • namespace - Is the WMI Namespace to execute against.
  • wql - Is the string that contains the WQL to be executed.

Example: ExecuteWQL('Numeric', 'root\CIMV2', 'select NumberOfProcessors from win32_ComputerSystem') <> 0

I don’t know why I need to expand on a topic where the example verifies that the local server has at least one processor. :)

Here is a quick example of one of the cool things you can do with PBM in SQL Server 2008. It is pretty simple. We get the freespace for each drive and we fail evaluation if the drive has less than 10000MB free. You don’t want this to be your complete monitoring system but it could be a nice supplement.

clip_image002

email it! |   |   | 

TechEd! Let's do this.

Posted by Jason Massie Click to IM Jason Massie on Friday, May 30, 2008 at 9:05 PM to SQL Server 2008, SQL performance tuning, tsql, WMI, CLR, internals, TechEd, Procedure Cache, DMVs
1788 Views | 2 Comments | Article Rating

I am pumped up about TechEd. Bill Gates's last technical keynote. He has to have a bomb to drop... The launch of Windows\VS\SQL Server 2008. All of the speakers are reaching deep into their bag of tricks and bringing new hardcore sessions. The universal party. The blogger's lounge. Twitter, FriendFeed, MSDN, and Technet are all buzzing with posts.

I found out a couple of days ago that a speaker would not be able to make it due to a family emergency. I offered to present his session on DMV's so I will be presenting these sessions:

 

Using Dynamic Management Views to Improve Your Development

Dynamic Management Views were added to SQL Server 2005 and have been enhanced in SQL Server 2008. While they provide great functionality and usefulness, it appears they are not widely understood or implemented as yet. This session provides an overview of the Dynamic Management Objects available (both Views and Functions) and describes those considered the most useful. It shows how they can be utilized in monitoring, troubleshooting, and ongoing management of SQL Server systems and in gaining an understanding of SQL Server’s operation. The session also illustrates how they can be used to improve the database development experience and how they can be used in custom reports within SQL Server Management Studio.

Database Platform
300
BRK
Jason Massie

Windows Management Instrumentation (WMI) for the Command Line DBA

This is a demo filled session on how a command line DBA can unleash the power of WMI. WMI allows the DBA access to information that was previously hard to get or unavailable like the application event logs, OS perfmon counters, and OS events. This session covers WQL queries from SSIS, accessing OS performance counters from SQL, automated reactions to WMI events, WMI from Windows PowerShell and much more.

Database Platform
400
BRK
Jason Massie

Troubleshooting Query Plans Gone Wrong

Have you ever flushed the procedure cache to resolve a performance problem? Have you ever had to use a join or index hint when you should not have to? Have you ever updated statistics to fix a performance problem and wondered why it worked? In this session, we get deep into how the query optimizer decides how to execute a SQL statement. We look at common problems that cause the optimizer to choose the wrong access paths like underestimation. We go over a detailed demo on parameter sniffing, problems with local variables and statistics. We also look at solutions to these problems in several areas including good design at the application, schema, and query levels, proactive maintenance and reactive fixes. This session applies to Microsoft SQL Server 2000 through 2008 but we pay special attention to new features in SQL Server 2008 like plan freezing, new hints, filter indexes, and statistics among others.

Database Platform
400
BRK
Jason Massie

I'll be there Monday through Saturday. Hope to see you there!

email it! |   |   | 

A generic WMI perfmon CLR procedure

Posted by Jason Massie Click to IM Jason Massie on Saturday, May 17, 2008 at 12:26 PM to SQL Server 2008, SQL Server 2005, WMI, CLR
1700 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! |   |   | 

Tech∙ED database platform track

Posted by Jason Massie Click to IM Jason Massie on Sunday, March 02, 2008 at 11:15 AM to SQL Server 2008, SQL Server 2005, SQL performance tuning, WMI, TechEd
1441 Views | 0 Comments | Article Rating

It looks like they posted the sessions for Tech∙ED. Get ready for some hardcore SQL mayhem. All of the Database Platform are sessions are 300 and 400 level. Not a single 200. The line up is heavy weight and it looks like they are all pulling out new sessions. It is an honor just to be presenting with this crew. I was planning on taking in some sessions on VS2008, sharepoint and other topics I don't get to see much action in but I am afraid I would miss something if I did.

My sessions will be "Troubleshooting query plans gone wrong." and "WMI for the command line DBA". I hope to see you there. It is truly going to be a great year with the new products. 

 

email it! |   |   | 

TechEd 2008

Posted by Jason Massie Click to IM Jason Massie on Tuesday, February 19, 2008 at 11:10 PM to SQL Server 2008, SQL Server 2005, query optimizer, WMI, TechEd
1030 Views | 0 Comments | Article Rating

Two of my session submissions got accepted for the TechEd Developer conference database track.

  • Troubleshooting Query Plans Gone Wrong
  • Windows Management Instrumentation(WMI) for the Command Line DBA

I am really pumped up about this. I have seen the other sessions that they have selected so far and they look great. I highly suggest you check this out! Orlando over the Memorial Day Weekend followed by some hardcore tech goodness. That sounds like a great vacation.

email it! |   |   | 

SQL Agent Alerts based on the OS WMI classes

Posted by Jason Massie Click to IM Jason Massie on Wednesday, December 05, 2007 at 10:48 PM to SQL Server 2005, WMI
1405 Views | 2 Comments | Article Rating

There is not an example in books online and very little on the net. Let's look at this example that I worked through with a guy in the news groups.

USE [msdb]

GO

EXEC msdb.dbo.sp_add_alert

@name =N'Test',

@enabled =1,

@delay_between_responses =0,

@include_event_description_in =0,

@wmi_namespace =N'\\.\ROOT\CIMV2',

@wmi_query =N'SELECT * FROM __InstanceModificationEvent WITHIN 600 WHERE TargetInstance ISA "Win32_LogicalDisk" AND TargetInstance.FreeSpace < 100000'

GO


This fires when any disk on the local server has less than 100000 bytes free. That is kinda boring huh? How about this?

USE [msdb]

GO

EXEC msdb.dbo.sp_add_alert

@name =N'Test',

@enabled =1,

@delay_between_responses =0,

@include_event_description_in =0,

@wmi_namespace =N'\\YourWebServer\ROOT\CIMV2',

@wmi_query =N'SELECT * FROM __InstanceModificationEvent WITHIN 600 WHERE TargetInstance ISA "Win32_PerfFormattedData_PerfOS_Processor" AND TargetInstance.PercentProcessorTime > 90'

GO


Now, let's say you have an annoying web admin that always gleefully lets you know when there is a problem with SQL. With this alert, you can fire him an email if the web server is running hot.

email it! |   |   | 

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

Posted by Jason Massie Click to IM Jason Massie on Thursday, November 29, 2007 at 10:47 PM to WMI, CLR
2354 Views | 3 Comments | Article Rating

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! |   |   | 

WMI - OPENROWSET

Posted by Jason Massie Click to IM Jason Massie on Wednesday, November 21, 2007 at 9:22 AM to WMI
699 Views | 0 Comments | Article Rating

Help the cause and vote for this a OLDB WMI "provider" that could be used in places like openrowset and dataflows in SSIS.

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=273782

**************snip***********************

Description
There's a WMI ODBC provider, and there's OPENROWSET. Unfortunately combining these is not supported (and per user reports hangs the machine).

We do have the need to query WMI from SQL. Currently we jump thru extra unnecessary hoops, specifically we do xp_cmdshell and parse out text output. That's pretty nasty...
Comments
Thanks for your feedback regarding the incompatibility between the WMI ODBC provider and OPENROWSET. We have triaged your request and will consider implementing a solution that allows you to query WMI from SQL in the next release.

email it! |   |   | 

NTSSUG Presentation on WMI

Posted by Jason Massie Click to IM Jason Massie on Wednesday, November 21, 2007 at 9:21 AM to SQL Server 2005, tsql, WMI, CLR
1130 Views | 0 Comments | Article Rating

Here are the demo files from my presentation at the North Texas SQL Server Users Group. It was a pretty fun experience. I was kind of hestitant to go with the topic because I thought it may be a little dry however I think it turned out pretty interesting.

Maybe I am a little biased but I think North Texas sets the bar high when it comes to knowledgable DBA's. We have a lot of of Microsoft people, MVP's and authors to spread the knowledge around. It was an honor to present and I look forward to attending the user group more often.

Download demo scripts.

email it! |   |   | 

Page 1 of 1First   Previous   Next   Last   


Copyright 2006 by Statistics IO, My SQL Server Blog