PowerShell and SQL Server Events–BLOCKED PROCESS REPORT and SP_WHOISACTIVE


I believe that one of the beauties of PowerShell is the ability to provide mechanisms, in a simple and clear way, to we can solve our daily tasks.

And what DBA has no block problem? If you are not monitoring when it happens, there is no way without using third-party tools or even SQL Server (sqldiag… Etc.) to log these blocked  processes to analyze later. Obvious that those processes that  I am talking about are that  do not stop your production

We can use SQL Profiler also, with BLOCKED PROCESS REPORT. But I want more information. I want the complete chain (with the evil process in the top) and the SQL Plan for each process. I am still not figured out how to do this using profiler.

Then , super powershell and ultra sp_whoisactive will do it for you Alegre

Firts, download this AWESOME stored procedure provide by the Master Jedi Adam Yoda Machanic´s  sp_whoisactive. And I will not tell to you what is sp_whoisactive, if you don´t know… Man, go fishing, be a doctor, a dentist, SQL Server definitely is not for you.

Also we need to download the Chad ObiWan Miller´s  Write-DataTable and Out-DataTable and put in your profile as well.

Firts we need to configure the SQL Server to report these events.

The Blocked Process Report event class indicates that a task has been blocked for more than a specified amount of time. This event class does not include system tasks or tasks that are waiting on non deadlock-detectable resources.

To configure the threshold and frequency at which reports are generated, use the sp_configure command to configure the blocked process threshold option, which can be set in seconds. By default, no blocked process reports are produced. For more information about setting the blocked process threshold option, see blocked process threshold Option.

Blocked process threshold Option

Use the blocked process threshold option to specify the threshold, in seconds, at which blocked process reports are generated. The threshold can be set from 0 to 86,400. By default, no blocked process reports are produced. This event is not generated for system tasks or for tasks that are waiting on resources that do not generate detectable deadlocks

Then we need to :

sp_configure 'show advanced options',1

go

reconfigure

go

sp_configure 'blocked process threshold',20

My events will be fired at each 20 seconds.

Now, let´s create the table that will store the values

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BlockedProcessReport]') AND type in (N'U'))

DROP TABLE [dbo].[BlockedProcessReport]

GO

 

USE [Test]

GO

 

