If retrieving query plan xml in a result pane after applying SP1 clicking on valid plan shows xml instead of graphical plan

If retrieving query plan xml in a result pane after applying SP1 clicking on valid plan shows xml instead of graphical plan by edq

Closed

as Duplicate Help for as Duplicate

Type:	Bug
ID:	686096
Opened:	8/29/2011 2:36:50 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

If retrieving query plan xml in a result pane after applying SP1 clicking on valid plan shows xml instead of graphical plan. The same query on 2008 and 2008 R2 non-SP1 works correctly.

Details (expand)

English

SQL Server 2008 R2 SP1

Tools (SSMS, Agent, Profiler, Migration, etc.)

Windows Server 2008 R2 Enterprise (SP1)

US English

SELECT TOP 10 convert(varchar(8000),qt.text) AS 'SP Name',qp.query_plan, qs.execution_count AS 'Execution Count',

qs.total_worker_time/ISNULL(qs.execution_count, 1) AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_elapsed_time/ISNULL(qs.execution_count, 1) AS 'AvgElapsedTime',
qs.max_logical_reads, qs.max_logical_writes, qs.creation_time,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache',
qs.execution_count/ISNULL(DATEDIFF(Minute, qs.creation_time, GetDate()), 1) AS 'Calls/Minute',
qs.execution_count/ISNULL(DATEDIFF(Second, qs.creation_time, GetDate()), 1) AS 'Calls/Second'

FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp

ORDER BY qs.execution_count DESC

When clicking on showplan link in results set, opens up a new tab in SSMS with XML for the plan instead of graphical plan

Graphical Plan

X64

0 attachments

Posted by Microsoft on 11/23/2011 at 9:30 AM

Hi,

This is regarding the connect item 686096. We are happy to inform that the issue has been fixed. The fix will be available in a next path for SQL Server 2008 R2 SP1.

thank you

Seshagiri
PM, SSMS

Posted by Chandan jha on 9/29/2011 at 7:09 AM

It has happened to me after applying SP1 in 2008 R2 RTM version(64-bit).

Posted by Chandan jha on 9/29/2011 at 7:08 AM

I am having the same issue since I applied SP1 to 2008R2 version. I try to retrieve execution plans in the cache and none of them show GUI, all they show is raw XML. Saving it as splplan and then opening it is a pain. As a DBA, execution plan is my bread and butter so need this fixed in coming CU. I hope it happens soon.

Posted by edwin.quick on 8/30/2011 at 2:52 PM

652855 says in most cases, I am finding this in every case since applying SP1 on 2008 R2. I don't believe this is the same bug.

Posted by Paul White NZ on 8/30/2011 at 12:55 AM

Related to/duplicate of 652855:

http://connect.microsoft.com/SQLServer/feedback/details/652855/valid-showplan-xml-fails-to-automatically-produce-graphical-showplan

Posted by edwin.quick on 11/29/2011 at 1:29 PM

The work arround posted by Aaron Bertrand here http://sqlblog.com/blogs/aaron_bertrand/archive/2011/10/28/getting-graphical-showplan-back-in-sql-server-2008-r2.aspx will work. If you have the installation media for CTP 3 look for the file kivdhjv4.xsd.

In regards to Connect #652855 which many have referenced as the same bug, that may not be the case. Since it was opened well before the release of SP1 and was intermittent, where as this issue was consistent after the application of SP1.