使用 SQL Server Profiler 记录表访问 [英] Log table access using SQL Server Profiler

问题描述

有没有办法使用 Profiler 来确定表是否正在被查询访问?

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

我看到一个名为 Object:Opened 的事件(指示对象何时被访问，例如 SELECT、INSERT 或 DELETE 语句)和 Object:Closed，但这些似乎不起作用.

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.

特别是，我使用 Object:Opened 和 Object:Closed 创建了一个简单的跟踪，没有过滤器(除了标准的Application Name not like 'SQL Profiler'"过滤器)并运行 SELECT TOP 1 * FROM TableName，但没有报告任何事件.

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.

那么，有没有办法使用 Profiler 来确定是否正在从表中选择?

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

推荐答案

它可能有助于调查 SQL 正在获取的锁.Select 语句通常会获取共享锁 (LCKMS)，因此您可以对此进行过滤.

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

在探查器中查找 Locks:Acquired 事件.ObjectID 将解析为您可以使用 OBJECT_NAME(objectid) 轻松查找的表.Mode 会告诉你被获取的锁类型是 3.有关更多信息，请查看 这里.

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.

这篇关于使用 SQL Server Profiler 记录表访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！