Using Extended Event For Tracking Updates To A Table

Question

I have a table that keeps getting updated, and no one can figure out where the updates are coming from. I suspect they are coming from Entity Framework, but I want to capture the UPDATE and associated information via an Extended Event to prove this theory.

I'm on SQL 2014 Enterprise, and trying to capture that information using the exec_prepared_sql and sql_statement_starting events. Here is what I have so far:

CREATE EVENT SESSION [Query Trace] ON SERVER 
ADD EVENT sqlserver.exec_prepared_sql(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%UPDATE %') AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%TableA%') AND [sqlserver].[database_id]=(123))),
ADD EVENT sqlserver.sql_statement_starting(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%UPDATE %') AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%TableA%') AND [sqlserver].[database_id]=(123)))
ADD TARGET package0.event_file(SET filename=N'E:\ExtendedEvent\Query-Trace.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)
GO

This seems to work well, I have tested it and been able to captured both a regular UPDATE statement and one called with sp_executesql. However, I am still missing something. The data in the table is still getting modified and this Extended Event is not capturing the UPDATE that does it.

So, my questions are:

1) Is there something else that I need to be watching in my Extended Event in order to capture UPDATES possibly coming from Entity Framework?

2) Is there something else I should be using instead of Extended Events for this?

Thanks!

Answer 1

When using SQL Server Enterprise Edition (or running any edition newer than 2016 SP1) I would recommend going with SQLAudit functionality. This will give you some great granualar information about who is touching your tables, and the commands that are being executed.

For something like this you would want to use a database audit specification along with a server audit (used to define where your audit will write to), and then scope it down to the table that you want to monitor. By then scoping changes to the public role you will capture any and all changes that happen.

This script should get you there (test in your dev environment, and replace the relevant pieces for the table(s) that you want to monitor).

USE [master]

GO

CREATE SERVER AUDIT [Audit-TblChanges]
TO FILE 
(   FILEPATH = N'C:\SQLAudit'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
)

GO

USE [YourDatabase]
GO

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-MyTable]
FOR SERVER AUDIT [Audit-TblChanges]
ADD (UPDATE ON OBJECT::[YourTable] BY [public]),
ADD (INSERT ON OBJECT::[YourTable] BY [public])

GO


USE [master]
GO
ALTER SERVER AUDIT [Audit-TblChanges] WITH (STATE = ON);

USE [YourDatabase]
GO
ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-MyTable] WITH (STATE = ON);
GO

You can quickly read the data back using the GUI, or use sys.fn_get_audit_file to query the data directly from SQL Server.

Answer 2

For anyone who doesn't have the SQLAudit functionality from this answer available, and is doing this kind of thing purely with Extended Events: there are more events you should be watching than just the two listed in the question.

I use this list of events as my starting point:

1. exec_prepared_sql
2. sql_statement_starting
3. sp_statement_starting
4. rpc_starting
5. sql_batch_starting

There may be some overlap between these, but I don't overthink it; I'd rather see multiple relevant events than none.

In particular, the table updates I'm troubleshooting right now are all coming from sp_statement_starting and rpc_starting events, neither of which the question writer was catching.