August 19, 2011 at 8:21 am
I am running a DMV query to get current activity on a server and, as part of that query, I get the query plan from the plan_handle of sys.dm_exec_requests.
This query has been running fine for me for quite a while now. As part of the output, I normally get the plan as a hyperlink, and when I click on it, I get the plan in graphical form. I can then right-click on the plan and pick "Show execution plan XML" to show the XML.
The problem is, that on this server I get the XML right away, and not the graphical plan, when I click on the hyperlink.
Has anyone else experienced this problem?
The server I am connecting to is on SQL 2008 R2 SP1; my SSMS client is on SQL 2008 SP2.
I wonder if the version difference is causing this...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 19, 2011 at 8:37 am
That's how SSMS 2005 behaves. Sure you're not running the wrong version by accident?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 19, 2011 at 8:41 am
GilaMonster (8/19/2011)
That's how SSMS 2005 behaves. Sure you're not running the wrong version by accident?
Nope, I'm running 10.0.2531.0 on my SSMS.
The server I am monitoring is on 10.50.2500.
Here is the query I am running:
--"Performance Tuning with SQL Server Dynamic Management Views", L. Davidson and T. Ford
SELECT
[des].session_id
,[des].[status]
,[des].login_name
,[des].[host_name]
,der.blocking_session_id
,DB_NAME(der.database_id) AS database_name
,der.command
,[des].cpu_time
,[des].reads
,[des].writes
,[dec].last_write
,[des].[program_name]
,der.wait_type
,der.wait_time
,der.last_wait_type
,der.wait_resource
,CASE [des].transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END AS transaction_isolation_level
,OBJECT_NAME( dest.objectid, der.database_id ) AS [object_name]
,dest.[text] AS [executing batch]
,SUBSTRING(
dest.[text], der.statement_start_offset / 2,
(CASE WHEN der.statement_end_offset = -1 THEN DATALENGTH (dest.[text])
ELSE der.statement_end_offset
END - der.statement_start_offset ) / 2
) AS [executing statement]
,deqp.query_plan
FROM
sys.dm_exec_sessions [des]
LEFT JOIN
sys.dm_exec_requests der
ON
[des].session_id = der.session_id
LEFT JOIN
sys.dm_exec_connections [dec]
ON
[des].session_id = [dec].session_id
OUTER APPLY
sys.dm_exec_sql_text(der.sql_handle) dest
OUTER APPLY
sys.dm_exec_query_plan(der.plan_handle) deqp
WHERE
[des].session_id <> @@SPID
AND [des].[status] <> 'sleeping'
ORDER BY
[des].session_id;
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 19, 2011 at 9:28 am
Goes straight to graphical plan here, on same version of SSMS
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 19, 2011 at 10:03 am
GilaMonster (8/19/2011)
Goes straight to graphical plan here, on same version of SSMS
Hmm, now that I am running it again on the same server, I get the graphical plan for one of the records in the output and straight XML for another record in the same result set.
There must be something in the XML that triggers SSMS to show the graphical plan in one case and not the other...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 20, 2011 at 1:35 pm
Just a guess, any chance that the database is in compatibility mode? I'm not sure that would affect anything, but that's odd behavior.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 20, 2011 at 3:33 pm
Grant Fritchey (8/20/2011)
Just a guess, any chance that the database is in compatibility mode? I'm not sure that would affect anything, but that's odd behavior.
Odd indeed...
Db is on 100 comp mode. Also I seem to be getting the graphical plan for some records and not others; I will try to find out whether there are any characteristics in the XML that are different in the 2 cases.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 20, 2011 at 4:00 pm
Any chance the plan xml is more than the limit on xml data in the grid results (default is 2MB i think)?
The other way I can get that behaviour is if the xml contains invalid data so it cannot be rendered graphically - you get no errors in this case, but it will display the xml instead of the graphical plan.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);August 21, 2011 at 4:52 am
mister.magoo (8/20/2011)
Any chance the plan xml is more than the limit on xml data in the grid results (default is 2MB i think)?The other way I can get that behaviour is if the xml contains invalid data so it cannot be rendered graphically - you get no errors in this case, but it will display the xml instead of the graphical plan.
Yeah, that's a good idea. It could be.
Another one occurs to me, what about zero cost plans? Do you have traceflag 2861 enabled?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 21, 2011 at 7:02 am
Grant Fritchey (8/21/2011)
mister.magoo (8/20/2011)
Any chance the plan xml is more than the limit on xml data in the grid results (default is 2MB i think)?The other way I can get that behaviour is if the xml contains invalid data so it cannot be rendered graphically - you get no errors in this case, but it will display the xml instead of the graphical plan.
Yeah, that's a good idea. It could be.
Another one occurs to me, what about zero cost plans? Do you have traceflag 2861 enabled?
Zero cost plan???
August 21, 2011 at 5:35 pm
Ninja's_RGR'us (8/21/2011)
Grant Fritchey (8/21/2011)
mister.magoo (8/20/2011)
Any chance the plan xml is more than the limit on xml data in the grid results (default is 2MB i think)?The other way I can get that behaviour is if the xml contains invalid data so it cannot be rendered graphically - you get no errors in this case, but it will display the xml instead of the graphical plan.
Yeah, that's a good idea. It could be.
Another one occurs to me, what about zero cost plans? Do you have traceflag 2861 enabled?
Zero cost plan???
Time for a blog post I imagine...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 21, 2011 at 7:08 pm
Or a short exemple like : SELECT NULL
It's the only thing I can imagine with 0 cost plan...
granted I didn't give this any energy before today.
August 22, 2011 at 3:56 am
Ninja's_RGR'us (8/21/2011)
Or a short exemple like : SELECT NULLIt's the only thing I can imagine with 0 cost plan...
granted I didn't give this any energy before today.
SET statements for parameters, a bunch of stuff.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 22, 2011 at 9:45 am
No, trace flag 2861 is not enabled.
Also, these are not 0-cost plans. For example, I'm getting the issue for XML that is 12,002 lines long from start to finish.
I'm also getting issue for the cached exec plan of a procedure (XML is only 1,789 lines long).
Thank you all for your responses.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 18, 2011 at 8:15 am
I found this link which deals with this exact issue;
(my server is on SQL 2008 SP1):
I am able to view the XML graphically through SQL Sentry plan explorer:
http://sqlsentry.net/plan-explorer/sql-server-query-view.asp
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply