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

I'm not seeing those in SQL Server 2005.

In my experience, I look at SQL:StmtStarting AND SP:StmtStarting - you can filter TextData on %TABLE_NAME%. This will even catch things inside SPs when you use SP:StmtStarting.

It's not bulletproof, because it has to use the LIKE syntax, but it might get you what you are looking for.

Answer 2

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.

Answer 3

There is a way to do that with Profiler, but it's got a heck of a performance impact.

Instead, can you clarify your question with the version of SQL Server you're using? If you're using SQL Server 2008, look into the Audit object, which is designed to do exactly that, plus it's got a very low performance impact.

Here's an article explaining how to set up an audit:

Implementing Security Audits in SQL Server 2008

Other posters have noted that you can filter TextData on object name, but that doesn't work if someone uses a view to access the object.