Event Notifications using Powershell, WMI, WQL and SQL Server 2008

This is my first post in a very long time, mainly because I’ve been incredibly busy but also because I keep reading other peoples blogs and I was of the opinion that it had all been said before, usually by someone who knows a shed-load more than I do!

However, I have just been through an experience that I felt I needed to share because I really couldn’t find the definitive answer out there. The question was – how can I capture the SQL:Stmtrecompile event using something other than Profiler Trace.

One solution is by using a combination of Powershell, WMI and WQL.

I could leave it there and let you do your own digging but I just know I’m going to need to do this all over again at some point so even if nobody else reads this, it’s going to be a useful exercise for me.

So. Where to begin?

A client I am doing some work for is seeing a pattern whereby a certain piece of functionality performs differently at certain parts of the day.

“What’s so unusual about that?” I hear you say, after all, isn’t this completely expected.

Well, yes and no. It is expected because as the number of users (and volumes of data) ramp up this is obviously going to have an impact.

But what if the impact is actually a positive one? What if the performance of the component increases when it is under the most pressure?

Early in the morning, when the “system” is quiet, the time measurement is ~80ms for a particular piece of functionality.

As the number of users increases, and the volume of data being shifted around also increases, the time measurement drops to ~60ms.

How could this be? Well, the stored procedure involved uses table variables. Many people, and far better DBA’s than myself, have blogged about the use of Table Variables vs Temporary Tables so I’m not even going to embarras myself with an attempt. I understand the differences and I believe that the combination of the table variable and the amount of data in the transitory tables (to which the table variable is joining) is a contributory factor behind the rise in performance.

Why?

Well (and I have actually witnessed this change in behaviour first hand) the query optimiser makes a decision based upon the statistics (on the transitory tables in the database; there are no stats for table variables of course) and, naturally, they change as the data in the tables change.

I have seen two identical queries behave in different ways because of the data volumes in the tables. One query plan will perform an Index Seek on Table A followed by a Key Lookup on Table B (yes, yes I know all about covering indexes – go tell that to the developers who want to use index hints instead) and on another database (same stored proc) the query plan will perform an Index Seek on Table B followed by a Key Lookup on Table A.

So how can I prove that what is happening is as a direct result of shifting patterns in the statistics histogram? The statistics are set to Auto Update so over the course of the day the picture is going to change. What I want to capture is the moment the picture changes to such an extent that it causes the plan to change.

Well one thing I did think of was to capture the SQL:StmtRecompile event for the given SP to see if I can see the moment when the plan changes.

This may not be the answer to this particular question, time will tell when I finally come to deploy the script, but it did set me off on a technical challenge that I will now write about here.

Capturing SQL Events using Powershell, WMI and WQL.

Firstly, check out the link to the SQL:StmtRecompile Event Class on BOL. You will need to understand what it is that you are capturing – http://technet.microsoft.com/en-us/library/ms179294.aspx

Please note, before this task my Powershell and WQL skills were practically non-existent, so it may not come as any surprise to you if you see some glaring errors in my code. I apologise for these in advance; I had deadlines to meet and I just needed this to work as best I could. I’ll try and make it perfect over time.

My first task was obviously to trawl Google to see if anyone else had done this before and save me time and effort. There are a few examples out there but none that provided me with a complete working solution.

One of my first aknowledgments is to the Shellyourexperience team who set me off on the right foot with this post http://shellyourexperience.wordpress.com/2011/09/04/hey-powershell-eventing-can-you-notify-me-when-a-sp-recompile-happens/

Another nod of aknowledgment goes to Chad Miller whose Write-DataTable and Out-DataTable functions have saved me a huge amount of effort. Thanks Chad.

So whilst I have had to borrow the code of others, it was not simply a case of pasting it all together and pressing F5 (if you happen to be using PowerGUI as I am).

So let’s cut to the chase.

Using PowerGui, I registerd a WMI Event, which is for all intents and purposes a job that listens for a particular event.

Here is the line of code I used (which I have saved as recompile-wrapper.ps1):

