Estimeted Time for SP

Hi,
How can i know estimated time taken by a SP before actual execution ?
thankscheck from the Execution Plan.
best Regards,
Chandra
---
"DMP" wrote:

> Hi,
> How can i know estimated time taken by a SP before actual execution ?
> thanks
>
>|||Sorry , I think you don't understand my requirement.
Basically,I am calling a SP from VB and showing a progress bar till SP is
running.
How can i control progessBar.Value till SP running ?
Any Idea to increase the ProgressBar symetrically ......
thanks,
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:C44599AA-74D3-40B8-BC59-3F0C44B140C8@.microsoft.com...
> check from the Execution Plan.
>
> --
> best Regards,
> Chandra
> ---
>
> "DMP" wrote:
>|||Sorry,Ithink you don't understand my requirement.
Basically, I am calling a SP from VB and showing a progressbar till SP is
running.
How can i control to increase the progressbar value symetrically ?
Any idea ?
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:C44599AA-74D3-40B8-BC59-3F0C44B140C8@.microsoft.com...
> check from the Execution Plan.
>
> --
> best Regards,
> Chandra
> ---
>
> "DMP" wrote:
>|||DMP wrote:
> Hi,
> How can i know estimated time taken by a SP before actual execution ?
> thanks
There is no way to know how long a stored procedure (or any query) is
going to take to execute, short of performing some tests. In order to
display a progress bar, you'll need to use async execution. If it's a
short query, you're better off not worrying about the progress bar
(whether you use an async call or not). If your using .Net, then an
async call is not possible. You'll have to run it on another thread if
you don't want to tie up the main application thread during execution.
If you're fetching rows, you can provide some feedback on screen while
the fetching is taking place since by that time control has returned to
the application.
David Gugick
Imceda Software
www.imceda.com|||Hi
I don't think this is possible. What you can do is gather information about
how long it will normally take and then use that time as a guideline. If thi
s
is taking a long time you may think of changing the method of implementation
.
You may be able to split the process into smaller units or use a different
delivery mechanism that does not require the user to watch a progress bar
tick away!
John
"DMP" wrote:

> Sorry,Ithink you don't understand my requirement.
> Basically, I am calling a SP from VB and showing a progressbar till SP is
> running.
> How can i control to increase the progressbar value symetrically ?
> Any idea ?
>
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:C44599AA-74D3-40B8-BC59-3F0C44B140C8@.microsoft.com...
>
>|||Basically I call a SP from VB and showing a ProgressBar till SP execution.
How can I increase the progressbar symetrically ?
That is why I needed the sp execution time.
Or any good sugg.
Thanks,
"DMP" <debdulal.mahapatra@.fi-tek.co.in> wrote in message
news:O0BsDvfUFHA.3624@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can i know estimated time taken by a SP before actual execution ?
> thanks
>|||Although it doesn't give you percentage completion in time, you could track
where a proc is in its execution by having it write to a table at certain
points in the code. If you run it async as other people have pointed out,
then another thread could query your "progress" table and determine how ar
along the first proc is.
Pretty kludgy but might be what you want.
"DMP" wrote:

> Basically I call a SP from VB and showing a ProgressBar till SP execution.
> How can I increase the progressbar symetrically ?
> That is why I needed the sp execution time.
> Or any good sugg.
> Thanks,
> "DMP" <debdulal.mahapatra@.fi-tek.co.in> wrote in message
> news:O0BsDvfUFHA.3624@.TK2MSFTNGP10.phx.gbl...
>
>

Estimation of Backup Time

Any idea, how to calculate estimated time for a DB Backup?
Kind regards
Khurram ShahzadHi
It is very dependent on your hardware and Server Load.
You are going to have to test it yourself.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Khurram Shahzad" wrote:

> Any idea, how to calculate estimated time for a DB Backup?
> Kind regards
> Khurram Shahzad
>
>
>sql

Estimation of Backup Time

Any idea, how to calculate estimated time for a DB Backup?
Kind regards
Khurram Shahzad
Hi
It is very dependent on your Hardware and Server Load.
You are going to have to test it yourself.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Khurram Shahzad" wrote:

> Any idea, how to calculate estimated time for a DB Backup?
> Kind regards
> Khurram Shahzad
>
>
>

Estimation of Backup Time

Any idea, how to calculate estimated time for a DB Backup?
Kind regards
Khurram ShahzadHi
It is very dependent on your Hardware and Server Load.
You are going to have to test it yourself.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Khurram Shahzad" wrote:
> Any idea, how to calculate estimated time for a DB Backup?
> Kind regards
> Khurram Shahzad
>
>
>

