Using XE to track user table activity

Question

Using XE to track user table activity

DVSQL

Hall of Fame

Points: 3108
More actions
November 22, 2022 at 12:55 am

#4116256
Hey guys, I am trying to figure out the best and most lightweight way to capture the activity that affects a specific user table. I've created a test scenario for myself, but I can't seem to reproduce the stuff I would see in SQL Profiler. The structure of data is like this
```
USE DBA

GO

CREATE TABLE DVTest1
(DVTest1ID INT PRIMARY KEY IDENTITY(1,1)
,DVTest1Int INT
,DVTest1Var NVARCHAR(255))

GO

CREATE OR ALTER PROCEDURE PutDVTest1 (@col2 INT, @col3 NVARCHAR(255))
AS
INSERT INTO DBA.dbo.DVTest1
(
    DVTest1Int,
    DVTest1Var
)
VALUES (@col2, @col3)

GO

CREATE OR ALTER PROCEDURE DelTest1 

AS

DELETE FROM DBA.dbo.DVTest1

GO
```
These are really basic objects just for testing of the essentials. When I create a SQL Profiler trace, I select Tuning as template, add Column Filter for TextData to be like %DVTest1%. Then I do the same in SQL Extended Events, putting a filter for sql_text to be like %DVTest1%. Now here is the difference. The profiler captures absolutely everything, which means that it also captures the statement inside of the DelTest1, which has a DVTest1 reference inside. The extended event session only captures just the execution block of code, but not what's inside, therefore DelTest1 doesn't get recorded. What am I doing wrong here? I am trying to setup the XE the same way as I do with SQL Profiler, but I get different results. Why is that, and what am I missing?
November 25, 2022 at 2:56 pm

This was removed by the editor as SPAM

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 1

One difference between the two is that the text field, where you're looking for table names, is different between the rpc_completed event and the sql_batch_completed event. In fact, you can't really search inside the proc definition from within the XE event. I would instead get the object_id values for every proc that can hit the table and filter on those. It'll be more efficient than searching the text anyway. You can still search the text for the batch events.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning