Introduction to Server SQL Profile

INTRODUCTION

SQL Server Profiler is a tool through which you can monitor, debug and analyze all the activities happening in SQL server database. You can easily monitor the selected events you want. By using SQL Server Profiler you can monitor all the activities including normal transactions to deadlocks and identify poor performance queries.

Also, the SQL Profiler can help us create Trace (a collection of events obtained from Database), we can store trace results in a file or database and we can schedule our trace execution.

INSTALLATION

Download and install SQL Server Management Studio (Download link). Once installation is complete open SQL Management Studio and select SQL Server Profiler under Tools.

 

CONFIGURATION

When you select SQL Server Profiler, the following screen will appear.

After providing valid credentials, click on Connect button. You will be navigated to Trace Properties screen.

We have two tabs on this screen.
1. General
2. Events Selection
In General section we have
1. Trace Name: Provide Trace name.
2. Trace Provider Name: Pre defined on the basis SQL Server.
3. Trace Provider Type: Pre defined on the basis SQL Server.
4. Use the Template: Different Templates are available or you can create your own template.
5. Save to file: You can save your trace results in file.
6. Save to table: You can save your trace in database as table.
7. Enable trace stop time: You can schedule your trace.

Following are the traces with their purposes which are available in SQL Server Profiler by default.

If you select Save to table then you will be asked, in which database you want save your trace results.

Then we have Events Selection section

 

An Event is an action that occurs in SQL Server Database. If you mouse hover over the events, you can find the details for each event.

Following are the examples of some events:

• Select, Insert, Update, Delete queries
• User login and logout sessions
• Stored Procedures

You can display all events by checking “Show all events” checkbox. “Show all columns” checkbox will display you all the attributes of events. But you can customize your trace and select Events and Event Classes as per your needs. Your trace result will depend on your selected Events and Events Class.

Column Filters will allow you to filter the trace results and avoid unwanted records. Through Organize Columns you can change the sequence of selected events.

As a result, you are able to get detailed information about executed queries like time duration, memory consumption, start time, end time and all other events information you selected.