Estimating Database Size Issue

All,
I am following the guidelines laid out on the following page to
calculate an estimated size for a database.
http://msdn2.microsoft.com/en-us/library/ms178085.aspx.
However, I am noticing something weird. Because
Index_Rows_Per_Page is calculated by dividing the (Index_Row_Size +
2), the MORE variable and non-variable keys you add to your table, the
SMALLER the size of the database gets. Does this make sense to anyone
because it doesn't make sense to me.
Here's my database:
Num_Rows = 75,000,000
Num_Cols = 3
Fixed_Data_Size = 8
Num_Variable_Cols = 0
Max_Var_Size = 0
Num_Key_Cols = 2
Fixed_Key_Size = 4
Num_Variable_Key_Cols = 0
Max_Var_Key_Size = 0
Num_Nullable_Key_Cols = 0
Using this information, I get 438.07GB as the size of my
database.
If I change the number of Fixed_Key_Size to 6, the size of the
database drops to 335.25GB.
Again, this doesn't make sense to me. If quadruple checked my
math against what's in the URL above, and it appears to match what is
in the document. Is Microsoft's calculation wrong? Surely not. Maybe I
missed something? Are you guys getting the same results based off of
my info above?
Thanks,
JeremyI remember when I first read this article I felt pretty overwhelmed.
I've found the many scripts and articles from www.sqlservercentral.com
(and www.sql-server-perfromance.com I think) to be much more
intuitive. I know this doesn't really answer your question, its more
of a suggestion.
Erik|||Thanks Erik. I was looking further through the code I wrote, value by
value, and it turns out that it's either the LOG function or the
Summation function (or a combination of both) that is making the size
decrease when the byte size of indexed keys increases. But it still
doesn't make sense.
I'll take a look at those links though, thanks!
Jeremysql

Estimating Database Size Issue

All,
I am following the guidelines laid out on the following page to
calculate an estimated size for a database.
http://msdn2.microsoft.com/en-us/library/ms178085.aspx.
However, I am noticing something weird. Because
Index_Rows_Per_Page is calculated by dividing the (Index_Row_Size +
2), the MORE variable and non-variable keys you add to your table, the
SMALLER the size of the database gets. Does this make sense to anyone
because it doesn't make sense to me.
Here's my database:
Num_Rows = 75,000,000
Num_Cols = 3
Fixed_Data_Size = 8
Num_Variable_Cols = 0
Max_Var_Size = 0
Num_Key_Cols = 2
Fixed_Key_Size = 4
Num_Variable_Key_Cols = 0
Max_Var_Key_Size = 0
Num_Nullable_Key_Cols = 0
Using this information, I get 438.07GB as the size of my
database.
If I change the number of Fixed_Key_Size to 6, the size of the
database drops to 335.25GB.
Again, this doesn't make sense to me. If quadruple checked my
math against what's in the URL above, and it appears to match what is
in the document. Is Microsoft's calculation wrong? Surely not. Maybe I
missed something? Are you guys getting the same results based off of
my info above?
Thanks,
Jeremy
I remember when I first read this article I felt pretty overwhelmed.
I've found the many scripts and articles from www.sqlservercentral.com
(and www.sql-server-perfromance.com I think) to be much more
intuitive. I know this doesn't really answer your question, its more
of a suggestion.
Erik
|||Thanks Erik. I was looking further through the code I wrote, value by
value, and it turns out that it's either the LOG function or the
Summation function (or a combination of both) that is making the size
decrease when the byte size of indexed keys increases. But it still
doesn't make sense.
I'll take a look at those links though, thanks!
Jeremy

Estimated row count vs. actual row count

Hello!
I was trying to troubleshoot a query. Very last step show estimated
rowcount=142386. When query is executed, it returns only 7234 rows. I
executed UPDATE STATISTICS ... WITH FULLSCAN against all tables involved in
this query but this didn't make a difference. I am not sure what else I can
do at this point.
Any advice is a greatly appreciated,
IgorIf you're referring to "estimated rowcount" in the show execution plan
screen of Query Analyzer, that just refers to how many rows were processed
by that specific step in the processing... The number of rows the query
eventually produces can be and most often is very different..
This is Nothing to be concerned about.
"Igor Marchenko" wrote:

> Hello!
> I was trying to troubleshoot a query. Very last step show estimated
> rowcount=142386. When query is executed, it returns only 7234 rows. I
> executed UPDATE STATISTICS ... WITH FULLSCAN against all tables involved i
n
> this query but this didn't make a difference. I am not sure what else I ca
n
> do at this point.
> Any advice is a greatly appreciated,
> Igor
>
>|||Yes, I am referring to execution plan in Query Analyzer. I got concerned
after reading following article:
http://www.windowsitpro.com/sqlserv...61.html

