opc.three (12/12/2012)
This response from Jonathan Kehayias to a similar question talks about possibly using a technique where we would capture the "sqlserver.lock_acquired event with the sqlserver.sql_text and sqlserver.tsql_stack actions" which is very clever but probably will not work on a Database Snapshot because as I understand it no locks are taken in Snapshots because it is a read-only database. I'll be testing that theory out soon enough though.
This turned out to be an interesting exercise. Locks are still taken in both the read-only database as well as the snapshot, but less than in a user-database and different locks from each other still. See the code comments for details.
It looks like I am going to implement an XE session like this:
CREATE EVENT SESSION test_session
ON SERVER
ADD EVENT sqlserver.lock_acquired
(ACTION (sqlserver.sql_text)
WHERE
-- my table
resource_0 = 459179422
-- my snapshot
and database_id = 16
)
ADD TARGET package0.asynchronous_file_target
(SET filename = N'X:\test_session.xel',
max_file_size = 1024,
max_rollover_files = 10,
increment = 32,
metadatafile = N'X:\test_session.xem')
WITH (TRACK_CAUSALITY = OFF, MAX_DISPATCH_LATENCY = 5 SECONDS);
Edited predicate and code comments after confirming we do not need a filter on mode. Only one lock is opened on the object, sometimes it is a SCH_S and others it is an IS depending on the isolation level.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato