Capture Query Execution Plans using SQL Server Profiler

It’s always like a nightmare for DBA to capture Query execution Plans in text format and study it in case of performance issues.

Here I will explore how SQL Server Profiler can be helpful in capturing Query Execution Plan in text format.

Note:  Always run SQL Server Profiler on server which is not production as it consumes server resources.

 Open SQL Server Profiler can connect to the SQL Server Instance from which u want to capture the Query Execution Plan.

In the Event Selection Tab uncheck all the default selected Events, Expand the [+] Performance Event and select Show Plan Statistics Profile.

Click on the column filter  and  filter the capture trace using database id (Production Database id) and Login name (connect using id which no one is using else it will be little difficult to figure out for which query the exercise is being done.

Click on the Run and start capturing the Plans,once u execute the query SQL Profilor will start caturing the plans as below.

It’s always good if u start reading the plan from bottom to top.It will help u understand how really execution plan work and the top most plan will be the result (total number of rows) which is the total number of rows as output so the query.

Leave a Reply

Your email address will not be published.