Monitor Queries against a Table

I recently had a need to monitor inserts against a particular table in order to determine what kind of locks they were acquiring. Being that we could not run traces on the system, I had to resort to a roll-your-own approach to monitoring the table. The goal was to determine what kind of locking was occurring, and it would also be nice to be able to associate it to the executing query.

The following code uses DMVs in order to trap a sample of the queries running against it. Please note that this will not trap all queries, however it will work in getting a good number of samples. This script will run in an endless loop so be sure to hit cancel at some point. Also, for some reason the SQL Statement will not always be trapped. It was not that important for me, as I mostly needed the locks, however if someone figures it out, please post.

[cc lang=”sql”]

— Capture query activity against a table using DMVs
DECLARE @TableName varchar(255);

— Specify the table you want to monitor
SET @TableName = ‘Sales.SalesOrderDetail’;

DECLARE @ObjectID int;
SET @ObjectID = (SELECT OBJECT_ID(@TableName));

IF OBJECT_ID(‘tempdb..##Activity’) IS NOT NULL
BEGIN
DROP TABLE ##Activity;
END;

— Create table
SELECT TOP 0 *
INTO ##Activity
FROM sys.dm_tran_locks WITH (NOLOCK);

— Add additional columns
ALTER TABLE ##Activity
ADD SQLStatement VARCHAR(MAX),
SQLText VARCHAR(MAX),
LoginName VARCHAR(200),
HostName VARCHAR(50),
Transaction_Isolation VARCHAR(100),
DateTimeAdded DATETIME;

DECLARE @Rowcount INT = 0;

WHILE 1 = 1
BEGIN

INSERT INTO ##Activity
SELECT dtl.*
,SQLStatement =
SUBSTRING
(
qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END – er.statement_start_offset)/2
)
,qt.text
,ses.login_name
,ses.host_name
,ses.transaction_isolation_level
,DateTimeAdded = GETDATE()
FROM sys.dm_tran_locks dtl WITH (NOLOCK)
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = dtl.request_session_id
LEFT JOIN sys.dm_exec_requests er WITH (NOLOCK)
ON er.session_id = dtl.request_session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE dtl.resource_associated_entity_id = @ObjectID;

SET @Rowcount = (SELECT @@ROWCOUNT)
IF @Rowcount > 100
BEGIN
BREAK;
END;
— Wait 50 milliseconds
WAITFOR DELAY ’00:00:00.50′;

END

SELECT *
FROM ##Activity
[/cc]