Execution plan SORT of 0 rows takes 70%

Is that the estimated or actual execution plan? And, I'd need more stats
and a complete plan to be able to answer this better. But as a rule,
sorting on a non-indexed field is expensive. If that's an estimates plan
SQL servers is probably taking that into account just incase rows show up.
So if that is the actual execution plan, then I'd say that the execution
plan didn't correctly optimize the sort...
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:uOj15rGmFHA.1232@.TK2MSFTNGP15.phx.gbl...
> SQL Server 2000 SP3 8.00.760 Developer Edition
> I have a query whose execution plan is showing a "Sort/TopN Sort" taking
> 70% of the cost of the query. The odd thing is that the row count going
> in to the sort is 0. The row count on the operation is 0 and the number
> of executes is 2.
> Any ideas what is going on here? Attached is the portion of the graphical
> execution plan.
> Thanks,
> Mike
>Is this a query or a stored procedure? Like could this be a cached plan?
Also how long does the query execute? Perhaps it is just guessing 0 rows
and it will take 70% if there are a few. 70% could be really small if the
overall time is miniscule :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Ben" <ben@.online.nospam> wrote in message
news:688f5$42f12f11$d8445835$12571@.FUSE.NET...
> Is that the estimated or actual execution plan? And, I'd need more stats
> and a complete plan to be able to answer this better. But as a rule,
> sorting on a non-indexed field is expensive. If that's an estimates plan
> SQL servers is probably taking that into account just incase rows show up.
> So if that is the actual execution plan, then I'd say that the execution
> plan didn't correctly optimize the sort...
>
> "Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
> news:uOj15rGmFHA.1232@.TK2MSFTNGP15.phx.gbl...
>|||I just ran the query again in another query analyzer window and the
execution plan was fine. Same exact plan, but now the sort shows only 4%.
Not quite sure what was going on there. Perhaps it was some other
monitoring I had running, but that shouldn't effect the execution plan
costs, right?
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:%23wQTYyOmFHA.3552@.TK2MSFTNGP10.phx.gbl...
> 1. The plan is an actual execution plan
> 2. It is a stored procedure
> 3. It is actually 0 rows.
> I couldn't attach the whole graphical plan, it was too big for the
> newsgroup to accept (I've included the text version below but its probably
> really hard to read w/ word wraps). I attached the detail on the sort and
> the detail of a clustered index s in the same query. There really are
> 0 rows being sorted, that's why I don't understand the 70%. The rest of
> the query is about as optimal as it gets about from the minor bookmark
> lookups. I attached the clustered index s to show that something seems
> anomolous: the I/O cost of sorting 0 rows two times (0.0112) is higher
> than the cost of sing on a clustered index 349 times (0.00320).
> Either I'm misreading something in the execution plan or something in the
> graphical display isn't right.
> Thanks for any help,
> Mike
>
> Execution Tree (db, table, field names changed)
> --
> Compute Scalar(DEFINE:([Expr1011]=isnull([tbAAAA
A].[fld11111], '')))
> |--Nested Loops(Left Outer Join, OUTER REFERENCES:([tbBBBBB].[fld22222]))
> |--Nested Loops(Inner Join, OUTER REFERENCES:([tb2].[fld33333) WITH
> PREFETCH)
> | |--Nested Loops(Inner Join, OUTER
> REFERENCES:([tb1].[fld44444]))
> | | |--Nested Loops(Inner Join, OUTER
> REFERENCES:([tb2].[fld33333) WITH PREFETCH)
> | | | |--Index
> S(OBJECT:([db00000].[dbo].[tbCCCCC].[IX_tbCCCCC_fld55555_fld33333 AS
> [tb2]), SEEK:([tb2].[fld55555]='123' OR [tb2].[fld55555]='456' OR
> [tb2].[fld55555]='789') ORDERED FORWARD)
> | | | |--Clustered Index
> S(OBJECT:([db00000].[dbo].[tbEEEEE].[PK_tbEEEEE] AS [tb1]),
> SEEK:([tb1].[fld33333=[tb2].[fld33333), WHERE:([tb1].[fldFFFFF]='R')
> ORDERED FORWARD)
> | | |--Index
> S(OBJECT:([db00000].[dbo].[tbGGGGG].[IX_tbGGGGG_fldAAAAA_fldEEEEE] AS
> [tb3]), SEEK:([tb3].[fldAAAAA]=[tb1].[fld44444] AND
> [tb3].[fldEEEEE]=[@.fldEEEEE]) ORDERED FORWARD)
> | |--Clustered Index
> S(OBJECT:([db00000].[dbo].[tbBBBBB].[IX_tbBBBBB_fld66666),
> SEEK:([tbBBBBB].[fld66666=[tb2].[fld33333) ORDERED FORWARD)
> |--Bookmark Lookup(BOOKMARK:([Bmk1005]),
> OBJECT:([db00000].[dbo].[tbAAAAA]))
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([tbAAAAA].[fld99999]))
> |--Sort(TOP 1, ORDER BY:([tbAAAAA].[fldBBBBB] DESC))
> | |--Bookmark Lookup(BOOKMARK:([Bmk1007]),
> OBJECT:([db00000].[dbo].[tbAAAAA]))
> | |--Index
> S(OBJECT:([db00000].[dbo].[tbAAAAA].[IX_tbAAAAA_XXXXX]),
> SEEK:([tbAAAAA].[fld77777]=[tbBBBBB].[fld22222] AND
> [tbAAAAA].[fldCCCCC]=[@.fldCCCCC] AND [tbAAAAA].[fldDDDDD]=[@.fldDDDDD])
> ORDERED FORWARD)
> |--Index
> S(OBJECT:([db00000].[dbo].[tbAAAAA].[IX_tbAAAAA_fld99999]),
> SEEK:([tbAAAAA].[fld99999]=[tbAAAAA].[fld99999]) ORDERED FORWARD)
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:%23TYEwJHmFHA.2484@.TK2MSFTNGP15.phx.gbl...
>
>|||This is one of those shoulder shrugging moments. The optimizer though
something or other, or it just took longer because something else was going
on. I wouldn't worry about it too much :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:OvzJqHPmFHA.3552@.TK2MSFTNGP10.phx.gbl...
>I just ran the query again in another query analyzer window and the
>execution plan was fine. Same exact plan, but now the sort shows only 4%.
>Not quite sure what was going on there. Perhaps it was some other
>monitoring I had running, but that shouldn't effect the execution plan
>costs, right?
>
> "Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
> news:%23wQTYyOmFHA.3552@.TK2MSFTNGP10.phx.gbl...
>