SQL Server Performance Forum – Threads Archive

Can’t see execution plan for derived table-query

Hi, I have a derived table-query in SQL Server 2005 where the actual execution
plan doesn’t show up. The execution plan shows for other queries I’ve tried
but not on the derived table-queries.
I can see the estimated execution plan. Here’s the code that I use:
create table t1 (val1 int)
create table t2 (val2 int)
create table t3 (val3 int) declare @i int
set @i=0
while @i<99
begin
insert into t1 values (@i)
set @[email protected]+1
end set @i=0
while @i<99
begin
insert into t2 values (2 * @i)
set @[email protected]+1
end set @i=0
while @i<99
begin
insert into t3 values (3 * @i)
set @[email protected]+1
end
— the query where I can’t see the execution plan select top 20 t1.val1
from t1
inner join (
select t2.val2
from t2
where t2.val2<50
) as t
on t1.val1=t.val2
where t1.val1<50 and not exists (
select t3.val3
from t3
where t3.val3<50 and t1.val1=t3.val3
) Does anyone know why I can’t see the actual execution plan in SQL Server
2005? (I can see it in SQL Server 2000) Thanks

May refer to this articlehttp://www.sql-server-performance.com/jc_yukon_beta1_execution_plans.asp by Joe for reference. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

a very interesting article but what I can see it does’nt tell why I can’t see the actual execution plan when I run a derived table-query.
If I run: 1. query – select val1 from t1 where val1<50
2. query – derived table as I wrote above.
3. query – select val2 from t2 where val2<50 I only get two tabs with execution plan showing query number 1 and 3.

Answer on my question from a newsgroup: "That’s an engine bug, as far as I can tell. When I trace the batch in
Profiler, with all performance events included, there is a SHOWPLAN XML
event, and when I click this event, I see this in the botton area of
Profiler: There is a problem with the XML control.
There is an error in XML document (1, 846). So SQL Server generates incorrect XML, and leaves Mgmt Studio without a
chance. If you run the query from Query Analyzer, you get the correct query plan,
since Query Analyzer uses SHOWPLAN TEXT and not the XML thing. Interesting
enough, Mgmt Studio also shows the plan if you first say SET STATISTICS
PROFILE ON. Unless you beat me to it, I will file a bug.
—
Erland Sommarskog, SQL Server MVP, [email protected]"

Thanks for your information and resolution, and I feel SQL 2K5 is still in BETA stage we can expect more changes in this area until the original version (RTM) is released. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>