forum

Home / DeveloperSection / Forums / How to use SQL Profiler to find slow-performing queries in a database?

Ask Question
Steilla Mitchel

Steilla Mitchel

Follow
I completed my post-graduation in 2013 in the engineering field. Engineering is the application of science and math to solve problems. Engineers figure out how things work and find practical uses for scientific discoveries. Scientists and inventors often get the credit for innovations that advance the human condition, but it is engineers who are instrumental in making those innovations available to the world. I love pet animals such as dogs, cats, etc.

Can you answer this question?

Answer

1 Answers

Ashutosh Kumar Verma

Ashutosh Kumar Verma

18-Jul-2024

SQL Profiler to find slow-performing queries

Follow these detailed steps to identify slow queries on a database using SQL Server Profiler. This involves capturing and analyzing SQL queries that take a long time to execute:


Open SQL Server Profiler

SQL Server 2012 and later: Open SQL Server Management Studio (SSMS), then go to Tools > SQL Server Profiler.
For previous versions: Open SQL Server Profiler directly from the Start menu.


Connect to the SQL Server Instance

In SQL Server Profiler, click File > New Trace.
Depend on the SQL Server instance you want to monitor query performance.


Create a New Trace

Once created, the "Trace Properties" dialog appears.
Enter a name for your trace in the "Trace name" field for easy reference.

answer 6518921807045331


Configure Trace Properties

Events Selection Tab 

This is where you choose which events to capture.
Select relevant events
Performance-related Events- If you want to capture slow queries, you should include events that simplify the request context:

SQL:BatchCompleted (Gets SQL batch completion information)
RPC:Completed (contains information about stored procedure execution)
SQL:StmtCompleted (contains information about individual SQL statements)
 

Additional Events

Performance > Performance Accounting (for Performance Accounting) .
 

Add Relevant Columns
Click the Columns button on the Events Selection tab to select columns to add. Important characters for identifying slow queries are,
TextData (to view SQL statements) .
Duration (to see how long the query took to execute).
StartTime and EndTime (to capture execution time) .
CPU, Read, Write (to capture resources) .

answer 6518921807045331

 

Apply Filters

Click the Column Filters button to filter the captured text and focus on slower queries.
Filter by time,
In the "Edit Filter" dialog, select Duration and set a threshold (e.g., 100 milliseconds) to edit queries executed longer than this duration.

answer 6518921807045331


Filter by Database (if necessary)
Here, also filter by DatabaseName mindstickdb  if you want to focus on a specific database.

answer 6518921807045331

 

 

Start the Trace

  • Once you have configured the events and filters, click Run to start the trace.
  • Profiler will then capture real-time data based on your settings.

answer 6518921807045331

 

Monitor and Analyze the Trace

View the trace results to identify SQL Queries in mindstickdb database that exceed your duration limit.

Let's run some SQL queries in mindstickdb  database to verify the Duration in SQL Server Profiler,

USE mindstickdb
GO
SELECT * FROM UserProfile
SELECT * FROM Article
SELECT * FROM Forum

Here are executed three different queries in different time. Now check the duration in SQL Profiler Trace window,

answer 6518921807045331
 

there are only two SQL queries listed here which execution times longer than 100 milliseconds.

You can sort the results by the Duration column to quickly identify the slowest queries.

 

Save and Review Trace Data

To save the trace data for later analysis, go to File > Save As > Trace File or Trace Table.
Review the trace file or table to investigate the questions in detail. Always look for examples or specific questions that show high levels of time spent.

 

Stop/Close Trace

To close or stop the trace click on File menu -> click on Stop Trace option.

 

By following these steps, you can effectively use SQL Server Profiler to catch and analyze slow queries in your database, enabling you to take corrective actions to improve performance

 

Also, Read: How to setup a trace using SQL Profiler to monitor a specific database?

Liked By