3

TL;DR

I happened upon a feature in SQL Server Management Studio v17.9 which I was not aware of. It looks a bit like the EXPLAIN found in Oracle, PostgreSQL and MySQL. Where is this feature documented?

How to Turn The Feature On

I was running the following query with the options Include Actual Execution Plan (Ctrl + M) and Include Live Query Statistics turned on:

    SELECT sdes.session_id
          ,sdes.[status]
          ,sdes.login_name
          ,sdes.[host_name]
          ,sder.blocking_session_id
          ,sdb.name
          ,sdes.cpu_time
          ,sdes.logical_reads --optionally: + sdes.reads + sdes.writes
          ,sdes.last_request_start_time
          ,sdes.program_name
          ,sdes.session_id
          ,sder.request_id
          ,dest.[text]
    FROM   sys.dm_exec_sessions             AS sdes
           LEFT JOIN sys.dm_exec_connections     AS sdec
                ON  sdes.session_id = sdec.session_id
           JOIN sys.databases               AS sdb
                ON  sdes.database_id = sdb.database_id
           LEFT JOIN sys.dm_exec_requests  AS sder
                ON  sdes.session_id = sder.session_id
           CROSS APPLY sys.dm_exec_sql_text(sdec.most_recent_sql_handle) AS dest
    WHERE 1=1
    AND sdb.name = ''

Outcome

This produced four results tabs:

  • Results
  • Messages
  • Live Query Statistics
  • Execution Plan

Special Results Tab

When I switched to the Results tab, I noticed a result set just below the actual results which looks like this:

SQL Server Management Studio - Explain Plan


Observations

If I turn on either the Include Actual Execution Plan (Ctrl + M) option or the Include Live Query Statistics option, then the SQL Server Explain isn't displayed. The "SQL Server Explain" is only displayed if both options are turned on.


Questions

  1. Can this feature be turned on without having to activate both Include Actual Execution Plan (Ctrl + M) option and the Include Live Query Statistics?
  2. Where is this neat little feature documented?

Prerequisites

The following components and/or software packages are involved in the scenario:

Server

  • Windows Server 2016
  • SQL Server 2016 SP1

Client

  • Windows 10 Version 1807 (Build 17134)
  • SQL Server Management Studio v17.9
    • Microsoft SQL Server Management Studio 14.0.17285.0
    • Microsoft Analysis Services Client Tools 14.0.1016.283
    • Microsoft Data Access Components (MDAC) 10.0.17134.1
    • Microsoft MSXML 3.0 6.0
    • Microsoft Internet Explorer 9.11.17134.0
    • Microsoft .NET Framework 4.0.30319.42000
    • Operating System 6.3.17134

SQL Server Management Studio v17.9

  • Include Actual Execution Plan (Ctrl + M) turned ON
  • Include Live Query Statistics turned ON
  • i am reporting the same on 17.4, this seems to be around on earlier versions of ssms ibb.co/ZMP35fF – Randi Vertongen Nov 28 '18 at 12:50
  • It's also in 2016 – RIanGillis Nov 29 '18 at 15:44
6

No, not a feature - it's turning one of these on:

SET SHOWPLAN_TEXT ON;  
SET STATISTICS PROFILE ON;

The SHOWPLAN_TEXT option and STATISTICS PROFILE option are well-documented and have been around for over a decade, but I don't know when SSMS started turning it on for the combo of live query plans and actual plans. I would consider it a bug.

Here's the SSMS bug I filed for it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.