PowerShell and SQL Server Events – Monitoring SP Recompilations


SQL Server 2005 introduced the concept of events and added a WMI provider only to works with events in SQL Server . You probably know as Extended  Events.

“The WMI Provider for Server Events works by translating WQL queries for events into event notifications in the database that you target. Event notifications, like the WMI Provider for Server Events, are new in SQL Server 2005. An understanding of how they work can be useful to you when programming against the provider. For more information, see Event Notifications (Database Engine). “

BOL – Working with the WMI Provider for Server Events

So this means that I can use this provider in my PowerShell Scripts Alegre

If you query in SSMs:

select * from sys.event_notification_event_types 

you will find all the events that you can interact, and one of them is SP_RECOMPILE.

Looking at BOL, we will find the resource that we need to start to play

SP:Recompile Event Class

From this I can create a table in SQL Server that will receive data when an recompile event  occurs . I chose only some information that for me are the most necessary

USE [Test]

GO

 

/****** Object:  Table [dbo].[SPrecompile]    Script Date: 09/04/2011 20:29:13 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE TABLE [dbo].[SPrecompile](

    [ApplicationName] [nvarchar](max) NULL,

    [ClientProcessID] [int] NULL,

    [DatabaseID] [int] NULL,

    [DatabaseName] [nvarchar](max) NULL,

    [EventSubClass] [nvarchar](50) NULL,

    [HostName] [nvarchar](max) NULL,

    [IsSystem] [int] NULL,

    [LoginName] [nvarchar](max) NULL,

    [NTDomainName] [nvarchar](max) NULL,

    [NTUserName] [nvarchar](max) NULL,

    [ObjectID] [int] NULL,

    [ObjectName] [nvarchar](max) NULL,

    [ObjectType] [nvarchar](max) NULL,

    [RequestID] [int] NULL,

    [ServerName] [nvarchar](max) NULL,

    [SessionLoginName] [nvarchar](max) NULL,

    [SPID] [int] NULL,

    [StartTime] [datetime] NULL,

    [TextData] [nvarchar](max) NULL

) ON [PRIMARY]

 

GO

 

 

Then I created a Module to Save at this table.In this module I have 3 functions.

Get-SQLRecompileEventSubClass : switch EventSubClass id to it description
    Ex : EventSubClass 1 = Schema Changed, EventSubClass 2 = Statistics Changed

Get-ObjectType : switch objectType id to it description
    Ex : ObjectType 8259 = Check Constraint , ObjectType  8262 = Stored Procedure

Get-SPRecomopile: Select the data formated from $event variable (this variable is automatically created by PowerShell with all info about the event)

function Get-SQLRecompileEventSubClass { param ([int] $EventSubClass)

    switch ($EventSubClass)

    {

        1 { 'Schema Changed' }

        2 { 'Statistics Changed' }

          3 { 'Recompile DNR' }

          4 { 'Set Option Changed' }

          5 { 'Temp Table Changed' }

          6 { 'Remote Rowset Changed' }

          7 { 'For Browse Perms Changed' }

          8 { 'Query Notification Environment Changed' }

          9 { 'MPI View Changed' }

          10 { 'Cursor Options Changed' }

          11 { 'With Recompile Option' }

        default {'Unknow'}

    }

 

}

 

 

 

function get-ObjectType { param ([int] $objectType )

 

    switch ($objectType)

    { 

        8259 {'Check Constraint'}

         8260 {'Default (constraint or standalone)'}

         8262 {'Foreign-key Constraint'}

        8272 {'Stored Procedure'}

         8274 {'Rule'}

         8275 {'System Table'}

         8276 {'Trigger on Server'}

         8277 {'(User-defined) Table '}

         8278 {'View '}

         8280 {'Extended Stored Procedure '}

         16724 {'CLR Trigger '}

         16964 {'Database '}

         16975 {'Object '}

         17222 {'FullText Catalog '}

         17232 {'CLR Stored Procedure '}

         17235 {'Schema '}

         17475 {'Credential '}

         17491 {'DDL Event '}

         17741 {'Management Event '}

         17747 {'Security Event '}

         17749 {'User Event '}

         17985 {'CLR Aggregate Function'}

         17993 {'Inline Table-valued SQL Function '}

         18000 {'Partition Function '}

         18002 {'Replication Filter Procedure '}

         18004 {'Table-valued SQL Function '}

         18259 {'Server Role '}

         18263 {'Microsoft Windows Group '}

         19265 {'Asymmetric Key '}

         19277 {'Master Key '}

         19280 {'Primary Key '}

         19283 {'ObfusKey '}

         19521 {'Asymmetric Key Login '}

         19523 {'Certificate Login '}

         19538 {'Role '}

         19539 {'SQL Login '}

         19543 {'Windows Login '}

         20034 {'Remote Service Binding '}

         20036 {'Event Notification on Database'}

         20037 {'Event Notification '}

         20038 {'Scalar SQL Function '}

         20047 {'Event Notification on Object'}

         20051 {'Synonym '}

         20549 {'End Point '}

         20801 {'Adhoc Queries which may be cached'}

         20816 {'Prepared Queries which may be cached'}

         20819 {'Service Broker Service Queue '}

         20821 {'Unique Constraint '}

         21057 {'Application Role '}

         21059 {'Certificate '}

         21075 {'Server '}

         21076 {'Transact-SQL Trigger '}

         21313 {'Assembly '}

         21318 {'CLR Scalar Function '}

         21321 {'Inline scalar SQL Function '}

         21328 {'Partition Scheme '}

         21333 {'User '}

         21571 {'Service Broker Service Contract '}

         21572 {'Trigger on Database'}

         21574 {'CLR Table-valued Function '}

         21577 {'Internal Table (For example, XML Node Table, Queue Table.) '}

         21581 {'Service Broker Message Type '}

         21586 {'Service Broker Route '}

         21587 {'Statistics '}

         (21825 -or 21827 -or  21831 -or  21843 -or  21847) {'User  '}

        22099 {'Service Broker Service '}

         22601 {'Index '}

         22604 {'Certificate Login '}

         22611 {'XMLSchema'}

         22868 {'Type'}

    }

}

 

function Get-SPRecompile { param ($myevent)

 

    

    $myevent |         select     ApplicationName,

                            ClientProcessID,

                            DatabaseID,

                            DatabaseName,

                            @{Expression={Get-SQLRecompileEventSubClass $_.EventSubClass};Label="EventSubClass"},

                            HostName,

                            IsSystem,

                            LoginName,

                            NTDomainName,

                            NTUserName,

                            ObjectID,

                            ObjectName,

                            @{Expression={get-ObjectType $_.ObjectType};Label="ObjectType"},

                            RequestID,

                            ServerName,

                            SessionLoginName,

                            SPID,

                            @{Expression={[Management.ManagementDateTimeConverter]::ToDateTime($_.StartTime)};Label="StartTime"},

                            TextData     

 

}

 

 

 

So now it is just play  :

Lets Register the Event  and Save to SQL Table using Out-DataTable and Write-DataTable

$query = "select * from SP_RECOMPILE"

Register-WMIEvent -Namespace 'root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER'  -Query $query  -Action {ipmo sprecompile;ipmo functions;get-sprecompile $event.SourceEventArgs.NewEvent | Out-DataTable | write-datatable -ServerInstance Vader -Database test -TableName SPrecompile}

#

And I create a SP that recompiles :

create procedure [dbo].[IwillRecompile] 

 

as 

 

select * from sys.sysobjects option  (recompile)

GO

Then it is just exec this procedure and see the table :

image

Can you image what you can monitor ? Database Mirroring, replication, Data File Auto Grow …ha this is cool Alegre

It is just we use BOL – Data File Auto Grow Event Class

Include the Data File Auto Grow event class in traces that are monitoring growth of the data file.

If I want to a specific database, query “ and databaseid = yourdatabaseid”

$query = "select * from DATA_FILE_AUTO_GROW where Databaseid = 10"

Again, a small video with good music Alegre

http://www.videolog.tv/laertesqldba

Download Video

Download Module

#PowerShellLifeStyle

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell, SQL SERVER EM GERAL, Virtual Pass BR. Bookmark the permalink.

3 Responses to PowerShell and SQL Server Events – Monitoring SP Recompilations

  1. I am a long time watcher and I just thought I’d stop by and say hi for that first time. I really get pleasure from your posts. Many thanks

  2. Oh my goodness! a tremendous article dude. Thanks Nevertheless I’m experiencing difficulty with ur rss . Don’t know why Unable to subscribe to it. Is there anyone getting equivalent rss drawback? Anybody who knows kindly respond. Thnkx

  3. Pingback: Event Notifications using Powershell, WMI, WQL and SQL Server 2008 « Paul Duffett – Me, myself and MS SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s