Tuesday, March 27, 2012

Estimated cost shows 300% in query plan

Hello all,
On a SQL Server 2000 sp4, on a 4 Xeon cpus (with hyperthreading) machine
(Windows Server 2003), most of the query plans I see are showing, on the
estimated cost of each step of the plan, more than 100% (e.g. 300% for a
seek).
Does anyone have an explanation or has already seen that ?
tia,
Rudi Bruchez
MCDBA
It's a bug in the graphical query plans when dealing with parallel query
plans. I have seen percentage reaching 4 figures even. I don't think it will
be fixed, considering it wasn't fixed in SP4.
Jacco Schalkwijk
SQL Server MVP
"Rudi Bruchez" <rudi#no-spam#at.babaluga.com> wrote in message
news:138syoixibdyn$.14zc2fkwrr1j5.dlg@.40tude.net.. .
> Hello all,
> On a SQL Server 2000 sp4, on a 4 Xeon cpus (with hyperthreading) machine
> (Windows Server 2003), most of the query plans I see are showing, on the
> estimated cost of each step of the plan, more than 100% (e.g. 300% for a
> seek).
> Does anyone have an explanation or has already seen that ?
> tia,
> Rudi Bruchez
> MCDBA
|||On Thu, 27 Oct 2005 16:13:39 +0100, Jacco Schalkwijk wrote:

> It's a bug in the graphical query plans when dealing with parallel query
> plans. I have seen percentage reaching 4 figures even. I don't think it will
> be fixed, considering it wasn't fixed in SP4.
Hi,
Thanks for the feedback. My problem is that I've set the maximum degree of
parallelism to 1 at the server level, and there's no mention of parallelism
on the graphical plan. Should this still appear ?
Btw, even with a maxdop to 1, I still see some "degree of parallelism"
event in profiler, with a BinaryData (CPUs involved) at 0X00000000.
Is it explanable ?
thanks again,
Rudi Bruchez
|||On Thu, 27 Oct 2005 17:05:35 +0200, Rudi Bruchez
<rudi#no-spam#at.babaluga.com> wrote:
>On a SQL Server 2000 sp4, on a 4 Xeon cpus (with hyperthreading) machine
>(Windows Server 2003), most of the query plans I see are showing, on the
>estimated cost of each step of the plan, more than 100% (e.g. 300% for a
>seek).
>Does anyone have an explanation or has already seen that ?
It happens.

How about the after-query plan?
J.
|||Rudi Bruchez wrote:
> On Thu, 27 Oct 2005 16:13:39 +0100, Jacco Schalkwijk wrote:
>
> Hi,
> Thanks for the feedback. My problem is that I've set the maximum
> degree of parallelism to 1 at the server level, and there's no
> mention of parallelism on the graphical plan. Should this still
> appear ?
> Btw, even with a maxdop to 1, I still see some "degree of parallelism"
> event in profiler, with a BinaryData (CPUs involved) at 0X00000000.
> Is it explanable ?
> thanks again,
> Rudi Bruchez
What happens with the actual plan, not the estimated one? Does it
display correctly? What happens if you add a MAXDOP (1) to the query?
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||On Thu, 27 Oct 2005 18:33:59 -0400, David Gugick wrote:

> Rudi Bruchez wrote:
> What happens with the actual plan, not the estimated one? Does it
> display correctly? What happens if you add a MAXDOP (1) to the query?
Hello,
I was talking about the actual plan, no difference there with the
estimated, on both I see 300% on several seeks. Same if I put the MAXDOP
(1) option on the query.
I'm interested in this also because it is a server hosted by an ISP, and
there are sometimes performances problems I've difficulties to explain form
the SQL server perspective only. I'm tracing peculiarities which could be
signs for problems.

No comments:

Post a Comment