Menu
Here is a way to set up an extended events session that can be used to collect blocked processes and deadlock records. I use similar code to collect this type of data each day and have a job that pulls the data into tables which can be used to evaluate the resources that are being blocked and deadlocked on.
When checking for blocked process records, you must set the blocked process threshold in the configuration. I have mine configured with the threshold set to 30 seconds. This means that blocked process reports are generated every 30 seconds.
EXEC sp_configure 'show advanced options', 1 ; GO RECONFIGURE ; GO /* Enable the bpr threshold */ EXEC sp_configure 'blocked process threshold', '30'; -- 30 seconds RECONFIGURE GO
Here is the extended events session to set up. I have it set to write what it collects to a file so that I won’t miss anything over time.
-- create the extended events session
CREATE EVENT SESSION [BPR_DL_monitor] ON SERVER
ADD EVENT sqlserver.blocked_process_report(
ACTION(package0.event_sequence,sqlserver.client_app_name,
sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,
sqlserver.database_name,sqlserver.query_hash,sqlserver.query_plan_hash,
sqlserver.request_id,sqlserver.server_instance_name,
sqlserver.server_principal_name,sqlserver.session_id,
sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)),
ADD EVENT sqlserver.xml_deadlock_report(
ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_hostname,
sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,
sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.request_id,
sqlserver.server_instance_name,sqlserver.server_principal_name,
sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack))
ADD TARGET package0.event_file(SET filename=N'S:\xEvents\BPR_DL_monitor.xel',max_file_size=(20),max_rollover_files=(0))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,
MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
/* Start the Extended Events session */
ALTER EVENT SESSION [BPR_DL_monitor] ON SERVER STATE = START;
I use a job to start and stop the extended events session every day. The collected data is pulled into tables after the extended events xml is shredded for information on the victim and the blocker/owner. I usually want to know what resource is causing blocking/deadlocking and where it is coming from.
/* Stop the Extended Events session */ ALTER EVENT SESSION [BPR_DL_monitor] ON SERVER STATE = STOP;
Here are the tables that I use to pull data into. There are more elements in the XML that you can import if needed but these seem to work for me.
CREATE TABLE [dbo].[BlockedProcessReport]( [RecID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, [EventTime] [datetime] NULL, [Duration] [real] NULL, [DatabaseId] [int] NULL, [ObjectId] [int] NULL, [TransactionId] [bigint] NULL, [vctmCurrentDb] [tinyint] NULL, [vctmCurrentDbName] [nvarchar](128) NULL, [vctmSpid] [int] NULL, [vctmTransCount] [int] NULL, [vctmLastBatchStarted] [datetime] NULL, [vctmLastBatchCompleted] [datetime] NULL, [vctmLastTranStarted] [datetime] NULL, [vctmWaitTime] [bigint] NULL, [vctmLogUsed] [bigint] NULL, [vctmLockMode] [nvarchar](8) NULL, [vctmStatus] [nvarchar](32) NULL, [vctmIsolationLevel] [nvarchar](64) NULL, [vctmTransactionName] [nvarchar](64) NULL, [vctmWaitResource] [nvarchar](128) NULL, [vctmWaitResourceDecoded] [nvarchar](512) NULL, [vctmHostname] [nvarchar](128) NULL, [vctmLoginName] [nvarchar](128) NULL, [vctmInputbuf] [varchar](2000) NULL, [blkrCurrentDb] [tinyint] NULL, [blkrCurrentDbName] [nvarchar](128) NULL, [blkrSpid] [int] NULL, [blkrTransCount] [int] NULL, [blkrLastBatchStarted] [datetime] NULL, [blkrLastBatchCompleted] [datetime] NULL, [blkrLastTranStarted] [datetime] NULL, [blkrWaitTime] [bigint] NULL, [blkrLockMode] [nvarchar](8) NULL, [blkrStatus] [nvarchar](32) NULL, [blkrIsolationLevel] [nvarchar](64) NULL, [blkrTransactionName] [nvarchar](64) NULL, [blkrWaitResource] [nvarchar](128) NULL, [blkrWaitResourceDecoded] [nvarchar](512) NULL, [blkrHostname] [nvarchar](128) NULL, [blkrLoginName] [nvarchar](128) NULL, [blkrInputbuf] [varchar](2000) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[DeadlockReport]( [RecID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, [EventTime] [datetime] NULL, [DatabaseId] [int] NULL, [vctmCurrentDb] [tinyint] NULL, [vctmCurrentDbName] [nvarchar](128) NULL, [vctmSpid] [int] NULL, [vctmTransCount] [int] NULL, [vctmLastBatchStarted] [datetime] NULL, [vctmLastBatchCompleted] [datetime] NULL, [vctmLastTranStarted] [datetime] NULL, [vctmWaitTime] [bigint] NULL, [vctmLogUsed] [bigint] NULL, [vctmLockMode] [nvarchar](8) NULL, [vctmStatus] [nvarchar](32) NULL, [vctmIsolationLevel] [nvarchar](64) NULL, [vctmTransactionName] [nvarchar](64) NULL, [vctmWaitResource] [nvarchar](128) NULL, [vctmWaitResourceDecoded] [nvarchar](512) NULL, [vctmHostname] [nvarchar](128) NULL, [vctmLoginName] [nvarchar](128) NULL, [vctmInputbuf] [varchar](2000) NULL, [ownrCurrentDb] [tinyint] NULL, [ownrCurrentDbName] [nvarchar](128) NULL, [ownrSpid] [int] NULL, [ownrTransCount] [int] NULL, [ownrLastBatchStarted] [datetime] NULL, [ownrLastBatchCompleted] [datetime] NULL, [ownrLastTranStarted] [datetime] NULL, [ownrWaitTime] [bigint] NULL, [ownrLockMode] [nvarchar](8) NULL, [ownrStatus] [nvarchar](32) NULL, [ownrIsolationLevel] [nvarchar](64) NULL, [ownrTransactionName] [nvarchar](64) NULL, [ownrWaitResource] [nvarchar](128) NULL, [ownrWaitResourceDecoded] [nvarchar](512) NULL, [ownrHostname] [nvarchar](128) NULL, [ownrLoginName] [nvarchar](128) NULL, [ownrInputbuf] [varchar](2000) NULL ) ON [PRIMARY] GO
And here is how to pull the collected data into tables. I shred the xml as I go and as long as I first pull the extended events xml into a temporary table it goes fairly quickly.
/*
BLOCKED PROCESSES
*/
IF EXISTS (SELECT name FROM tempdb.sys.objects WHERE name like N'#XEventBPR%' AND type = 'U')
DROP TABLE #XEventBPR
CREATE TABLE #XEventBPR (ID INT PRIMARY KEY IDENTITY (1,1), BPR_XML [xml] NOT NULL)
INSERT INTO #XEventBPR (BPR_XML)
SELECT CAST([XML DATA] AS XML) AS BPR_XML
FROM
(SELECT OBJECT_NAME AS [Event],
CONVERT(XML, event_data) AS [XML DATA]
FROM sys.fn_xe_file_target_read_file('S:\xEvents\BPR_DL_monitor*.xel', null, null, null)) AS v
WHERE v.Event = 'blocked_process_report'
INSERT INTO BlockedProcessReport WITH (TABLOCKX)
SELECT
BPR_XML.value('(/event/@timestamp)[1]','DATETIME') AS EventTime,
BPR_XML.value('(/event/data[@name=''duration'']/value)[1]','BIGINT') AS Duration,
BPR_XML.value('(/event/data[@name=''database_id'']/value)[1]','INT') AS DatabaseId,
BPR_XML.value('(/event/data[@name=''object_id'']/value)[1]','INT') AS ObjectId,
BPR_XML.value('(/event/data[@name=''transaction_id'']/value)[1]','BIGINT') AS TransactionId,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocked-process/process/@currentdb)[1]','int') as vctmCurrentDb,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocked-process/process/@currentdbname)[1]','nvarchar(128)') as vctmCurrentDbName,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocked-process/process/@spid)[1]','int') as vctmSpid,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocked-process/process/@trancount)[1]','int') as vctmTransCount,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocked-process/process/@lastbatchstarted)[1]','datetime') as vctmLastBatchStarted,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocked-process/process/@lastbatchcompleted)[1]','datetime') as vctmLastBatchCompleted,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocked-process/process/@lasttranstarted)[1]','datetime') as vctmLastTranStarted,
isnull(BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocked-process/process/@waittime)[1]','bigint'),0) as vctmWaitTime,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocked-process/process/@logused)[1]','bigint') as vctmLogUsed,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocked-process/process/@lockMode)[1]','nvarchar(8)') as vctmLockMode,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocked-process/process/@status)[1]','nvarchar(32)') as vctmStatus,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocked-process/process/@isolationlevel)[1]','nvarchar(64)') as vctmIsolationLevel,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocked-process/process/@transactionname)[1]','nvarchar(64)') as vctmTransactionName,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocked-process/process/@waitresource)[1]','nvarchar(128)') as vctmWaitResource,
'' as vctmWaitResourceDecoded,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocked-process/process/@hostname)[1]','nvarchar(128)') as vctmHostname,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocked-process/process/@loginname)[1]','nvarchar(128)') as vctmLoginName,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocked-process/process/inputbuf)[1]','varchar(1200)') as vctmInputbuf,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocking-process/process/@currentdb)[1]','int') as blkrCurrentDb,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocking-process/process/@currentdbname)[1]','nvarchar(128)') as blkrCurrentDbName,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocking-process/process/@spid)[1]','int') as blkrSpid,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocking-process/process/@trancount)[1]','int') as blkrTransCount,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocking-process/process/@lastbatchstarted)[1]','datetime') as blkrLastBatchStarted,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocking-process/process/@lastbatchcompleted)[1]','datetime') as blkrLastBatchCompleted,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocking-process/process/@lasttranstarted)[1]','datetime') as blkrLastTranStarted,
isnull(BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocking-process/process/@waittime)[1]','bigint'),0) as blkrWaitTime,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocking-process/process/@lockMode)[1]','nvarchar(8)') as blkrLockMode,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocking-process/process/@status)[1]','nvarchar(32)') as blkrStatus,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocking-process/process/@isolationlevel)[1]','nvarchar(64)') as blkrIsolationLevel,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocking-process/process/@transactionname)[1]','nvarchar(64)') as blkrTransactionName,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocking-process/process/@waitresource)[1]','nvarchar(128)') as blkrWaitResource,
'' as blkrWaitResourceDecoded,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocking-process/process/@hostname)[1]','nvarchar(128)') as blkrHostname,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocking-process/process/@loginname)[1]','nvarchar(128)') as blkrLoginName,
BPR_XML.value('(/event/data[@name=''blocked_process'']/value/blocked-process-report[1]/blocking-process/process/inputbuf)[1]','varchar(1200)') as blkrInputbuf
FROM #XEventBPR
ORDER BY BPR_XML.value('/blocked-process-report[1]/blocked-process[1]/process[1]/@lastbatchstarted', 'datetime'), EventTime
/*
DEADLOCKS
*/
IF EXISTS (SELECT name FROM tempdb.sys.objects WHERE name like N'#XEventDL%' AND type = 'U')
DROP TABLE #XEventDL
CREATE TABLE #XEventDL (ID INT PRIMARY KEY IDENTITY (1,1), DL_XML [xml] NOT NULL)
INSERT INTO #XEventDL (DL_XML)
SELECT CAST([XML DATA] AS XML) AS DL_XML
FROM
(SELECT OBJECT_NAME AS [Event],
CONVERT(XML, event_data) AS [XML DATA]
FROM sys.fn_xe_file_target_read_file('S:\xEvents\BPR_DL_monitor*.xel', null, null, null)) AS v
WHERE v.Event = 'xml_deadlock_report'
INSERT INTO DeadlockReport WITH (TABLOCKX)
SELECT
DL_XML.value('(/event/@timestamp)[1]','DATETIME') AS EventTime,
DL_XML.value('(/event/action[@name=''database_id'']/value)[1]','INT') AS DatabaseId,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[1]/@currentdb)[1]', 'int') as vctmCurrentDb,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[1]/@currentdbname)[1]', 'nvarchar(128)') as vctmCurrentDbName,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[1]/@spid)[1]', 'int') as vctmSpid,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[1]/@trancount)[1]', 'int') as vctmTransCount,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[1]/@lastbatchstarted)[1]', 'datetime') as vctmLastBatchStarted,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[1]/@lastbatchcompleted)[1]', 'datetime') as vctmLastBatchCompleted,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[1]/@lasttranstarted)[1]', 'datetime') as vctmLastTranStarted,
isnull(DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[1]/@waittime)[1]', 'bigint'),0) as vctmWaitTime,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[1]/@logused)[1]', 'bigint') as vctmLogUsed,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[1]/@lockMode)[1]', 'nvarchar(8)') as vctmLockMode,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[1]/@status)[1]', 'nvarchar(32)') as vctmStatus,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[1]/@isolationlevel)[1]', 'nvarchar(64)') as vctmIsolationLevel,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[1]/@transactionname)[1]', 'nvarchar(64)') as vctmTransactionName,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[1]/@waitresource)[1]', 'nvarchar(128)') as vctmWaitResource,
'' as vctmWaitResourceDecoded,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[1]/@hostname)[1]', 'nvarchar(128)') as vctmHostname,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[1]/@loginname)[1]', 'nvarchar(128)') as vctmLoginName,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[1]/inputbuf)[1]', 'varchar(1200)') as vctmInputbuf,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[2]/@currentdb)[1]', 'int') as ownrCurrentDb,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[2]/@currentdbname)[1]', 'nvarchar(128)') as ownrCurrentDbName,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[2]/@spid)[1]', 'int') as ownrSpid,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[2]/@trancount)[1]', 'int') as ownrTransCount,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[2]/@lastbatchstarted)[1]', 'datetime') as ownrLastBatchStarted,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[2]/@lastbatchcompleted)[1]', 'datetime') as ownrLastBatchCompleted,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[2]/@lasttranstarted)[1]', 'datetime') as ownrLastTranStarted,
isnull(DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[2]/@waittime)[1]', 'bigint'),0) as ownrWaitTime,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[2]/@lockMode)[1]', 'nvarchar(8)') as ownrLockMode,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[2]/@status)[1]', 'nvarchar(32)') as ownrStatus,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[2]/@isolationlevel)[1]', 'nvarchar(64)') as ownrIsolationLevel,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[2]/@transactionname)[1]', 'nvarchar(64)') as ownrTransactionName,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[2]/@waitresource)[1]', 'nvarchar(128)') as ownrWaitResource,
'' as ownrWaitResourceDecoded,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[2]/@hostname)[1]', 'nvarchar(128)') as ownrHostname,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[2]/@loginname)[1]', 'nvarchar(128)') as ownrLoginName,
DL_XML.value('(/event/data[@name=''xml_report'']/value/deadlock/process-list/process[2]/inputbuf)[1]', 'varchar(1200)') as ownrInputbuf
FROM #XEventDL
ORDER BY EventTime
You will notice that I have some columns with “WaitResourceDecoded” in the name that are not populated with anything. This is because I want to get the Wait Resource afterwards. I have found that decoding wait resources can take a while and only want to do this periodically.
Here are some ways to decode your wait resources so that you can do performance work or report on things. Of course, Kendra Little is the authority on this stuff. You will see that I did borrow from her. 😊 Decoding Key and Page WaitResource for Deadlocks and Blocking (kendralittle.com)
/****************************
Decode Waitresource
****************************/
-- EXAMPLES
-- OBJECT = Database_Id : Object_Id : Index_Id
-- OBJECT: 9:82815357:0
-- KEY = Database_Id : Hobt_id : Magic Hash Value
-- KEY: 6:72057594041991168 (ce52f92a058c)
-- PAGE = Database_Id : FileId : PageNumber
-- PAGE: 6:3:70133
-- Get the database from the Database_Id
SELECT [name] FROM sys.databases WHERE database_id = 'Database_Id from example';
/****************************
SWITCH to database that = Database_Id for the rest
****************************/
-- FOR KEY LOCK WAITS
-- KEY = Database_Id : Hobt_id : Magic Hash Value
-- EXAMPLE - KEY: 6:72057594041991168 (ce52f92a058c)
SELECT
sc.name AS SchemaName,
so.name AS ObjectName,
si.name AS IndexName
FROM sys.partitions AS p
JOIN sys.objects AS so ON p.object_id=so.object_id
JOIN sys.indexes AS si ON p.index_id=si.index_id and p.object_id=si.object_id
JOIN sys.schemas AS sc ON so.schema_id=sc.schema_id
WHERE hobt_id = 'Hobt_id from example';
-- Decode Magic Hash Value to get the record if you want to
SELECT *
FROM 'schema_name from above'.'object_name from above' (NOLOCK)
WHERE %%lockres%% = '(Magic Hash Value)';
-- FOR OBJECT WAITS
-- OBJECT = Database_Id : Object_Id : Index_Id
-- EXAMPLE - OBJECT: 9:82815357:0
SELECT
sc.name AS SchemaName,
so.name AS ObjectName,
si.name AS IndexName
FROM sys.objects AS so
JOIN sys.indexes AS si ON so.object_id = si.object_id
JOIN sys.schemas AS sc ON so.schema_id = sc.schema_id
WHERE so.object_id = 'Object_Id from example'
AND si.index_id = 'Index_Id from example';
-- FOR PAGE LOCK WAITS
-- PAGE = Database_Id : FileId : PageNumber
-- EXAMPLE - PAGE: 6:3:70133
SELECT *
FROM sys.dm_db_page_info ('Database_Id from example', 'FileId from example', 'PageNumber from example', DEFAULT);
-- Use Object_Id and Index_Id from data returned in query above to find the table and index
SELECT
sc.name AS SchemaName,
so.name AS ObjectName,
si.name AS IndexName
FROM sys.objects AS so
JOIN sys.indexes AS si ON so.object_id = si.object_id
JOIN sys.schemas AS sc ON so.schema_id = sc.schema_id
WHERE so.object_id = 'Object_Id from sys.dm_db_page_info'
AND si.index_id = 'Index_Id from sys.dm_db_page_info';
The final part is to report on the collected data. Here are some queries that I use to start the process of tuning.
/*********************** Evaluate BPRs ***********************/ -- all bprs in last 7 days SELECT * FROM BlockedProcessReport WHERE EventTime > GETDATE() - 7 ORDER BY EventTime -- top objects (unique victim + owner) bpr combos in last 14 days SELECT vctmWaitResource, blkrWaitResource, COUNT(*) AS NumOccurrances FROM BlockedProcessReport WHERE EventTime > GETDATE() - 7 GROUP BY vctmWaitResource, blkrWaitResource ORDER BY NumOccurrances DESC -- top databases involved in blockers in last 14 days SELECT blkrCurrentDbName, COUNT(*) AS NumOccurrances FROM BlockedProcessReport WHERE EventTime > GETDATE() - 7 GROUP BY blkrCurrentDbName ORDER BY NumOccurrances DESC -- top queries involved in blockers in last 14 days SELECT blkrCurrentDbName, blkrInputbuf, COUNT(*) AS NumOccurrances FROM BlockedProcessReport WHERE EventTime > GETDATE() - 7 GROUP BY blkrCurrentDbName, blkrInputbuf ORDER BY NumOccurrances DESC /*********************** Evaluate DLs ***********************/ -- all deadlocks in last 7 days SELECT * FROM DeadlockReport WHERE EventTime > GETDATE() - 7 ORDER BY EventTime -- top objects (unique victim + owner) deadlock combos in last 7 days SELECT vctmWaitResource, ownrWaitResource, COUNT(*) AS NumOccurrances FROM DeadlockReport WHERE EventTime > GETDATE() - 7 GROUP BY vctmWaitResource, ownrWaitResource ORDER BY NumOccurrances DESC -- top databases involved in deadlocks in last 7 days SELECT ownrCurrentDbName, COUNT(*) AS NumOccurrances FROM DeadlockReport WHERE EventTime > GETDATE() - 7 GROUP BY ownrCurrentDbName ORDER BY NumOccurrances DESC -- top queries involved in deadlocks in last 7 days SELECT ownrCurrentDbName, ownrInputbuf, COUNT(*) AS NumOccurrances FROM DeadlockReport WHERE EventTime > GETDATE() - 7 GROUP BY ownrCurrentDbName, ownrInputbuf ORDER BY NumOccurrances DESC
Hope this is helpful to anyone needing to get blocking and deadlocking information.
Here are some helpful links on the subjects in this blog post:
blocked process threshold (server configuration option) – SQL Server | Microsoft Learn
Quickstart: Extended Events – SQL Server | Microsoft Learn
sys.dm_db_page_info (Transact-SQL) – SQL Server | Microsoft Learn
For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!
Session expired
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.
[…] Lori Brown’s speaking my language: […]