Monitoring Specific Table Using SQL Profiler

Today I am writing about a simple trick to capture and monitor all the queries, batches and stored procedures executed against a table in SQL Server. I have used SQL Server Profiler for monitoring specific table in a database and capturing the events executed against it.

Here are the steps for using profiler to monitor the events against a table:

  1. From the profiler trace window, go to menu Files > Properties.
  2. In the Trace Properties window, select Blank for Use the template.
  3. Go to Events Selections tab.
  4. From the left Events panel, Expand Stored Procedures and select:
    1. RPC:Starting.
    2. SP:StmtStarting.
    3. In addition, you can select RPC:Completed and SP:StmtCompleted. These events will help you to get the time duration of the statement execution.
  5. From TSQL event category, select:
    1. SQL:BatchStarting.
    2. SQL:StmtStarting.
    3. In addition, you can select SQL:BatchCompleted and SQL:StmtCompleted.
  6. Make sure the check box Show all columns is selected.
  7. Press the Columns Filters… button below.
  8. In the Filter pop-up window, from the left panel select TextData.
  9. From the right panel, under Like tree node, enter the table name padded with percentage symbol. For example %PurchaseOrders%.
    Monitoring Specific Table Using SQL Profiler
  10. Optionally, if you want to filter for specific database then:
    1. from the left panel, select DatabaseName.
    2. From the right panel, under Like tree node, enter the database name.
  11. and press OK.
  12. Then, press Run button to start the trace.

You will see the trace is filtering and showing only the queries and other events running against the table.

Related Articles


Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.