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.
You will get the "Trace Properties" window.
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.
Then, click OK.
Check the "Set maximum rows".
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%
Then, click OK.
On the next window, click YES.
Now, click on the "Run" button. The following window will show up.
Move to your SQL Server and refresh your table. You will get the sampleTrace table that you created in SQL Profiler.
Perform some operations on the table. You will see that any query you are executing is tracing all events of the table.
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.