I can't test this theory at the moment, but based on the most recent capture data posted to GitHub, I would say that the reason that thee <process> node is empty is that it requires a currently running request (many of the attributes are found in sys.dm_exec_requests and not in sys.dm_exec_sessions) and without a currently running request, it can't report any details, similar to how doing an INNER JOIN between sys.dm_exec_requests and sys.dm_exec_sessions will exclude rows where a Session is active but is idle due to no current request.
Looking at the top set of data (monitorLoop values: 1748823, 1748824, 1748825, and 1748827) we can see the following:
- the
id of the blocked-process is the same in each case: process2552c1fc28, and the only attribute that is different is the waittime (understandably).
- the attributes of the
blocking-process nodes show differences in both lastbatchstarted and lastbatchcompleted
- the attributes of the
blocking-process nodes show identical values for spid and xactid
So, how can the SessionID and TransactionID of the blocking process be the same across 4 different query batches? Easy, an explicit transaction was started and then these batches were executed. And because these are seperate batches, there is time between them being submitted, at which point there is no current request, hence no process info to show (but the session and the transaction are still there).
In order to do additional research into this, you can capture helpful information from sys.dm_exec_requests and sys.dm_tran_locks by placing the following T-SQL in a SQL Server Agent "Transaction-SQL script (T-SQL)" Job Step, setting the "Database" to be the one you are researching (in this case it is the one with an ID of 6), and scheduling this job to run every 10 seconds. The T-SQL below will create the two tables in that same DB if they don't exist and then will populate the "Requests" table if any request is either blocking itself, or if it is a Delete or Update operation that is being blocked. If any requests are found, it will try to capture:
- Session and Request info on the blocking process (this part does not assume that there is an active Request, hence the
RIGHT JOIN to at least get the Session info)
- Connection info for the blocked and (hopefully) blocking processes.
the current locks for those same session_id's (just keep in mind that the lock info isn't guaranteed to be 100% accurate as that info can change in the time between those two statements executing; still, the info is good enough often enough to be worth capturing). This section is currently commented out.
SQL Server Agent T-SQL Job Step:
-- !! Remember to set the "Database" for the T-SQL Job Step to
-- the DB that has database_id = 6 !!
SET NOCOUNT ON;
IF (OBJECT_ID(N'dbo.tmpBlockingResearch_Requests') IS NULL)
BEGIN
-- Create requests capture table
SELECT SYSDATETIME() AS [CaptureTime], req.*,
ses.login_time, ses.[host_name], ses.[program_name], ses.host_process_id,
ses.client_version, ses.client_interface_name, ses.security_id,
ses.login_name, ses.nt_domain, ses.nt_user_name, ses.memory_usage,
ses.total_scheduled_time, ses.endpoint_id, ses.last_request_start_time,
ses.last_request_end_time, ses.is_user_process, ses.original_security_id,
ses.original_login_name, ses.last_successful_logon, ses.last_unsuccessful_logon,
ses.unsuccessful_logons, ses.authenticating_database_id
INTO dbo.tmpBlockingResearch_Requests
FROM sys.dm_exec_requests req
INNER JOIN sys.dm_exec_sessions ses
ON ses.[session_id] = req.[session_id]
WHERE 1 = 0;
END;
IF (OBJECT_ID(N'dbo.tmpBlockingResearch_Connections') IS NULL)
BEGIN
-- Create connections capture table
SELECT SYSDATETIME() AS [CaptureTime], con.*
INTO dbo.tmpBlockingResearch_Connections
FROM sys.dm_exec_connections con
WHERE 1 = 0;
END;
IF (OBJECT_ID(N'dbo.tmpBlockingResearch_Locks') IS NULL)
BEGIN
-- Create locks capture table
SELECT SYSDATETIME() AS [CaptureTime], loc.*
INTO dbo.tmpBlockingResearch_Locks
FROM sys.dm_tran_locks loc
WHERE 1 = 0;
END;
---------------------------------
DECLARE @SessionIDs TABLE (SessionID SMALLINT NOT NULL,
BlockingSessionID SMALLINT NOT NULL);
INSERT INTO dbo.tmpBlockingResearch_Requests
OUTPUT inserted.[session_id], inserted.[blocking_session_id]
INTO @SessionIDs ([SessionID], [BlockingSessionID])
SELECT SYSDATETIME() AS [CaptureTime], req.*,
ses.login_time, ses.[host_name], ses.[program_name], ses.host_process_id,
ses.client_version, ses.client_interface_name, ses.security_id,
ses.login_name, ses.nt_domain, ses.nt_user_name, ses.memory_usage,
ses.total_scheduled_time, ses.endpoint_id, ses.last_request_start_time,
ses.last_request_end_time, ses.is_user_process, ses.original_security_id,
ses.original_login_name, ses.last_successful_logon, ses.last_unsuccessful_logon,
ses.unsuccessful_logons, ses.authenticating_database_id
FROM sys.dm_exec_requests req
INNER JOIN sys.dm_exec_sessions ses
ON ses.[session_id] = req.[session_id]
WHERE ses.[is_user_process] = 1
AND req.[database_id] = DB_ID()
AND (
req.blocking_session_id IN (req.[session_id], -2, -3, -4)
OR (req.[command] IN (N'DELETE', N'UPDATE') AND req.[blocking_session_id] > 0)
);
-- Get at least session info, if not also request info, on blocking process
INSERT INTO dbo.tmpBlockingResearch_Requests
SELECT SYSDATETIME() AS [CaptureTime], req.*,
ses.login_time, ses.[host_name], ses.[program_name], ses.host_process_id,
ses.client_version, ses.client_interface_name, ses.security_id,
ses.login_name, ses.nt_domain, ses.nt_user_name, ses.memory_usage,
ses.total_scheduled_time, ses.endpoint_id, ses.last_request_start_time,
ses.last_request_end_time, ses.is_user_process, ses.original_security_id,
ses.original_login_name, ses.last_successful_logon, ses.last_unsuccessful_logon,
ses.unsuccessful_logons, ses.authenticating_database_id
FROM sys.dm_exec_requests req
RIGHT JOIN sys.dm_exec_sessions ses
ON ses.[session_id] = req.[session_id]
WHERE ses.[session_id] IN (SELECT DISTINCT [BlockingSessionID] FROM @SessionIDs);
-- If any rows are captured this time, try to capture their connection info
INSERT INTO dbo.tmpBlockingResearch_Connections
SELECT SYSDATETIME() AS [CaptureTime], con.*
FROM sys.dm_exec_connections con
WHERE con.[session_id] IN (
SELECT [SessionID]
FROM @SessionIDs
UNION -- No "ALL" so it does DISTINCT
SELECT [BlockingSessionID]
FROM @SessionIDs
);
/*
-- If any rows are captured this time, try to capture their lock info
INSERT INTO dbo.tmpBlockingResearch_Locks
SELECT SYSDATETIME() AS [CaptureTime], loc.*
FROM sys.dm_tran_locks loc
WHERE loc.[request_session_id] IN (
SELECT [SessionID]
FROM @SessionIDs
UNION -- No "ALL" so it does DISTINCT
SELECT [BlockingSessionID]
FROM @SessionIDs
);
*/
I think you should be able to reproduce this by opening one query tab and executing the following:
CREATE TABLE dbo.tmp (Col1 INT);
BEGIN TRAN;
INSERT INTO dbo.tmp (Col1) VALUES (1);
Then, open a second query tab and execute the following:
UPDATE dbo.tmp
SET Col1 = 2
WHERE Col1 = 1;
P.S. Just to have it stated, the only thing that does not make sense is that the request & session info – dbo.tmpBlockingResearch_Requests – still never contains rows for the blocking session. Yet I know that the table variable has the blocking session id in it as it did pull in the locks for both SessionIDs. This could point to a scenario in which a Transaction is allowed to stay open after the "connection" from the client is closed but the connection is still maintained due to Connection Pooling.
Best Answer
You can take a look at Jonathan's post here: An XEvent a Day (10 of 31) – Targets Week – etw_classic_sync_target
The provider you see in
logmanis for using the Event Tracing for Windows. I have never used it but from what I understand it is more for tracking events from your application or the Windows OS back into SQL Server. I do not think it is something that can be used to just track general performance of SQL Server like you would with an XEvent session you create in SQL Server.