/****** Object:  Table [dbo].[BlockedProcessReport]    Script Date: 09/14/2011 11:09:23 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

SET ANSI_PADDING ON

GO

 

CREATE TABLE [dbo].[BlockedProcessReport](

    [dd hh:mm:ss.mss] [varchar](15) NULL,

    [session_id] [varchar](30) NULL,

    [sql_text] [xml] NULL,

    [query_plan] [xml] NULL,

    [login_name] [sysname] NOT NULL,

    [wait_info] [nvarchar](4000) NULL,

    [CPU] [varchar](30) NULL,

    [Duration] [int] NULL,

    [tempdb_allocations] [varchar](30) NULL,

    [tempdb_current] [varchar](30) NULL,

    [blocking_session_id] [varchar](30) NULL,

    [blocking_session_count] [varchar](30) NULL,

    [reads] [varchar](30) NULL,

    [writes] [varchar](30) NULL,

    [physical_reads] [varchar](30) NULL,

    [used_memory] [varchar](30) NULL,

    [Status] [varchar](30) NULL,

    [open_tran_count] [varchar](30) NULL,

    [host_name] [varchar](100) NULL,

    [Database_name] [varchar](100) NULL,

    [program_name] [varchar](500) NULL,

    [start_time] [datetime] NULL,

    [login_time] [datetime] NULL,

    [collection_time] [datetime] NULL

) ON [PRIMARY]

 

GO

 

SET ANSI_PADDING OFF

GO

 

 

 

Now, a Posh function to process and save the info to SQL

function Get-ProcessBlockedInfo { param ($myevent)

    

    #Find The blocked spid

    $SPIDBlocked=""

    $matches.Clear | Out-Null

    $found = $myEvent.textdata.substring(1,400)  -match '^.*spid="(\d+)".*$' 

    if ($found)

    {

        

        

        $SPIDBlocked = $matches[1] 

        

        $Data = Invoke-Sqlcmd -ServerInstance vader -Database Master -Query "Sp_whoisactive $SPIDBlocked, @find_block_leaders =1,@get_plans =2,@get_full_inner_text=1" | `

                select     "dd hh:mm:ss.mss",

                        session_id,    sql_text,query_plan,    login_name,    wait_info,    CPU,

                        @{Expression={($myEvent.Duration / 1000)};Label="Duration"},

                        tempdb_allocations,

                        tempdb_current,    blocking_session_id,    blocked_session_count,    reads,    writes,    physical_reads,    

                        used_memory    ,status,    open_tran_count,    host_name,    database_name,    program_name,    

                        start_time,    login_time,    collection_time

 

        

        sleep 1

 

        $valuedatatable = Out-DataTable -InputObject $data

        Write-DataTable -ServerInstance Vader -Database Test -TableName BlockedProcessReport -data $valuedatatable

    } 

}

I am using invonke-sqlcmd to call the Adám´s provedure, you must be sure that SQL Server provider is loaded into your profile, as this function as well. (and the sp_whoisactive created in the master database )

Then, just fire in the bomb.( ops, the event)

$query = "select * from  BLOCKED_PROCESS_REPORT where duration > 50000"

Register-WMIEvent -Namespace 'root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER'  -Query $query  -Action {ipmo functions ; Get-ProcessBlockedInfo $Event.SourceEventArgs.NewEvent}

Take a look, I am just logging events with duration > 50000 ms

and the data are stored Alegre

Notice that for each blocked chain, we have a unique identifier ([collection_time]) column. This is mean that you can query the complete chain for each blocked process

image

or we can use row_number also :

select collection_time,

        ROW_NUMBER () over (PARTITION by collection_time order by collection_time) as Row_number ,

        * 

from BlockedProcessReport

and the blocked chain will show grouped by collection_time,  with the evil guy in the top at the chain, and with line number Alegre

image

or even just the lines that have a duration > 20000

select collection_time,

        ROW_NUMBER () over (PARTITION by collection_time order by collection_time) as Row_number ,

        * 

from BlockedProcessReport

where duration > 20000

All The number 1 guys, are the cruel alien Rodak and you are Goldar  now (or PowerGoldar). Just kick their …head

Imagem:Vingadores do espaco1.jpg

That is it guys, now I can log all blocked process with duration > 5000 with COMPLETE INFO for further analisys and with SQL PLAN (HAAAAAAAAAAAAA)

image

Ha, if you wanto to be notified by email when some process were > 20000 for example..simple :

function Get-ProcessBlockedInfo { param ($myevent)

    

    #Find The blocked spid

    $SPIDBlocked=""

    $matches.Clear | Out-Null

    $found = $myEvent.SourceEventArgs.NewEvent.textdata.substring(1,400)  -match '^.*spid="(\d+)".*$' 

    if ($found)

    {

        if ( $myEven.SourceEventArgs.NewEvent.duration > 20000 )

        {

            Send me an email please

        }

        

        $SPIDBlocked = $matches[1] 

        

        $Data = Invoke-Sqlcmd -ServerInstance vader -Database Master -Query "Sp_whoisactive $SPIDBlocked, @find_block_leaders =1,@get_plans =2" | `

                select     "dd hh:mm:ss.mss",

                        session_id,    sql_text,query_plan,    login_name,    wait_info,    CPU,tempdb_allocations,

                        tempdb_current,    blocking_session_id,    blocked_session_count,    reads,    writes,    physical_reads,    

                        used_memory    ,status,    open_tran_count,    host_name,    database_name,    program_name,    

                        start_time,    login_time,    collection_time

 

        

        sleep 1

        

        $valuedatatable = Out-DataTable -InputObject $data

        Write-DataTable -ServerInstance Vader -Database Test -TableName BlockedProcessReport -data $valuedatatable

    } 

}

 

 

 

 

 

and change in the event fire command :

$query = "select * from  BLOCKED_PROCESS_REPORT where duration > 50000"

Register-WMIEvent -Namespace 'root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER'  -Query $query  -Action {ipmo functions ; Get-ProcessBlockedInfo $Event.SourceEventArgs.NewEvent}

Big Thanks to Sir Adam Yoda Machanic to provide to us this AWESOME stored procedure

#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–BLOCKED PROCESS REPORT and SP_WHOISACTIVE

  1. “go fishing, be a doctor, a dentist, SQL Server definitely is not for you.”

    huuahauh

  2. Normally I don’t read article on blogs, but I would like to say that this write-up very forced me to try and do it! Your writing style has been surprised me. Thanks, very nice post.

  3. Stefano says:

    AWESOME WRITING STYLE DUDE U RULZ!!111

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s