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.

How to make Dedicated Admin Connection to SQL Server in Single User Mode

Some time there is a need to start SQL Server Service in DAC mode (Dedicated Administrator Connection) (Emergency Mode). 

Enable Remote Admin Connection using sp_configure so a dedicated connection can be made when SQL Server Start in single user mode. 

sp_configure ‘remote admin connections’ ,1 
reconfigure with override

 

  

 

 

 

C:\Program Files\Microsoft SQL Server\MSSQL10.BI\MSSQL\Binn>sqlservr.exe -sBI(instance name) -m 

Note : Before stopping SQL Server Service Stop the SQL Server Reporting Service as it will make the first connection (Connection Pooling) as SQL Server Service starts in Single User Mode. 

 To create a DAC Connection open a new commond prompt and connect to sqlcmd 

 

  

Stop SQL Server Service and start again using option -m from command Prompt.

 

  

  

 

 

 

 

Start SQL Server Service in Single-User Mode

Under certain circumstances, you may have to start an instance of Microsoft SQL Server in single-user mode by using the startup option -m. For example, you may want to change server configuration options or recover a damaged master database or other system database. Both actions require starting an instance of SQL Server in single-user mode.

When you start an instance of SQL Server in single-user mode, note the following:

• Only one user can connect to the server.
• The CHECKPOINT process is not executed. By default, it is executed automatically at startup.