Sql-server – Blocking Extended events

blockingextended-eventssql server

I have very basic table

CREATE TABLE [dbo].[Table_1](   [Test] [int] NULL)
INSERT INTO [dbo].[Table_1]([Test]) Select 1 

sp_configure'block process threshold', 1  --> 1 sec
go

To create blocking, in 1 session i executed

Begin tran
Update [dbo].[Table_1]
set [Test]=1

and another session

select * from [dbo].[Table_1]

when i setup extended events, i got below 

<blocked-process-report monitorLoop="1670">
 <blocked-process>
  <process id="process1f18028c8"  .......>
   <executionStack>
    <frame line="1" stmtend="56" sqlhandle="0x02000000c7f1523220ed350bb649a019ae7dc2bca448a2000000000000000000000000000000000000000000" />
   </executionStack>
   <inputbuf>
select * from [dbo].[Table_1]
   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="sleeping" spid="59"....>
   <executionStack />
   <inputbuf>
**DECLARE @edition sysname; SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname); select case when @edition =
N'SQL Azure' then 2 else 1 end as 'DatabaseEngineType'****   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>

From which sky <<< DECLARE @edition sysname; … came while it should be Update statement….

Please help

Best Answer

This query is run by Management Studio. I have checked this Microsoft SQL Server Management Studio(13.0.11000.78).

In recent releases ability to connect to Azure SQL Database been added to SSMS and looks like SSMS executes this query when ever we change focus to any tab or click in SSMS tab.

So now when we switch table from session 1 (which creates blocking) to session 2, below query is getting executed for the session 1. You can check this by running DBCC InputBudder(<>).

DECLARE @edition sysname; SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname); select case when @edition = N'SQL Azure' then 2 else 1 end as 'DatabaseEngineType'

And hence when blocked process report captures event it get this query from Session 1.

Now we may think that why this is not the case for Session 2. But this is not case as while query in session 2 is executing, SSMS not fire above query.

Related Question