2

With very long queries in SQL Server, sometimes the execution plan can get very involved and will require a scrolling through the query text and the execution plan.

Except in between the execution plan and the SQL there is a reiteration of the SQL in a single string and potential indexes. When you hover over either of these it will expand to show you the whole query or suggested index. Per the image below.

enter image description here

The text will never appear and will lock up SSMS. I don't see the benefit to the SQL being shown in a tool tip.

Is there a way to shut it off?

Elias
  • 2,602
  • 5
  • 28
  • 57

2 Answers2

1

A possible way around this is to grab the XML query plan. You can then copy/paste the XML into SQL Sentry's Plan Explorer, a free download. Plan Explorer has excellent query plan visualization.

To get the actual XML query plan:

  • Run the query in SSMS with Include Actual Execution Plan turned on. If you can right-click somewhere without triggering the popup, you can choose Show Execution Plan XML....
  • You can run the T-SQL command set statistics xml on. It has to be the single statement in its batch (run it alone, or surround it with go.) If you try this option, make sure you turn off SSMS's query plan options, they interfere with set execution plan settings.
  • Run SQL Profiler and under the Events Selection tab check Show all events, then check Performance -> Showplan XML.
  • You can read cached execution plans from a dynamic management (sys.dm_) view:

(last two options from Justin's nice answer)

select  UseCounts
,       Cacheobjtype
,       Objtype
,       TEXT
,       query_plan
from    sys.dm_exec_cached_plans 
cross apply   
        sys.dm_exec_sql_text(plan_handle)
cross apply   
        sys.dm_exec_query_plan(plan_handle)
Community
  • 1
  • 1
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • I like this answer a lot because it is a great workaround, but I'm going to hold out for a bit hoping for a direct solution within SSMS. – Elias Aug 29 '14 at 14:35
0

If you're looking for an SSMS-integrated solution, Supratimas is a an SSMS add-in that provides an improved query plan visualization.

There's a free ad-supported version as well as a paid add-free version.

David Atkinson
  • 5,759
  • 2
  • 28
  • 35
  • I actually find that the local SSMS query plan viewer suffices, but that massive tooltip is a killer and pretty much crashes SSMS. – Elias Sep 02 '14 at 15:17
  • Sadly I doubt there's a way of fixing that issue, and it's unlikely that Microsoft will prioritise this. Have you tried the free add-in? If it works, it works! – David Atkinson Sep 03 '14 at 09:28