Lesson Learned #98: Is possible to create an extended event on the server in Azure Managed Instance?

[Jose_Manuel_Jurado]

1) The first point that we need is to create the credential, because the extended file will create in a blob storage.

CREATE CREDENTIAL [https://myblogstorage.blob.core.windows.net/myfolder]
WITH IDENTITY='SHARED ACCESS SIGNATURE',
SECRET = 'sv=2018-03-28&ss=bfqt&srt=sco&sp=rwdlacup&se=2019-07-23T23:29:33Z&st=2019-07-09T15:29:33Z&spr=https&sig=...%%3D'

2) The second point is to create the extended event, in this case, as an example, I'm going to define an extended event for deadlocks for all databases at server level.

CREATE EVENT SESSION [deadlockJM] ON SERVER 
ADD EVENT sqlserver.lock_deadlock(
    ACTION(sqlserver.sql_text,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.session_id,sqlserver.username)),
ADD EVENT sqlserver.lock_deadlock_chain(
    ACTION(sqlserver.sql_text,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.session_id,sqlserver.username))
ADD TARGET package0.asynchronous_file_target(
SET filename='https://myblobstorage.blob.core.windows.net/myfolder/deadlock.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)

3) The third one, is to enable ALTER EVENT SESSION [deadlockJM] ON server STATE = START;

4) Try to reproduce the issue using these two sessions:

• 4.1) Create the tables and add some rows.

CREATE TABLE [dbo].[Person](
[Id] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](20) NOT NULL,
[BusinessEntityID] [int] NOT NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[Id] ASC
))

CREATE TABLE [dbo].[PersonPhone](
[PhoneNumber] [varchar](50) NOT NULL,
[BusinessEntityID] [int] NOT NULL,
CONSTRAINT [PK_PersonPhone] PRIMARY KEY CLUSTERED
(
[PhoneNumber] ASC
))

insert into [PersonPhone] values ( '999-555-1212',1)
insert into [Person] values( 'Chris',1)

• 4.2) Open the SQL SERVER Management Studio and open two queries.

-- Execute this query using a query window for Deadlock Thread 1
BEGIN TRANSACTION
UPDATE [PersonPhone] SET PhoneNumber = '999-555-1212' WHERE [BusinessEntityID] = 1
WAITFOR DELAY '00:00:50'
UPDATE [Person] SET [FirstName] = 'Chris' WHERE [BusinessEntityID] = 1
ROLLBACK TRANSACTION

-- Very quickly execute this query using a query window for Deadlock Thread 2
-- ====================================
BEGIN TRANSACTION
UPDATE [Person] SET [FirstName] = 'Chris' WHERE [BusinessEntityID] = 1
UPDATE [PersonPhone] SET PhoneNumber = '999-555-1212' WHERE [BusinessEntityID] = 1
WAITFOR DELAY '00:00:10'
ROLLBACK TRANSACTION

5) Once the deadlock has been generated, when I tried to download from Azure Portal I found that the process took too much time and I was not able to, just only running the following TSQL:

ALTER EVENT SESSION [deadlockJM] ON server STATE = STOP I was able to. I found that the size of the file initialy was 1 Gib but after stopping got the expected size of the information required in the definition of this extended event.

6) Just right now, using this extended event you could see the deadlock. Also, you could see this extended event is you are using the latest version of SSMS 18.1 under Management option of the instance.

Enjoy!