For other queries there is no discrepancy between estimated and actual
number of rows.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:0DF221BF-921A-4D4A-9963-6FD335621588@.microsoft.com...
> If you're referring to "estimated rowcount" in the show execution plan
> screen of Query Analyzer, that just refers to how many rows were
> processed
> by that specific step in the processing... The number of rows the query
> eventually produces can be and most often is very different..
> This is Nothing to be concerned about.
>
> "Igor Marchenko" wrote:
>|||OK, read the article... The author's first comment, "Yes go ahead and
worry..." is a bit overkill - in that he is talking to a point extraneous to
the question he was asked...
He was asked about whether there's an issue simply because the numbers are
different. But he answered based on the fact the "estimated" Number is HIGH
.
Any time you see an "estimated rowCount" number much higher than the actual
number that were generated, then the Query Optimizer might not be choosing
the "fastest" most efficient option in processing the query... the accuracy
or completeness of the data results will not be affected, only the speed at
which it is generated.
"Igor Marchenko" wrote:

> Yes, I am referring to execution plan in Query Analyzer. I got concerned
> after reading following article:
> http://www.windowsitpro.com/sqlserv...61.ht
ml
> For other queries there is no discrepancy between estimated and actual
> number of rows.
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:0DF221BF-921A-4D4A-9963-6FD335621588@.microsoft.com...
>
>|||That's exactly why I am worried : 'Query Optimizer might not be choosing
the "fastest" most efficient option in processing the query'. I do know
accuracy won't be affected.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:2BE6C2AB-8D39-4D79-ACC6-D0DBB427F651@.microsoft.com...
> OK, read the article... The author's first comment, "Yes go ahead and
> worry..." is a bit overkill - in that he is talking to a point extraneous
> to
> the question he was asked...
> He was asked about whether there's an issue simply because the numbers are
> different. But he answered based on the fact the "estimated" Number is
> HIGH.
> Any time you see an "estimated rowCount" number much higher than the
> actual
> number that were generated, then the Query Optimizer might not be choosing
> the "fastest" most efficient option in processing the query... the
> accuracy
> or completeness of the data results will not be affected, only the speed
> at
> which it is generated.
> "Igor Marchenko" wrote:
>|||Igor, I found this link:
http://msdn.microsoft.com/library/d...
etchapt14.asp
On it I extracted this bit if text...
Compare Actual vs. Estimated Rows and Executions
When you read the output from a SHOWPLAN statement, start from the
most-indented row that has the highest incremental change in the
TotalSubtreeCost column. Carefully evaluate both the index selection and the
optimizer's estimate by using the SET STATISTICS PROFILE ON command. This
command runs the statement, so only use it on SELECT statements or T-SQL cod
e
that does not modify data, or you can preface the command with a BEGIN TRAN
/ROLLBACK statement.
As an alternative, use the new profiler Performance:Showplan Statistics
event in SQL 2000. This event belongs to event class 98. This event reports
four columns that show estimated and actual rows and executions. You must
select the Binary Data column before the profiler event adds data to the
T-SQL or SP:stmtcompleted events.
Substantial differences in the estimated row count may indicate the
optimizer had out-of-date statistics or skewed statistics. For example, if
the estimated row count is 2 rows, and the actual row count is 50,000, the
optimizer may have had out-of-date statistics or skewed statistics. Try usin
g
the UPDATE STATISTICS WITH FULLSCAN command.
Hth,
Charly
"Igor Marchenko" wrote:

