Using SQL Server Profiler

In this article, let's discuss how the SQL Server Profiler can be used in our day to day programming applications.

What is SQL Server Profiler

The SQL Server Profiler is a performance tool which included with any version of SQL Server. It generally shows how SQL Server accesses the database internally. This tool is used by developers as well as a performance engineering team to analyze which SQL statements take more time to execute. It shows information like, how many reads and writes happened in a transaction, the duration it takes to complete the transaction and information such as that is displayed to show complete information of events occurring in the database end.

Why SQL Server Profiler

Consider a scenario like, you have made a request to an aspx page, it took 20 seconds to load the page. So it's a performance issue here. Here we need to find out what takes the most amount of time, either in the front end or in the back end. In a single request of an aspx page, there may be n number of database hits based on the functionality; here we need to find out what are all the SQL statements executed in this page request. There the SQL Server Profiler helps you to trace the information on the page request.

How to Use SQL Server Profiler

Enter the profiler keyword in run command; it will launch the profiler window to connect to the appropriate database. Once the database is connected, the trace will be started; now click the page where you want to trace the information. Stop the trace once the page request is completed; now you can see all the transactions that are performed in that single page request.

Tthe trace information will be displayed in the screen like below.



EventClass - Event Name occurred

TextData - Stored Procedure name or the SQL Statement that is executed

Application Name - Which application invoked this SQL statement

Like that various information will be tracked using the profiler.

Different Scenarios for Using SQL Server Profiler

• To find the worst performing queries or stored procedures
• To monitor Transact- SQL activity per user
• To view the connections involved in a Deadlock

Step By Step to Use Profiler

1. Launch Profiler Tool from Run Command
   
   
    
2. Connect to the appropriate Database
   
   
    
3. Configure the Properties
   
   
   
   Here you can save the trance information to a file or store the information in a table and you can also select the template through the drop down in the properties based on your requirement.
    
4. Select the Events that you need
   
   
   

You can organize the columns as well as apply the column filters through the button provided in the dialog box. Finally click the run button to monitor the events happening behind the screen.