SQL Server Performance

Can't see execution plan for derived table-query

Discussion in 'SQL Server 2005 General DBA Questions' started by Makida, Oct 29, 2004.

  1. Makida New Member

    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 @i=@i+1
    end

    set @i=0
    while @i<99
    begin
    insert into t2 values (2 * @i)
    set @i=@i+1
    end

    set @i=0
    while @i<99
    begin
    insert into t3 values (3 * @i)
    set @i=@i+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
  2. satya Moderator

  3. Makida New Member

    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.
  4. Makida New Member

    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, esquel@sommarskog.se"
  5. satya Moderator

    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.

Share This Page