> That's exactly why I am worried : 'Query Optimizer might not be choosing
> the "fastest" most efficient option in processing the query'. I do know
> accuracy won't be affected.
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:2BE6C2AB-8D39-4D79-ACC6-D0DBB427F651@.microsoft.com...
>
>|||Charly,
I did execute UPDATE STATISTICS WITH FULLSCAN but it didn't correct
discrepancies between estimated and actual number of rows.
Regards,
Igor
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:A427092E-9686-4F08-B9F3-115B8C313DA7@.microsoft.com...
> Igor, I found this link:
> http://msdn.microsoft.com/library/d...enetchapt14.asp
> On it I extracted this bit if text...
> Compare Actual vs. Estimated Rows and Executions
> When you read the output from a SHOWPLAN statement, start from the
> most-indented row that has the highest incremental change in the
> TotalSubtreeCost column. Carefully evaluate both the index selection and
> the
> optimizer's estimate by using the SET STATISTICS PROFILE ON command. This
> command runs the statement, so only use it on SELECT statements or T-SQL
> code
> that does not modify data, or you can preface the command with a BEGIN
> TRAN
> /ROLLBACK statement.
> As an alternative, use the new profiler Performance:Showplan Statistics
> event in SQL 2000. This event belongs to event class 98. This event
> reports
> four columns that show estimated and actual rows and executions. You must
> select the Binary Data column before the profiler event adds data to the
> T-SQL or SP:stmtcompleted events.
> Substantial differences in the estimated row count may indicate the
> optimizer had out-of-date statistics or skewed statistics. For example, if
> the estimated row count is 2 rows, and the actual row count is 50,000, the
> optimizer may have had out-of-date statistics or skewed statistics. Try
> using
> the UPDATE STATISTICS WITH FULLSCAN command.
> Hth,
> Charly
> "Igor Marchenko" wrote:
>|||When the optimizer's rowcount estimates are incorrect, often
statistics cannot remedy the problem. For many query plan
operators, the actual rowcount depends on the distribution
of values in two separate columns (perhaps in two separate
tables), and as far as I know, SQL Server keeps no statistics
on how the values in multiple columns are correlated. In
addition, there is no feedback mechanism to do anything with
the information you're seeing - that the actual and estimated
counts are very different.
I have often seen suboptimal query plans be chosen as a
result of this situation, but more often than not, the best
query plan is still being chosen, even though its cost is not
estimated correctly. When there is a better plan the optimizer
is missing, it's my experience that the culprit is less often the
bad rowcounts than that the indexes available are not ideal.
For instance, many queries benefit from nonclustered indexes
on more than a single column, but many designers never define
indexes on more than one column at a time.
Without seeing the query and actual query plan, and assuming
that some basic tuning has already been done to make sure
no more filters can be made SARGable than already are, I can
suggest a couple of things to look for - beyond that, if you post the
query and plan, along with the create table and create index
statements, we can take a further look.
First, if the bad rowcounts appear in parallel operators, you
might try adding the hint OPTION (MAXDOP 1) and see if
performance improves. Bad rowcounts can cause parallelism
to be used in countereffective ways. Second, if you can
identify the particular WHERE conditions that the optimizer
is costing poorly (the optimizer expects many more rows to
satisfy the conditions that actually do), try adding one or more
non-SARGable dummy conditions at the same point in the
query and see if the estimates are more accurate. For example,
if the condition A.col1 = B.col1 is estimated poorly, where
these two columns are integers, try
(A.col1 = B.col1 AND -A.col1 = -B.col1), or try (A.col1 =
B.col1 AND A.col1%2 < 2 AND B.col1%2 < 2). If date
columns are involved, add something like ...
AND MONTH(A.datecolumn) > 0. Be sure to use parentheses
so you don't change the meaning of your query due to the way
AND and OR are associated. The query optimizer handles
non-SARGable predicates generically and this may help out.
A simpler situation where the rowcounts can be
wrong is for non-SARGable conditions
like MONTH(datecolumn) = 1 or like
DATEADD(d,30,datecolumn) > GETDATE(). Here
the optimizer will generally use generic estimates (perhaps
something like "gee, I think about a third of the rows will
satisfy a > condition, no matter what the condition is").
Sometimes the condition can be rewritten so the optimizer
can use statistics instead of a generic estimate.
The second of these examples can better be written as
datecolumn > DATEADD(d,-30,GETDATE()), which
is SARGable. The first can't be SARGed on its own, but
if it is there for something like MONTH(datecolumn) = 1
AND YEAR(datecolumn) = 2005, it can be rewritten as
(datecolumn >= '20050101' and datecolumn < '20050201').
Feel free to post more details.
Steve Kass
Drew University
Igor Marchenko wrote:

>Charly,
>I did execute UPDATE STATISTICS WITH FULLSCAN but it didn't correct
>discrepancies between estimated and actual number of rows.
>Regards,
>Igor
>
>"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
>news:A427092E-9686-4F08-B9F3-115B8C313DA7@.microsoft.com...
>
>
>|||"Steve Kass" <skass@.drew.edu> wrote in message
news:ev8gWnZNFHA.3380@.TK2MSFTNGP15.phx.gbl...

> (A.col1 = B.col1 AND -A.col1 = -B.col1), or try (A.col1 =
> B.col1 AND A.col1%2 < 2 AND B.col1%2 < 2). If date
> columns are involved, add something like ...
> AND MONTH(A.datecolumn) > 0. Be sure to use parentheses
> so you don't change the meaning of your query due to the way
> AND and OR are associated.
Since AND has a higher precedence than OR, I think this caveat would only
apply if NOTs were involved. It never hurts to include extra parentheses, of
course, but in this case, I don't see that they're necessary.
Excellent post, as usual.|||Thank you Steve for your more than elaborate answer! I was able to
speed the query up by implementing clustered index on different set of
columns. Query plan has changed completely. Instead of using hash and merge
joins, SQL server is using inner loop joins.Overall query cost went down
from 137 to 56.1. I am currently verifying all queries accessing table in
question to make sure there is no side affect as a result of different
clustered index. I was wondering if you could provide a reference on where
to read more about query optimization techniques (statistics, better index
selection, etc.)
Thanks,
Igor

Estimated Query Plan

Hi,

I am writing a client application that shows estimated queries plans and statistics. I know how to obtain estimated plans by using SQL Server Management Studio. But is it possible to obtain by using database functions?

I have found sys.dm_exec_query_plan, but it seems that this function can only be used for executed (or executing) queries...

Thanks

SET SHOWPLAN_XML ON

go

exec YourSP

go

SET SHOWPLAN_XML OFF

go

|||

I do not want to obtain the execution plan for a stored procedure. I do want to use a stored procedure (that may be invoked by a client application) to obtain the execution plan for a user-submmitted query. (It is just like SQL Manager Studio does).

Thanks

Estimated Execution Time

Hi,
Is it possible to know the Estimated Execution Time of a stored procedure
before actual execution from VB/SQL Server Application ?DMP wrote:
> Hi,
> Is it possible to know the Estimated Execution Time of a stored
> procedure before actual execution from VB/SQL Server Application ?
Sure. From Query Analyzer, enter the execution text for the procedure
with the parameters you want and use the Query | Display Estimated
Execution Plan (CTRL + L). You can also use SET SHOWPLAN_ALL ON / OFF in
batches before the procedure to see a textual execution plan.
David Gugick
Imceda Software
www.imceda.com|||Yes of course, just look at this example:
SET SHOWPLAN_ALL ON
GO
Select sc.name,st.name,st.length from syscolumns sc
Inner join sysobjects so on
sc.id = so.id
Inner join systypes st on
sc.xtype = st.xtype
Where so.Xtype = 'U' And
so.name like 'Orders'
GO
SET SHOWPLAN_ALL OFF
GO
SET SHOWPLAN_TEXT ON
GO
Select sc.name,st.name,st.length from syscolumns sc
Inner join sysobjects so on
sc.id = so.id
Inner join systypes st on
sc.xtype = st.xtype
Where so.Xtype = 'U' And
so.name like 'Orders'
GO
SET SHOWPLAN_TEXT OFF
HTH, Jens Smeyer.
"DMP" <debdulal.mahapatra@.fi-tek.co.in> schrieb im Newsbeitrag
news:O2U$n5%23PFHA.244@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is it possible to know the Estimated Execution Time of a stored procedure
> before actual execution from VB/SQL Server Application ?
>

Estimated Execution Plan-Diff betw Seek Predicate and Predicate?

Hi experts,
I am looking at an estimated execution plan for a query that joins between 2
significantly big tables. One of the Clustered Index Seek uses has both
Predicate and Seek Predicate. What are the differences between Seek Predicate
and Predicate?
In detail, this is what I have done:
...
INNER JOIN BonusPromotion BP ON
...
AND BP.status = 'A'--Active
AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
...
The index used is the primary key along fields.
endDate,
validStartDate,
storeID,
emEAN,
bpID
Seek predicate is along field endDate, while Predicate is along field
validStartDate. My impression of Seek predicate is very fast search
performance. So why can't the Seek predicate include BOTH endDate and
validStartDate? Therefore back to my 1st question: What are the differences
between Seek Predicate and Predicate?
Hi
What are the differences between Seek Predicate
> and Predicate?
http://blogs.msdn.com/craigfr/archive/2006/07/07/652668.aspx
..
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:4AC93D3A-69C0-4BD2-9CBD-2EEB86714040@.microsoft.com...
> Hi experts,
> I am looking at an estimated execution plan for a query that joins between
> 2
> significantly big tables. One of the Clustered Index Seek uses has both
> Predicate and Seek Predicate. What are the differences between Seek
> Predicate
> and Predicate?
> In detail, this is what I have done:
> ...
> INNER JOIN BonusPromotion BP ON
> ...
> AND BP.status = 'A' --Active
> AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
> ...
> The index used is the primary key along fields.
> endDate,
> validStartDate,
> storeID,
> emEAN,
> bpID
> Seek predicate is along field endDate, while Predicate is along field
> validStartDate. My impression of Seek predicate is very fast search
> performance. So why can't the Seek predicate include BOTH endDate and
> validStartDate? Therefore back to my 1st question: What are the
> differences
> between Seek Predicate and Predicate?
|||HardKhor,
BP.endDate will be the only seek predicate, because you are not
searching for an exact match for that column, but for a range of values.
This means the other predicates will be evaluated by scanning the index
pages in this range of endDate.
Theoretically, it could still seek the next index column (in your case
validStartDate), but SQL Server does not support this. And even if it
did, the multiple (looped) seeks would only be useful if the preceding
column (in your case endDate) has a very low selectivity, and the index
is very shallow. Otherwise the cost of the multiple seeks would exceed
the cost of scanning the index pages.
HTH,
Gert-Jan
HardKhor wrote:
> Hi experts,
> I am looking at an estimated execution plan for a query that joins between 2
> significantly big tables. One of the Clustered Index Seek uses has both
> Predicate and Seek Predicate. What are the differences between Seek Predicate
> and Predicate?
> In detail, this is what I have done:
> ...
> INNER JOIN BonusPromotion BP ON
> ...
> AND BP.status = 'A' --Active
> AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
> ...
> The index used is the primary key along fields.
> endDate,
> validStartDate,
> storeID,
> emEAN,
> bpID
> Seek predicate is along field endDate, while Predicate is along field
> validStartDate. My impression of Seek predicate is very fast search
> performance. So why can't the Seek predicate include BOTH endDate and
> validStartDate? Therefore back to my 1st question: What are the differences
> between Seek Predicate and Predicate?
sql

Estimated Execution Plan-Diff betw Seek Predicate and Predicate?

Hi experts,
I am looking at an estimated execution plan for a query that joins between 2
significantly big tables. One of the Clustered Index Seek uses has both
Predicate and Seek Predicate. What are the differences between Seek Predicate
and Predicate?
In detail, this is what I have done:
...
INNER JOIN BonusPromotion BP ON
...
AND BP.status = 'A' --Active
AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
...
The index used is the primary key along fields.
endDate,
validStartDate,
storeID,
emEAN,
bpID
Seek predicate is along field endDate, while Predicate is along field
validStartDate. My impression of Seek predicate is very fast search
performance. So why can't the Seek predicate include BOTH endDate and
validStartDate? Therefore back to my 1st question: What are the differences
between Seek Predicate and Predicate?Hi
What are the differences between Seek Predicate
> and Predicate?
http://blogs.msdn.com/craigfr/archive/2006/07/07/652668.aspx
.
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:4AC93D3A-69C0-4BD2-9CBD-2EEB86714040@.microsoft.com...
> Hi experts,
> I am looking at an estimated execution plan for a query that joins between
> 2
> significantly big tables. One of the Clustered Index Seek uses has both
> Predicate and Seek Predicate. What are the differences between Seek
> Predicate
> and Predicate?
> In detail, this is what I have done:
> ...
> INNER JOIN BonusPromotion BP ON
> ...
> AND BP.status = 'A' --Active
> AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
> ...
> The index used is the primary key along fields.
> endDate,
> validStartDate,
> storeID,
> emEAN,
> bpID
> Seek predicate is along field endDate, while Predicate is along field
> validStartDate. My impression of Seek predicate is very fast search
> performance. So why can't the Seek predicate include BOTH endDate and
> validStartDate? Therefore back to my 1st question: What are the
> differences
> between Seek Predicate and Predicate?|||HardKhor,
BP.endDate will be the only seek predicate, because you are not
searching for an exact match for that column, but for a range of values.
This means the other predicates will be evaluated by scanning the index
pages in this range of endDate.
Theoretically, it could still seek the next index column (in your case
validStartDate), but SQL Server does not support this. And even if it
did, the multiple (looped) seeks would only be useful if the preceding
column (in your case endDate) has a very low selectivity, and the index
is very shallow. Otherwise the cost of the multiple seeks would exceed
the cost of scanning the index pages.
HTH,
Gert-Jan
HardKhor wrote:
> Hi experts,
> I am looking at an estimated execution plan for a query that joins between 2
> significantly big tables. One of the Clustered Index Seek uses has both
> Predicate and Seek Predicate. What are the differences between Seek Predicate
> and Predicate?
> In detail, this is what I have done:
> ...
> INNER JOIN BonusPromotion BP ON
> ...
> AND BP.status = 'A' --Active
> AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
> ...
> The index used is the primary key along fields.
> endDate,
> validStartDate,
> storeID,
> emEAN,
> bpID
> Seek predicate is along field endDate, while Predicate is along field
> validStartDate. My impression of Seek predicate is very fast search
> performance. So why can't the Seek predicate include BOTH endDate and
> validStartDate? Therefore back to my 1st question: What are the differences
> between Seek Predicate and Predicate?

Estimated Execution Plan-Diff betw Seek Predicate and Predicate?

Hi experts,
I am looking at an estimated execution plan for a query that joins between 2
significantly big tables. One of the Clustered Index Seek uses has both
Predicate and Seek Predicate. What are the differences between Seek Predicat
e
and Predicate?
In detail, this is what I have done:
...
INNER JOIN BonusPromotion BP ON
...
AND BP.status = 'A' --Active
AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
...
The index used is the primary key along fields.
endDate,
validStartDate,
storeID,
emEAN,
bpID
Seek predicate is along field endDate, while Predicate is along field
validStartDate. My impression of Seek predicate is very fast search
performance. So why can't the Seek predicate include BOTH endDate and
validStartDate? Therefore back to my 1st question: What are the differences
between Seek Predicate and Predicate?Hi
What are the differences between Seek Predicate
> and Predicate?
http://blogs.msdn.com/craigfr/archi.../07/652668.aspx
.
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:4AC93D3A-69C0-4BD2-9CBD-2EEB86714040@.microsoft.com...
> Hi experts,
> I am looking at an estimated execution plan for a query that joins between
> 2
> significantly big tables. One of the Clustered Index Seek uses has both
> Predicate and Seek Predicate. What are the differences between Seek
> Predicate
> and Predicate?
> In detail, this is what I have done:
> ...
> INNER JOIN BonusPromotion BP ON
> ...
> AND BP.status = 'A' --Active
> AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
> ...
> The index used is the primary key along fields.
> endDate,
> validStartDate,
> storeID,
> emEAN,
> bpID
> Seek predicate is along field endDate, while Predicate is along field
> validStartDate. My impression of Seek predicate is very fast search
> performance. So why can't the Seek predicate include BOTH endDate and
> validStartDate? Therefore back to my 1st question: What are the
> differences
> between Seek Predicate and Predicate?|||HardKhor,
BP.endDate will be the only seek predicate, because you are not
searching for an exact match for that column, but for a range of values.
This means the other predicates will be evaluated by scanning the index
pages in this range of endDate.
Theoretically, it could still seek the next index column (in your case
validStartDate), but SQL Server does not support this. And even if it
did, the multiple (looped) seeks would only be useful if the preceding
column (in your case endDate) has a very low selectivity, and the index
is very shallow. Otherwise the cost of the multiple seeks would exceed
the cost of scanning the index pages.
HTH,
Gert-Jan
HardKhor wrote:
> Hi experts,
> I am looking at an estimated execution plan for a query that joins between
2
> significantly big tables. One of the Clustered Index Seek uses has both
> Predicate and Seek Predicate. What are the differences between Seek Predic
ate
> and Predicate?
> In detail, this is what I have done:
> ...
> INNER JOIN BonusPromotion BP ON
> ...
> AND BP.status = 'A' --Active
> AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
> ...
> The index used is the primary key along fields.
> endDate,
> validStartDate,
> storeID,
> emEAN,
> bpID
> Seek predicate is along field endDate, while Predicate is along field
> validStartDate. My impression of Seek predicate is very fast search
> performance. So why can't the Seek predicate include BOTH endDate and
> validStartDate? Therefore back to my 1st question: What are the difference
s
> between Seek Predicate and Predicate?

Estimated Execution Plan Fails, XML Error?

Good afternoon, all. Whenever I try to use Query | Display Estimated
Execution Plan in SQL Studio on any non-trivial query, I get this unhelpful
message:
Error processing execution plan results. The error message is:
There is an error in XML document (1, 501).
There is an unclosed literal string. Line 1, position 501.
The only suggestion I've heard of is to instal SQL SPs, but that didn't
help. This is SQL 2K5 running on Win2K3. I don't believe there's any
strangeness like odd characters in instance names, and all language settings
are default. I've seen this before on another server, but Google has nothing.
Any ideas? Thanks!
I think I heard that the XML used for this can not handle some unusual
object names, but at best that is a long shot.
Roy Harvey
Beacon Falls, CT
On Mon, 6 Aug 2007 13:32:02 -0700, JonOfAllTrades
<JonOfAllTrades@.discussions.microsoft.com> wrote:

> Good afternoon, all. Whenever I try to use Query | Display Estimated
>Execution Plan in SQL Studio on any non-trivial query, I get this unhelpful
>message:
>Error processing execution plan results. The error message is:
>There is an error in XML document (1, 501).
>There is an unclosed literal string. Line 1, position 501.
> The only suggestion I've heard of is to instal SQL SPs, but that didn't
>help. This is SQL 2K5 running on Win2K3. I don't believe there's any
>strangeness like odd characters in instance names, and all language settings
>are default. I've seen this before on another server, but Google has nothing.
> Any ideas? Thanks!

Estimated execution plan

I've been using the estimated execution plan feature in SQL 2005 and I see that the "cost %" is sometimes just wacko (e.g. 333% or 25173%).

That makes me wonder just how much I can count on the estimated subtree cost value for a given path in the plan. I relied on this value (subtree cost) in SQL 2000 and would occasionally see the same kinds of wacko "cost%". I was hoping that 2005 would clean this up.

I am aware that the calculations in the plan depend upon a representative database of data. I think I am very experienced using the estimated execution plan tool in SQL 2000. I'm just concerned that, with the new release, that perhaps I shouldn't trust it as much, especially after continuing to see the wierdness with the cost% displayed.

Does anyone have any reliable information about whether the estimated subtree cost is something I should rely upon, even if the cost% is screwy?

Thanks for any insight.

To clarify the original question:

Is the calculation for an estimated subtree cost calculated independently of the displayed "cost %" for an individual, graphically-displayed (logical | physical) operator in the estimated execution plan?

I would guess that it could be independent since the subtree cost should be: a percent of the overall procedure being estimated

whereas the "cost %" for an individual, graphically-displayed (logical | physical) operator is a percent of the estimated cost of the command to be executed.

On the other-hand, the same operator cost value could also be used in both calculations.

Probably only the person who wrote that code might know, so I will do some experimenting to see if the values equate between SQL versions. Also, I am overall more pleased with what I see in the 2005 estimated execution plan presentation so don't judge this as a strong complaint. The new version is more useful.

Estimated Execution Plan

How do I display this in Query Analyzer ?Hi,
Go to Query menu -- Click Show Execution plan.
After that execute the Query.
Thanks
Hari
SQL Server MVP
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%23blIBj5yEHA.3236@.TK2MSFTNGP15.phx.gbl...
> How do I display this in Query Analyzer ?
>|||To display the estimated execution plan, select Query --> Display Estimated
Execution Plan on the menu or press Ctrl-L.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%23blIBj5yEHA.3236@.TK2MSFTNGP15.phx.gbl...
> How do I display this in Query Analyzer ?
>|||On Tue, 16 Nov 2004 08:10:29 -0600, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:
>To display the estimated execution plan, select Query --> Display Estimated
>Execution Plan on the menu or press Ctrl-L.
Or click the little icon.
Or SET SHOWPLAN_ALL ON or SET SHOWPLAN_TEXT ON.
J.sql

Estimated Execution Plan

How do I display this in Query Analyzer ?
Hi,
Go to Query menu -- Click Show Execution plan.
After that execute the Query.
Thanks
Hari
SQL Server MVP
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%23blIBj5yEHA.3236@.TK2MSFTNGP15.phx.gbl...
> How do I display this in Query Analyzer ?
>
|||To display the estimated execution plan, select Query --> Display Estimated
Execution Plan on the menu or press Ctrl-L.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%23blIBj5yEHA.3236@.TK2MSFTNGP15.phx.gbl...
> How do I display this in Query Analyzer ?
>
|||On Tue, 16 Nov 2004 08:10:29 -0600, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:
>To display the estimated execution plan, select Query --> Display Estimated
>Execution Plan on the menu or press Ctrl-L.
Or click the little icon.
Or SET SHOWPLAN_ALL ON or SET SHOWPLAN_TEXT ON.
J.

Estimated Execution Plan

How do I display this in Query Analyzer ?Hi,
Go to Query menu -- Click Show Execution plan.
After that execute the Query.
Thanks
Hari
SQL Server MVP
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%23blIBj5yEHA.3236@.TK2MSFTNGP15.phx.gbl...
> How do I display this in Query Analyzer ?
>|||To display the estimated execution plan, select Query --> Display Estimated
Execution Plan on the menu or press Ctrl-L.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%23blIBj5yEHA.3236@.TK2MSFTNGP15.phx.gbl...
> How do I display this in Query Analyzer ?
>|||On Tue, 16 Nov 2004 08:10:29 -0600, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:
>To display the estimated execution plan, select Query --> Display Estimated
>Execution Plan on the menu or press Ctrl-L.
Or click the little icon.
Or SET SHOWPLAN_ALL ON or SET SHOWPLAN_TEXT ON.
J.

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
MCDBAIt'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:
>> 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
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:
>> 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
> 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.

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.

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
MCDBAIt'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 wi
ll
> 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.sql