Entries for the 'WMI' Category

ExecuteWQL() in Policy Based Management

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.

email it! |   |   | 

Permalink     1 Comments

TechEd! Let's do this.

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

Permalink     2 Comments

A generic WMI perfmon CLR procedure

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

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

email it! |   |   | 

Permalink     3 Comments

Tech∙ED database platform track

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

Permalink     0 Comments

TechEd 2008

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

Permalink     0 Comments

SQL Agent Alerts based on the OS WMI classes

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

Permalink     0 Comments

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.