How To Identify Long Running Queries Using SQL Server Profiler

Your queries are running slow and you want to determine which queries are running slowly. You can perform this operation by using SQL Server Profiler. We are opening SQL Server Profiler with Run As Administrator as follows.

Click File->New.

On the screen that appears, write the instance name in the “Server Name” section and click Connect.

We go to Event Selection on the screen and select “RPC: Completed” and “SQL: BatchCompleted” as below and we deselect others. Then click “Show all columns” and deselect  and select again “RPC: Completed” and “SQL: BatchCompleted”. Thus, we have chosen all the columns.

Then click on Column Filters and the corresponding Database Name as below. You should write your database name between the %% marks in Like.

Click OK and then click Column filters again. In the duration tab, we write 1000 to “greater than or equal”.(The value here is in milliseconds. Since we write 1000, we’ll catch queries that take longer than 1 second.)

Click OK and then run the profiler.

We have created filter to capture queries that take longer than 1 second. You can also filter by using other parameters according to your needs.

For Example;

You can filter;

• According to the application server that sent the query by using HostName.
• According to the login that sends the query using LoginName.
• According to the contents of the query using TextData.

You can also filter using other parameters. I have shared the most used.

Well, you’ve watched the trace for a while, but reading through the trace may not be easy. Therefore, you may want to import the trace into the database as a table.

We stop Trace and click File-> Save As-> Trace Table.

We select the instance where the database is located from the screen that appears.

From the next screen,

Select the database where we will save trace in the” database: “section.

Write the name of the trace table in the “table:” section and click OK.

We can then go to the related database and query the trace table as we want.