Log table access using SQL Server Profiler

Question

Is there a way to use Profiler to determine whether a table is being accessed by queries?

I saw an event named Object:Opened (Indicates when an object has been accessed, such as for SELECT, INSERT, or DELETE statements) and Object:Closed, but these do not seem to work.

In particular, I created a simple trace with both Object:Opened and Object:Closed with no filters (except the standard "Application Name not like 'SQL Profiler'" filter) and ran SELECT TOP 1 * FROM TableName, but no events were reported.

So, is there a way to use Profiler to determine if a table is being SELECTed from?

Answer 1

It may help to investigate the locks SQL is acquiring. Select statements will generally aquire shared Locks (LCKMS), so you can filter for this.

In profiler look for the Locks:Acquired event. The ObjectID will resolve to the table which you can easily lookup with OBJECT_NAME(objectid). The Mode will tell you the kind of lock being acquired shared locks are 3. For more information look here.