$query = “select ApplicationName, ClientProcessID, DatabaseID, DatabaseName, EventSequence, EventSubClass, EventSubClassDesc, GroupID, HostName, IntegerData2, IsSystem, LineNumber, LoginName, NestLevel, NTDomainName, NTUserName, ObjectID, ObjectName, ObjectType, Offset, RequestID, ServerName, SessionLoginName, SPID, StartTime, TextData from SQL_STMTRECOMPILE”

Register-WMIEvent -Namespace ‘root\Microsoft\SqlServer\ServerEvents\TEST01′-Query $query -Action {ipmo -name C:\WMI\sprecompile.psm1 -Verbose;ipmo -name C:\WMI\functions.psm1 -Verbose;$dtr = Get-SPStmtRecompile $event.SourceEventArgs.NewEvent`

    | Out-DataTable ; write-datatable -ServerInstance MyServer\TEST01 -Database SBTest -TableName StmtRecompile -Data $dtr}

So let’s break that down.

$query is a variable that contains my WQL query. I am wanting to retrieve the rows from the SQL_StmtRecompile event that has fired. I have deliberately specified the columns as opposed to selecting everything because I am currently having issues with the Varbinary datatypes and BulkCopy.

The -Namespace is the WMI path to my SQL instance. If you want to explore all of your available Namespace paths and events I highly recommend you download a copy of PowerGUI. By using the WMI Browser in the Admin Console you will be able to see exactly what you are dealing with.

The -Action parameter is followed by a number of options inside the curly brackets. The ipmo command is “Import Module” and I am specifying the fully named path, just so there is no ambiguity. I am also specifying the -verbose option because I want to see the modules being loaded when the event fires.

The two modules I am loading, sprecompile.psm1 and functions.psm1, have been heavily borrowed from the ShellYourExperience team and Chad Miller (I’ll provide links and downloads somewhere in this blog). I did have to create a new function in the sprecompile module and also modify the Write-DataTable function in the functions module to allow the use of triggers with the BulkCopy command. Those modifications in themselves were not easy (well, they are now that I know exactly what it is I need to do) and I wanted to point them out just so you don’t think I’ve robbed someone elses’ code and just put it all together! I’ve literally spent the last two days with my head buried in this. I’ve pulled the legs off it, put it all back together, debugged every single step and I have even woken up in the middle of the night trying to figure out how I’m going to enable the FireTriggers BulkCopy option. I’m completely new to PowerShell so it just wasn’t obvious at the time and I feel all the better for having gone through all of that pain and learning.

Anyway, back to the code.

The line $dtr = Get-SPStmtRecompile $event.SourceEventArgs.NewEvent | Out-DataTable is assigining the output from the Out-DataTable function to a variable called $dtr.

The line write-datatable -ServerInstance GBEDUB-APS08\TEST01 -Database SBTest -TableName StmtRecompile -Data $dtr is calling the Write-DataTable function and passing in the SQL Server Instance Name, the Database Name, the Table Name and the data I want to Bulk Copy in to the SQL Table.

This is where my code differentiates to that from the ShellYourExperience guys: they are piping the output from the Out-DataTable function into the Write-DataTable function. Now I just could not get this to work so I assigned the output to a variable and used the -Data parameter of the Write-DataTable function. This worked a treat.

You will need to create a table (mine is called StmtRecompile in my SBTest database) and if you use the data types from the Event Class you’ll not go far wrong. Well, substitute the Image datatypes for Varbinary(MAX) and that will see you right. I’ll attach my table def somewhere in thist post.

Now, to actually register the WMI Event you need to execute the recompile-wrapper.ps1 PowerShell script, and I have done it from the command line like this:

C:\WMI>powershell -noexit -noninteractive “& “c:\wmi\wrapper.ps1″

It fires up Powershell, executes the command and leaves the job running in the background.

How do I know? Well, from the PS command line, just type get-job and you’ll see the job in the list.

It’ll almost certainly have a status of Not Started and that is absolutely fine. You don’t have to hunt around for clues on how to get it started, it will start itself when the event fires.

So how do you fire the event? Simple, run a query! You’ll need to run a query that will create a new plan and you can force this by creating\rebuilding indexes (causing a schema change).

As soon as you hit the F5 key and your SQL query executes, check out the PS window and you should see the modules being loaded.

Type, get-job and you should either see the status change to Running or Failed.

If it’s Running then all is well – enjoy.

Here’s a screenshot of the job being registered and then fired.

If it’s Failed and/or the modules didn’t load then don’t panic, all is not lost and there are a number of options we will need to explore.

The best way I have found to debug these scripts and functions is to open them in the PowerGUI Script Editor. It’s a really nice interface and gives you great feedback (and clues!) as to what’s occurring (a small nod to Gavin and Stacey there).

First things first.

Open up the recompile-wrapper.ps1 script in the Script Editor and press F5. What happens? Does it register the event.

If it comes up with something like a Namespace error this is usually a red herring, although it is worth confirming that the namespace you are using actually exists (use PowerGUI! ).

Check that the modules you are trying to import (ipmo) exist in the folders you are specifying and that the names of the files are correct. Also check that you haven’t accidentally renamed a function by mashing the keys when trying to save it.

It’s also worth deconstructing the WMi event command line; try removing the Write-DataTable and adding a ;$dtr} instead so that when you type in Receive-Job -ID 1 (1 being your job ID) you will see the output of the Out-DataTable function (what with it being captured in the $dtr variable and all).

Like this:

PS C:\WMI> Register-WMIEvent -Namespace ‘root\Microsoft\SqlServer\ServerEvents\TEST01′-Query $query -Action {ipmo -name C:\WMI\sprecompile.psm1 -Verbose;ipmo -name C:\WMI\functions.psm1 -Verbose;$dtr = Get-SPStmtRecompile $event.SourceEventArgs.NewEvent | Out-DataTable ;$dtr}

PS C:\WMI> Receive-Job -ID 1

(The above Receive-Job command is assuming that the event is registering ok and has been fired – i.e. the job status is either Running or Failed).

If that appears to be working then check the object definition of your target table. The datatypes (all except for image, which should be substituted for varbinary(MAX)) and column names should match.

If that all seems ok then I’m afraid you have hit something I haven’t and will most likely endure a small amount of pain for what will ultimately be a huge gain. By constantly debugging, amending, pulling my hair out over the last two days I have accidentally got into PowerShell, WMI Events and WQL…..and I love it!

If you do actually see some output in the table then well done. But what if you want to include the plan definition?

Well, the only way I could think of this was very crude indeed – I’ve written a trigger on the StmtRecompile table to match the plan of the object and store it into the table. I will look to tighten this part of the process up and will re-post if I can find a better way of achieving this.

I hope you find all\some of the above useful. If you have any questions please feel free to drop me a line and I’ll do my best to answer; I’m far from an expert in PowerShell, WMI or WQL but having been through the pain of getting the above to work I may well know more than I think I do!

Thanks

Paul.

Useful links:

Shell Your Experience http://shellyourexperience.wordpress.com/2011/09/04/hey-powershell-eventing-can-you-notify-me-when-a-sp-recompile-happens/

Write-DataTable http://gallery.technet.microsoft.com/scriptcenter/2fdeaf8d-b164-411c-9483-99413d6053ae

Out-DataTable http://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd

 System.Data.SqlClient.NameSpace http://msdn.microsoft.com/en-us/library/8t72t3k4(v=VS.90).aspx

SQL BulkCopy Constructor http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.sqlbulkcopy(v=VS.90).aspx

SQL BulkCopyOptions http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopyoptions(v=VS.90).aspx

Scripts

sp_recompile.psm1
functions.psm1
stmtrecompile-wrapper.ps1
stmtRecompile-SQLTable

Disclaimer: I don’t claim to have written the above scripts from scratch, I have merely added\amended functionality to make the whole process work for me.