Using SQL Server Profiler

Introduction

Before reading this article, I will suggest you go through this: 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 happen 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 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 the 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.

The trace information will be displayed on 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

Conclusion

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.