Monitoring Specific Table Using SQL Profiler

Introduction

This article helps you to understand how to use the SQL Profiler, and how to trace a particular table. This is the flow of the article.

  • Introduction to SQL Profiler
  • Why we need this
  • How to create SQL Profiler
  • Advantage & disadvantages of it
SQL Profiler

SQL Server Profiler is a graphical user interface. It is used to trace and monitor an instance of the Database Engine. You can capture and save the data about each event to a file or table to analyze later. In short, it’s a tool for tracing, recreating, and troubleshooting problems in RDBMS.

Why we need SQL profiler:

To monitor the performance of an instance of the SQL Server Database Engine.

To debug the Transact-SQL statements and stored procedures.

How to create SQL profiler and trace a tables event 

Open Microsoft SQL Server 2005, click Performance Tools >> SQL Server Profiler.

SQL Profiler

You will get the "Trace Properties" window.

SQL Profiler

It has two selection tabs,

  • General: It is used for general settings of tracing the Database Engine.
  • Event: It is used to add or remove some selected events for monitoring.

The General Section is divided into four sections.

Check the "Save to table" option and enter the database name, server name, table name, etc.

SQL Profiler

Then, click OK.

Check the "Set maximum rows".

SQL Profiler

Now, move to "Event Section" tab.

Just click on the "Column Filter Button". In this section, you can specify some condition (like or not like). Click on the "Text data" and write a condition:

If you want to trace a particular table, you can enter condition like %tablename%. My table name is userDetails so I will write %userDetails%

SQL Profiler

Then, click OK.

On the next window, click YES.

SQL Profiler

Now, click on the "Run" button. The following window will show up.

SQL Profiler

Move to your SQL Server and refresh your table. You will get the sampleTrace table that you created in SQL Profiler.

SQL Profiler

Perform some operations on the table. You will see that any query you are executing is tracing all events of the table.

SQL Profiler

You can perform any event using SQL Profiler. If you want only to trace Insert statement, you can do that like this:

%INSERT INTO %tablename%

You can do the same thing with Update and Delete statements.

%update %userDetails% 

%delete %userDetails%

Advantages & disadvantages of SQL Profiler

Advantage - Monitors the system performance in a better way.

Collects the complete Transact-SQL load of a production system and replays it in your test environment.

Disadvantage - It will increase the load on Server.


Similar Articles