Query longer in SP: plan

Showing posts with label plan. Show all posts

Friday, March 23, 2012

Query Plans differs

Why shoudl the query plan derived by an SP and one on Query Analyser would
differ widely?
I have a system that has been running stably for a while now when one of the
queries started behaving badly. The SP version of the query comes out with
an estimated cost of 0.311 whereas if I take the same TSQL and paste it into
Query Analyser it estimates as 0.153.
Unfortuately, the query plan cost isn't the whole story as when estimated
the SP performs about 5m page reads whereas the explicit code only takes 800
page reads - this makes a big difference in execution time :-)
Any ideas how to solve this - I've tried WITH RECOMPILE on the SP, but it
doesn't effect the QP choosen.
Regards
PaulPaul,
The two pieces of code, TSQL script and stored procedure, are not optimized
the same because of the knowledge available at the time of optimization.
For example, the TSQL script has the variables set with the values that you
want to run.
Here is one thing that causes stored procedures to be less than optimal:
If internally to a stored proc you set a variable, the optimizer (which has
no value for the variable when it optimizes) may have selected a suboptimal
plan. So, if this is your case, rather than:
DECLARE @.MyVal INT
SET @.MyVal = 123
SELECT ... WHERE MyCol = @.MyVal
You could do:
DECLARE @.MyVal INT
SET @.MyVal = 123
EXEC AnotherStoredProcedure @.MyVal
The called stored procedure will optimize with the value supplied in @.MyVal.
Remember, of course, that recompiles of stored procedures are common, so
this is not as simple as black and white.
And, of course, there are other factors that could mislead the optimizer.
RLF
"Paul Hatcher" <phatcher@.spamless.cix.co.uk> wrote in message
news:uU1mWm$HFHA.1580@.TK2MSFTNGP10.phx.gbl...
> Why shoudl the query plan derived by an SP and one on Query Analyser would
> differ widely?
> I have a system that has been running stably for a while now when one of
> the
> queries started behaving badly. The SP version of the query comes out with
> an estimated cost of 0.311 whereas if I take the same TSQL and paste it
> into
> Query Analyser it estimates as 0.153.
> Unfortuately, the query plan cost isn't the whole story as when estimated
> the SP performs about 5m page reads whereas the explicit code only takes
> 800
> page reads - this makes a big difference in execution time :-)
> Any ideas how to solve this - I've tried WITH RECOMPILE on the SP, but it
> doesn't effect the QP choosen.
> Regards
> Paul
>|||Thanks, there was one such variable, so I changed the code as per your
suggestion - reduced it from 5m reads to around 4.5m :-)
The optimiser normally does such a good job that it's just a pain when it
gets it wrong. Given the nature of this query, it's almost impossible to put
any useful hints that wouldn't cause much more pain elsewhere in the system.
I'll have to see if I can program around it with a different query just to
get it back running - previously it took around 100ms now it takes 10s+
Paul
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:#u#Rlw$HFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Paul,
> The two pieces of code, TSQL script and stored procedure, are not
optimized
> the same because of the knowledge available at the time of optimization.
> For example, the TSQL script has the variables set with the values that
you
> want to run.
> Here is one thing that causes stored procedures to be less than optimal:
> If internally to a stored proc you set a variable, the optimizer (which
has
> no value for the variable when it optimizes) may have selected a
suboptimal
> plan. So, if this is your case, rather than:
> DECLARE @.MyVal INT
> SET @.MyVal = 123
> SELECT ... WHERE MyCol = @.MyVal
> You could do:
> DECLARE @.MyVal INT
> SET @.MyVal = 123
> EXEC AnotherStoredProcedure @.MyVal
> The called stored procedure will optimize with the value supplied in
@.MyVal.
> Remember, of course, that recompiles of stored procedures are common, so
> this is not as simple as black and white.
> And, of course, there are other factors that could mislead the optimizer.
> RLF
> "Paul Hatcher" <phatcher@.spamless.cix.co.uk> wrote in message
> news:uU1mWm$HFHA.1580@.TK2MSFTNGP10.phx.gbl...
would
with
estimated
it
>|||Paul,
Maybe if you post the query in question (accompanied with the relevant
DDL) someone might be able to give a more specific advice...
Gert-Jan
Paul Hatcher wrote:
> Thanks, there was one such variable, so I changed the code as per your
> suggestion - reduced it from 5m reads to around 4.5m :-)
> The optimiser normally does such a good job that it's just a pain when it
> gets it wrong. Given the nature of this query, it's almost impossible to p
ut
> any useful hints that wouldn't cause much more pain elsewhere in the syste
m.
> I'll have to see if I can program around it with a different query just to
> get it back running - previously it took around 100ms now it takes 10s+
> Paul
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:#u#Rlw$HFHA.2924@.TK2MSFTNGP15.phx.gbl...
> optimized
> you
> has
> suboptimal
> @.MyVal.
> would
> with
> estimated
> it|||Gert-Jan
Thanks for the offer, but it's too complicated - as an example I would guess
that the query pans out to about a 30 table join, mostly self-joins as it's
a triple-based schema, but still fairly hairy.
I've now managed to solve it by a different structured query.
Paul
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:422766C1.A0FE26A1@.toomuchspamalready.nl...
> Paul,
> Maybe if you post the query in question (accompanied with the relevant
> DDL) someone might be able to give a more specific advice...
> Gert-Jan
>
> Paul Hatcher wrote:
it
put
system.
to
optimization.
that
optimal:
(which
so
optimizer.
one of
out
it
takes
butsql

Query plan variation

Hello all:
I have several databases (one per customer) on several different
servers. The servers and databases are alike in structure. However,
one of the databases is consistently coming up with different (and
slower) query plans than the others (which are consistent), even
disregarding the correct indices to use until a table hint is
provided, something the other databases are not needing. I repeat that
the databases all have an identical structure - except, I suppose, for
whatever is causing this behavior.
Can anyone point me in the direction of why this might be happening?
Thanks,
zdrakecStatistics? Radically different data?
http://msdn2.microsoft.com/en-us/library/aa260645(SQL.80).aspx
Just a thought. If that turns out to be it, maybe look into the Auto Update
Statistics option for the database.
//Andrew
> Hello all:
> I have several databases (one per customer) on several different
> servers. The servers and databases are alike in structure. However,
> one of the databases is consistently coming up with different (and
> slower) query plans than the others (which are consistent), even
> disregarding the correct indices to use until a table hint is
> provided, something the other databases are not needing. I repeat that
> the databases all have an identical structure - except, I suppose, for
> whatever is causing this behavior.
> Can anyone point me in the direction of why this might be happening?
> Thanks,
> zdrakec|||On Sep 4, 4:26 pm, Andrew Backer <awbac...@.gmail.com> wrote:
> Statistics? Radically different data?
> http://msdn2.microsoft.com/en-us/library/aa260645(SQL.80).aspx
> Just a thought. If that turns out to be it, maybe look into the Auto Update
> Statistics option for the database.
> //Andrew
>
> > Hello all:
> > I have several databases (one per customer) on several different
> > servers. The servers and databases are alike in structure. However,
> > one of the databases is consistently coming up with different (and
> > slower) query plans than the others (which are consistent), even
> > disregarding the correct indices to use until a table hint is
> > provided, something the other databases are not needing. I repeat that
> > the databases all have an identical structure - except, I suppose, for
> > whatever is causing this behavior.
> > Can anyone point me in the direction of why this might be happening?
> > Thanks,
> > zdrakec- Hide quoted text -
> - Show quoted text -
Thank you Andrew:
Well, the data is of course different, but only in details, not in
form, so not radically so. We have an app I wrote that updates the
statistics on all the databases on a regular basis, so I don't think
that is it either. However, I do believe that it has something to do
with the setup.
Thanks for you input,
zdrakec

Query plan utilization vs CPU time...

I was tuning a query testing out SARG with these two queries:

select col1 from table1 (nolock) where col1 like '#,%ABC%' or col1 like 'BC,%ABC%'
select col1 from table1 (nolock) where col1 like '%ABC%'

I flushed out the cache, added an index to col1, then ran those two together. Provided below are the actual query plan and stat time:

Query 1: Query cost (relative to the batch): 0%
select col1 from table1 (nolock) where col1 like '#,%ABC%' or col1 like 'BC,%ABC%'
-
SELECT Index Seek
Cost:0% <- [DB1].[dbo].[table1].[Idx..]

Cost: 100%

Query 2: Query cost (relative to the batch): 100%
select col1 from table1 (nolock) where col1 like '%ABC%'
-
SELECT Index Scan
Cost:0% <- [DB1].[dbo].[table1].[Idx..]

Cost: 100%

STAT TIME--
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 7 ms.

(3 row(s) affected)
Table 'table1'. Scan count 2, logical reads 2932, physical reads 0, read-ahead reads 18, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 938 ms, elapsed time = 943 ms.

(3 row(s) affected)
Table 'table1'. Scan count 1, logical reads 2927, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 515 ms, elapsed time = 505 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

STAT TIME--

As expected, SARGable Query 1 did a nonclustered index seek and nonSARGable Query 2 did an index scan instead. According to the query plan, Query 1 consumed 0% relative to the batch whereas Query 2 is 100%. When I checked the CPU time, I was a bit confused because Query 1 showed CPU time of 938ms whereas Query 2 showed 515ms. I triple checked and every time I got similar results. I am sure I'm missing something, could someone please tell me what I'm missing? Thanks a bunch!

Btw, I'm using SQL Server 2005. Any suggestion please, even just tell me if this is the right place to post this question. Hope some of you know the answer to this. Thanks!

Query plan utilization vs CPU time...

I was tuning a query testing out SARG with these two queries:

select col1 from table1 (nolock) where col1 like '#,%ABC%' or col1 like 'BC,%ABC%'
select col1 from table1 (nolock) where col1 like '%ABC%'

I flushed out the cache, added an index to col1, then ran those two together. Provided below are the actual query plan and stat time:

Query 1: Query cost (relative to the batch): 0%
select col1 from table1 (nolock) where col1 like '#,%ABC%' or col1 like 'BC,%ABC%'
-
SELECT Index Seek
Cost:0% <- [DB1].[dbo].[table1].[Idx..]

Cost: 100%

Query 2: Query cost (relative to the batch): 100%
select col1 from table1 (nolock) where col1 like '%ABC%'
-
SELECT Index Scan
Cost:0% <- [DB1].[dbo].[table1].[Idx..]

Cost: 100%

STAT TIME--
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 7 ms.

(3 row(s) affected)
Table 'table1'. Scan count 2, logical reads 2932, physical reads 0, read-ahead reads 18, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 938 ms, elapsed time = 943 ms.

(3 row(s) affected)
Table 'table1'. Scan count 1, logical reads 2927, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 515 ms, elapsed time = 505 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

STAT TIME--

Btw, I'm using SQL Server 2005. Any suggestion please, even just tell me if this is the right place to post this question. Hope some of you know the answer to this. Thanks!
sql

Query plan re-use on views?

Here's the setup:

Client database has a complex view with eight nested subqueries used to return "dashboard" information. The application code uses NHibernate to call and filter the view with three parameters, one of which is the CustomerID.

A certain customer, (the biggest client), has more than ten times the number of records of the next largest customer.

Occasionally, the database reaches a state where when this particular customer tries to run the dashboard view, the application times out.

If I open up the view and re-save it, all is well again for a few days.

What gives?

Views are supposedly not pre-compiled, though I know that 2000 stores bits and pieces of query plans.

Any ideas on what causes this and what to do about it?are you experiencing large amounts of new data on a regular basis throwing off statistics and fragmenting your indices. just cuz they ain't precompiled don;t mean that they do not use that stuff right?|||No. Loaded a ton of historical data when the app was first installed, but inserts have been slow and steady since then.

Query plan question

Is there a way we can figure out if the query optimiser is using a
particular stat/s ( Non indexed specifically ) to determine a query plan ?
I understand when missing stats are noticed, its obviously looking for it
but if we dont see any, can we tell if its looking at some non indexed stats
to come to a conclusion .
Also how does update stats affect performance on a highly OLTP system ? Does
it include blocking ,etc. ?sql server will use any stats available,
make a table, populate one column with unique values, but
not indexed, then query against it, notice that the
execution plan will have a fairly accurate estimate of the
expected row count, even though it is scanning the entire
table
stats will matter if it influences the plan, notice that
loop joins may exhibit a spike in plan cost at ~130 rows,
so there is a tendency to shift to scan
>--Original Message--
>Is there a way we can figure out if the query optimiser
is using a
>particular stat/s ( Non indexed specifically ) to
determine a query plan ?
>I understand when missing stats are noticed, its
obviously looking for it
>but if we dont see any, can we tell if its looking at
some non indexed stats
>to come to a conclusion .
>Also how does update stats affect performance on a highly
OLTP system ? Does
>it include blocking ,etc. ?
>
>.
>|||> sql server will use any stats available,
> make a table, populate one column with unique values, but
> not indexed, then query against it, notice that the
> execution plan will have a fairly accurate estimate of the
> expected row count, even though it is scanning the entire
> table
When does this happen ? When does it make this table you referred to ? Is
this at the time of update stats or create stats or every time a query runs
.
You also mentioned that it will use any stats available. Does it even use
those that may not be needed ?
> loop joins may exhibit a spike in plan cost at ~130 rows,
> so there is a tendency to shift to scan
What scan are you talking about ?
"joe chang" <jchang6@.yahoo.com> wrote in message
news:0ea901c37281$fd521af0$a001280a@.phx.gbl...
> sql server will use any stats available,
> make a table, populate one column with unique values, but
> not indexed, then query against it, notice that the
> execution plan will have a fairly accurate estimate of the
> expected row count, even though it is scanning the entire
> table
> stats will matter if it influences the plan, notice that
> loop joins may exhibit a spike in plan cost at ~130 rows,
> so there is a tendency to shift to scan
> >--Original Message--
> >Is there a way we can figure out if the query optimiser
> is using a
> >particular stat/s ( Non indexed specifically ) to
> determine a query plan ?
> >I understand when missing stats are noticed, its
> obviously looking for it
> >but if we dont see any, can we tell if its looking at
> some non indexed stats
> >to come to a conclusion .
> >
> >Also how does update stats affect performance on a highly
> OLTP system ? Does
> >it include blocking ,etc. ?
> >
> >
> >.
> >|||the following white paper contains a good description of the statistics
framework in SQL Server 2000 and probably answers all your questions.
http://msdn.microsoft.com/library/default.asp?URL=/library/techart/statquery.htm
Peter Zabback
SQL Server Development
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OFA7oOocDHA.1696@.TK2MSFTNGP10.phx.gbl...
> > sql server will use any stats available,
> > make a table, populate one column with unique values, but
> > not indexed, then query against it, notice that the
> > execution plan will have a fairly accurate estimate of the
> > expected row count, even though it is scanning the entire
> > table
> When does this happen ? When does it make this table you referred to ? Is
> this at the time of update stats or create stats or every time a query
runs
> .
> You also mentioned that it will use any stats available. Does it even use
> those that may not be needed ?
> > loop joins may exhibit a spike in plan cost at ~130 rows,
> > so there is a tendency to shift to scan
> What scan are you talking about ?
> "joe chang" <jchang6@.yahoo.com> wrote in message
> news:0ea901c37281$fd521af0$a001280a@.phx.gbl...
> > sql server will use any stats available,
> > make a table, populate one column with unique values, but
> > not indexed, then query against it, notice that the
> > execution plan will have a fairly accurate estimate of the
> > expected row count, even though it is scanning the entire
> > table
> > stats will matter if it influences the plan, notice that
> > loop joins may exhibit a spike in plan cost at ~130 rows,
> > so there is a tendency to shift to scan
> >
> > >--Original Message--
> > >Is there a way we can figure out if the query optimiser
> > is using a
> > >particular stat/s ( Non indexed specifically ) to
> > determine a query plan ?
> > >I understand when missing stats are noticed, its
> > obviously looking for it
> > >but if we dont see any, can we tell if its looking at
> > some non indexed stats
> > >to come to a conclusion .
> > >
> > >Also how does update stats affect performance on a highly
> > OLTP system ? Does
> > >it include blocking ,etc. ?
> > >
> > >
> > >.
> > >
>

Query Plan Question

I'm running SQL Server 2000 Std SP3 on Windows 2000 Standard SP4.
I have the following query:
declare @.FromDate as DATETIME
SET @.FromDate = '2004-03-10'
Select Count(*)
From URLS
Where (URLString like '%homepage%')
And AddedOn >= @.FromDate
Select Count(*)
From URLS
Where (URLString like '%homepage%')
And AddedOn >= '2004-03-10'
The first query which uses @.FromDate does a table scan. The second query th
at has the date hard coded uses the index on the AddedOn date field.
My question is why doesn't the first query also use the index? There are 10
Million + rows in the table.
Thanks,
StephenIf you use a variable in a WHERE clause, then the optimizer doesn't know
what value you are looking for (the optimizer optimizes statement by
statement). So, it will have to guess number of rows to be returned. I don't
recall the values it guesses (you find them in the Inside SQL Server book),
but I think that it is either 10% or 25% for greater then. Say you have 10
million rows, this means that SQL Server will read 1 million rows. Say you
have an NC index, then SQL Server will potentially need to jump to a data
page for each row. This means 1 million data page accesses.
Above is just to give you an understand about how the optimizer works. Note
that using a variable and a stored procedure parameter are two different
things!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Stephen Schissler" <anonymous@.discussions.microsoft.com> wrote in message
news:4A78A446-AC10-4F41-9EED-A048B3B5864D@.microsoft.com...
> I'm running SQL Server 2000 Std SP3 on Windows 2000 Standard SP4.
> I have the following query:
> declare @.FromDate as DATETIME
> SET @.FromDate = '2004-03-10'
> Select Count(*)
> From URLS
> Where (URLString like '%homepage%')
> And AddedOn >= @.FromDate
>
> Select Count(*)
> From URLS
> Where (URLString like '%homepage%')
> And AddedOn >= '2004-03-10'
> The first query which uses @.FromDate does a table scan. The second query
that has the date hard coded uses the index on the AddedOn date field.
> My question is why doesn't the first query also use the index? There are
10 Million + rows in the table.
> Thanks,
> Stephen|||It guesses?
So if I have 1 record of 10 million where the AddedOn date is equal to '2004
-03-10' and I pass this value as a variable it will do a table scan? That s
eems to me to be the wrong thing to do. I've updated the statistics on the
AddedOn date field using th
e FULLSCAN option and it still does a table scan which I find very disturbin
g. I'm now wondering how many other queries that use variables as a paramet
er are choosing the wrong query plan due to this.
Thanks,
Stephen|||Stephen Schissler wrote:

> It guesses?
> So if I have 1 record of 10 million where the AddedOn date is equal to '2004-03-10
' and I pass this value as a variable it will do a table scan? That seems to me to
be the wrong thing to do. I've updated the statistics on the AddedOn date field usi
ng
the FULLSCAN option and it still does a table scan which I find very disturbing. I'm now w
ondering how many other queries that use variables as a parameter are choosing the wrong qu
ery plan due to this.
> Thanks,
> Stephen
>
If you run it as a stored procedure and use the where clause as a
parameter you will notice different results.
Aaron Weiker
http://blogs.sqladvice.com/aweiker
http://aaronweiker.com/|||> It guesses?
What else can it do? Well, not a wild guess, it has its rules. The optimizer
does not know the value of the variable, as it optimizes statement by
statement. (Yes, one could question why that it, but it is the way SQL
Server work.) As I said, for different predicates, SQL Server estimates to
return different percentage of rows. Details in Inside SQL Server.
Statistics has nothing to do with this.
If you use a constant or a stored procedure parameter, it s a different
thing, though!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Stephen Schissler" <anonymous@.discussions.microsoft.com> wrote in message
news:23380C0C-0820-4155-BA43-D574741DC19C@.microsoft.com...
> It guesses?
> So if I have 1 record of 10 million where the AddedOn date is equal to
'2004-03-10' and I pass this value as a variable it will do a table scan?
That seems to me to be the wrong thing to do. I've updated the statistics
on the AddedOn date field using the FULLSCAN option and it still does a
table scan which I find very disturbing. I'm now wondering how many other
queries that use variables as a parameter are choosing the wrong query plan
due to this.
> Thanks,
> Stephen

query plan on sql server 2005

Hi,
I've got the following query:
exec sp_executesql N'
select i.issuer_id,
i.issuer_name,
i.last_update ,
ia_bbeqtk.alias ,
ia_bbeqtk.last_update,
ia_bq.alias ,
ia_bq.last_update
from issuer i
left outer join issuer_alias as ia_bbeqtk
on ia_bbeqtk.issuer_id = i.issuer_id
and ia_bbeqtk.alias_type = ''BBEQTK''
left outer join issuer_alias as ia_bq
on ia_bq.issuer_id = i.issuer_id
and ia_bq.alias_type = ''BQUOTE''
where i.last_update > @.IssuerLastUpdate
or ia_bbeqtk.last_update > @.BlastUpdate
or ia_bq.last_update > @.BQLastUpdate
',N'@.IssuerLastUpdate datetime,@.BlastUpdate datetime,@.BQLastUpdate
datetime',@.IssuerLastUpdate=''2006-06-06
09:05:41:003'',@.BlastUpdate=''2006-06-01
18:09:17:937'',@.BQLastUpdate=''2006-06-06
09:05:41:003''
And here are the indexes for the 2 tables
Issuer (approx 4,000 rows)
^^^^
index_name index_description index_keys
IX_issuer nonclustered, stats no recompute located on PRIMARY last_update,
issuer_id
PK__issuer__3EFC4F81 clustered, unique, primary key located on
PRIMARY issuer_id
UQ__issuer__3FF073BA nonclustered, unique, unique key located on
PRIMARY issuer_name
Issuer_alias (approx 18,000 rows)
^^^^^^^
index_name index_description index_keys
IX_issuer_alias nonclustered, unique located on PRIMARY issuer_id, alias_typ
e
PK__issuer_alias__5303482E clustered, unique, primary key located on
PRIMARY alias_type, alias
Now when I execute the query it completely ignores the fact that last_update
column has more than 4000 rows with an update column of 29 May 2006 and the
rest 10-20 are after that. Instead it uses the clustered index on the isssue
r
table PK__issuer__3EFC4F81 instead of IX_issuer! Instead if it went for the
last_update column first you get 2 rows selectivity by searching the non
clustered and then it can run the query much quicker by linking on the
issuer_id.
I've also checked the stats on the table and they seem ok. Does anybody can
explain this behaviour which is not the most efficient in this case?
Thanks,
Panos.4000 rows are not too much for a database engine. So SQL Server, based on it
s
cost optimization algorithm, might have thought that it will be easier to
scan through the 4000 rows rather than reading the index and getting the
other coulmns from the table again. the query execution plan might (and I
guess will) change as the number of rows increases and when it reaches the
breakeven point.
Hope this helps.|||Hi Pano,
The the last two ("OR") predicates in your query prevent the usage of the
non-clustered index in the way that you describe. In other words the query
processor cannot push the filter on i.last_update below the left-outer join
and produce correct results (it would be filtering rows based on the
last_update column ,which could still qualify if they satisfy either the two
predicates on bbeqtk.last_update and bq.last_update).
However, if your predicates were joint with an AND instead of an OR you
should see the filter on last_update being pushed down below the join and
into an index s

(assuming that the plan which uses the non-clustered
index s

would be estimated as more efficient than the plan which uses the
clustered index scan by the query optimizer).
Regards,
Leo
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:BCEC9194-2EFC-4957-8120-A2B29FCED4E2@.microsoft.com...
> Hi,
> I've got the following query:
> exec sp_executesql N'
> select i.issuer_id,
> i.issuer_name,
> i.last_update ,
> ia_bbeqtk.alias ,
> ia_bbeqtk.last_update,
> ia_bq.alias ,
> ia_bq.last_update
> from issuer i
> left outer join issuer_alias as ia_bbeqtk
> on ia_bbeqtk.issuer_id = i.issuer_id
> and ia_bbeqtk.alias_type = ''BBEQTK''
> left outer join issuer_alias as ia_bq
> on ia_bq.issuer_id = i.issuer_id
> and ia_bq.alias_type = ''BQUOTE''
> where i.last_update > @.IssuerLastUpdate
> or ia_bbeqtk.last_update > @.BlastUpdate
> or ia_bq.last_update > @.BQLastUpdate
> ',N'@.IssuerLastUpdate datetime,@.BlastUpdate datetime,@.BQLastUpdate
> datetime',@.IssuerLastUpdate=''2006-06-06
> 09:05:41:003'',@.BlastUpdate=''2006-06-01
> 18:09:17:937'',@.BQLastUpdate=''2006-06-06
> 09:05:41:003''
> And here are the indexes for the 2 tables
> Issuer (approx 4,000 rows)
> ^^^^
> index_name index_description index_keys
> IX_issuer nonclustered, stats no recompute located on PRIMARY last_update,
> issuer_id
> PK__issuer__3EFC4F81 clustered, unique, primary key located on
> PRIMARY issuer_id
> UQ__issuer__3FF073BA nonclustered, unique, unique key located on
> PRIMARY issuer_name
> Issuer_alias (approx 18,000 rows)
> ^^^^^^^
> index_name index_description index_keys
> IX_issuer_alias nonclustered, unique located on PRIMARY issuer_id,
> alias_type
> PK__issuer_alias__5303482E clustered, unique, primary key located on
> PRIMARY alias_type, alias
> Now when I execute the query it completely ignores the fact that
> last_update
> column has more than 4000 rows with an update column of 29 May 2006 and
> the
> rest 10-20 are after that. Instead it uses the clustered index on the
> isssuer
> table PK__issuer__3EFC4F81 instead of IX_issuer! Instead if it went for
> the
> last_update column first you get 2 rows selectivity by searching the non
> clustered and then it can run the query much quicker by linking on the
> issuer_id.
> I've also checked the stats on the table and they seem ok. Does anybody
> can
> explain this behaviour which is not the most efficient in this case?
> Thanks,
> Panos.

Query plan miscalculates row count causing system slow down?

Has anyone seem this before. I've actually caught it once but I think
it's happening multiple times. I have a partitioned view and
performance is generally really good. However, every once in a great
while we'll see a stored procedure "hang" and just run for hours.
Doing much digging the only abnormalities I found where these: 1) the
server was requesting a large amount of PAGE and KEY locks (meaning an
index lock was requested. Checking the locks for that SPID confirmed
this) 2) At one time during testing I saw that the execution plan
called for an estimated 1 billion rows to be returned. Amazing since
the underlying table only have 400,000 records. Now, the theory is
this: Since I'm using a partitioned view and Microsoft claims that
they retain no histogram (statistical data from which to build an
"intelligent" execution plan) somehow it's figuring this 1 billion
estimated row count again and is entering some kind of horrendous
loop. Any thoughts?Have you checked the fragentation of the indexes on the table(s) that the sp
uses?
dbcc showcontig ('table_name').
If the scan desity is low, you might want to do a defrag.
dbcc indexdefrag ('database_name','table_name','index_name')
or in 2005
alter index <index_name> | ALL
rebuild with online = ON
on <table_name>
Sometimes rebuilding is better because is certain cases, indexdefrag doesn't
make the all of index pages contiguous.
--
MG
"tmorris" wrote:
> Has anyone seem this before. I've actually caught it once but I think
> it's happening multiple times. I have a partitioned view and
> performance is generally really good. However, every once in a great
> while we'll see a stored procedure "hang" and just run for hours.
> Doing much digging the only abnormalities I found where these: 1) the
> server was requesting a large amount of PAGE and KEY locks (meaning an
> index lock was requested. Checking the locks for that SPID confirmed
> this) 2) At one time during testing I saw that the execution plan
> called for an estimated 1 billion rows to be returned. Amazing since
> the underlying table only have 400,000 records. Now, the theory is
> this: Since I'm using a partitioned view and Microsoft claims that
> they retain no histogram (statistical data from which to build an
> "intelligent" execution plan) somehow it's figuring this 1 billion
> estimated row count again and is entering some kind of horrendous
> loop. Any thoughts?
>|||Look in BOL for update statistics
--
TheSQLGuru
President
Indicium Resources, Inc.
"tmorris" <TheRealPawn@.gmail.com> wrote in message
news:1177340474.292787.166810@.q75g2000hsh.googlegroups.com...
> Has anyone seem this before. I've actually caught it once but I think
> it's happening multiple times. I have a partitioned view and
> performance is generally really good. However, every once in a great
> while we'll see a stored procedure "hang" and just run for hours.
> Doing much digging the only abnormalities I found where these: 1) the
> server was requesting a large amount of PAGE and KEY locks (meaning an
> index lock was requested. Checking the locks for that SPID confirmed
> this) 2) At one time during testing I saw that the execution plan
> called for an estimated 1 billion rows to be returned. Amazing since
> the underlying table only have 400,000 records. Now, the theory is
> this: Since I'm using a partitioned view and Microsoft claims that
> they retain no histogram (statistical data from which to build an
> "intelligent" execution plan) somehow it's figuring this 1 billion
> estimated row count again and is entering some kind of horrendous
> loop. Any thoughts?
>|||DBCC dbreindex is preformed on all tables once a week as a form of
matanance.
We've concided doing this again midweek or one of the other processes
that update statistics on columns and indexes in SQL 2000. However, in
the test enviroment it doesn't seem to have any impact one way or
another. Like I, and microsoft said, partitioned views don't contain
any statistical history from which to base an exection plan. Thanks
for the suggestion though. I'm looking more of someone to confirm my
theory or say it's complete bunk.
Now, the theory is this: Since I'm using a partitioned view and
Microsoft claims that they retain no histogram (statistical data from
which to build an "intelligent" execution plan) somehow it's figuring
this 1 billion estimated row count again and is entering some kind of
horrendous loop. Any thoughts?
On Apr 23, 11:26 am, Hurme <michael.ge...@.thomson.com> wrote:
> Have you checked the fragentation of the indexes on the table(s) that the sp
> uses?
> dbcc showcontig ('table_name').
> If the scan desity is low, you might want to do a defrag.
> dbcc indexdefrag ('database_name','table_name','index_name')
> or in 2005
> alter index <index_name> | ALL
> rebuild with online = ON
> on <table_name>
> Sometimes rebuilding is better because is certain cases, indexdefrag doesn't
> make the all of index pages contiguous.
> --
> MG
> "tmorris" wrote:
> > Has anyone seem this before. I've actually caught it once but I think
> > it's happening multiple times. I have a partitioned view and
> > performance is generally really good. However, every once in a great
> > while we'll see a stored procedure "hang" and just run for hours.
> > Doing much digging the only abnormalities I found where these: 1) the
> > server was requesting a large amount of PAGE and KEY locks (meaning an
> > index lock was requested. Checking the locks for that SPID confirmed
> > this) 2) At one time during testing I saw that the execution plan
> > called for an estimated 1 billion rows to be returned. Amazing since
> > the underlying table only have 400,000 records. Now, the theory is
> > this: Since I'm using a partitioned view and Microsoft claims that
> > they retain no histogram (statistical data from which to build an
> > "intelligent" execution plan) somehow it's figuring this 1 billion
> > estimated row count again and is entering some kind of horrendous
> > loop. Any thoughts?|||We Run DBCC ReIndex on all tables as a weekly maintenance job. And we
looked as doing update statistics 25% midweek but it seemed to have no
effect in our test environment
On Apr 23, 12:42 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> Look in BOL for update statistics
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "tmorris" <TheRealP...@.gmail.com> wrote in message
> news:1177340474.292787.166810@.q75g2000hsh.googlegroups.com...
> > Has anyone seem this before. I've actually caught it once but I think
> > it's happening multiple times. I have a partitioned view and
> > performance is generally really good. However, every once in a great
> > while we'll see a stored procedure "hang" and just run for hours.
> > Doing much digging the only abnormalities I found where these: 1) the
> > server was requesting a large amount of PAGE and KEY locks (meaning an
> > index lock was requested. Checking the locks for that SPID confirmed
> > this) 2) At one time during testing I saw that the execution plan
> > called for an estimated 1 billion rows to be returned. Amazing since
> > the underlying table only have 400,000 records. Now, the theory is
> > this: Since I'm using a partitioned view and Microsoft claims that
> > they retain no histogram (statistical data from which to build an
> > "intelligent" execution plan) somehow it's figuring this 1 billion
> > estimated row count again and is entering some kind of horrendous
> > loop. Any thoughts?

Query plan miscalculates row count causing system slow down?

Has anyone seem this before. I've actually caught it once but I think
it's happening multiple times. I have a partitioned view and
performance is generally really good. However, every once in a great
while we'll see a stored procedure "hang" and just run for hours.
Doing much digging the only abnormalities I found where these: 1) the
server was requesting a large amount of PAGE and KEY locks (meaning an
index lock was requested. Checking the locks for that SPID confirmed
this) 2) At one time during testing I saw that the execution plan
called for an estimated 1 billion rows to be returned. Amazing since
the underlying table only have 400,000 records. Now, the theory is
this: Since I'm using a partitioned view and Microsoft claims that
they retain no histogram (statistical data from which to build an
"intelligent" execution plan) somehow it's figuring this 1 billion
estimated row count again and is entering some kind of horrendous
loop. Any thoughts?Have you checked the fragentation of the indexes on the table(s) that the sp
uses?
dbcc showcontig ('table_name').
If the scan desity is low, you might want to do a defrag.
dbcc indexdefrag ('database_name','table_name','index_nam
e')
or in 2005
alter index <index_name> | ALL
rebuild with online = ON
on <table_name>
Sometimes rebuilding is better because is certain cases, indexdefrag doesn't
make the all of index pages contiguous.
--
MG
"tmorris" wrote:

> Has anyone seem this before. I've actually caught it once but I think
> it's happening multiple times. I have a partitioned view and
> performance is generally really good. However, every once in a great
> while we'll see a stored procedure "hang" and just run for hours.
> Doing much digging the only abnormalities I found where these: 1) the
> server was requesting a large amount of PAGE and KEY locks (meaning an
> index lock was requested. Checking the locks for that SPID confirmed
> this) 2) At one time during testing I saw that the execution plan
> called for an estimated 1 billion rows to be returned. Amazing since
> the underlying table only have 400,000 records. Now, the theory is
> this: Since I'm using a partitioned view and Microsoft claims that
> they retain no histogram (statistical data from which to build an
> "intelligent" execution plan) somehow it's figuring this 1 billion
> estimated row count again and is entering some kind of horrendous
> loop. Any thoughts?
>|||Look in BOL for update statistics
TheSQLGuru
President
Indicium Resources, Inc.
"tmorris" <TheRealPawn@.gmail.com> wrote in message
news:1177340474.292787.166810@.q75g2000hsh.googlegroups.com...
> Has anyone seem this before. I've actually caught it once but I think
> it's happening multiple times. I have a partitioned view and
> performance is generally really good. However, every once in a great
> while we'll see a stored procedure "hang" and just run for hours.
> Doing much digging the only abnormalities I found where these: 1) the
> server was requesting a large amount of PAGE and KEY locks (meaning an
> index lock was requested. Checking the locks for that SPID confirmed
> this) 2) At one time during testing I saw that the execution plan
> called for an estimated 1 billion rows to be returned. Amazing since
> the underlying table only have 400,000 records. Now, the theory is
> this: Since I'm using a partitioned view and Microsoft claims that
> they retain no histogram (statistical data from which to build an
> "intelligent" execution plan) somehow it's figuring this 1 billion
> estimated row count again and is entering some kind of horrendous
> loop. Any thoughts?
>|||DBCC dbreindex is preformed on all tables once a week as a form of
matanance.
We've concided doing this again midweek or one of the other processes
that update statistics on columns and indexes in SQL 2000. However, in
the test enviroment it doesn't seem to have any impact one way or
another. Like I, and microsoft said, partitioned views don't contain
any statistical history from which to base an exection plan. Thanks
for the suggestion though. I'm looking more of someone to confirm my
theory or say it's complete bunk.
Now, the theory is this: Since I'm using a partitioned view and
Microsoft claims that they retain no histogram (statistical data from
which to build an "intelligent" execution plan) somehow it's figuring
this 1 billion estimated row count again and is entering some kind of
horrendous loop. Any thoughts?
On Apr 23, 11:26 am, Hurme <michael.ge...@.thomson.com> wrote:[vbcol=seagreen]
> Have you checked the fragentation of the indexes on the table(s) that the
sp
> uses?
> dbcc showcontig ('table_name').
> If the scan desity is low, you might want to do a defrag.
> dbcc indexdefrag ('database_name','table_name','index_nam
e')
> or in 2005
> alter index <index_name> | ALL
> rebuild with online = ON
> on <table_name>
> Sometimes rebuilding is better because is certain cases, indexdefrag doesn
't
> make the all of index pages contiguous.
> --
> MG
> "tmorris" wrote:|||We Run DBCC ReIndex on all tables as a weekly maintenance job. And we
looked as doing update statistics 25% midweek but it seemed to have no
effect in our test environment
On Apr 23, 12:42 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> Look in BOL for update statistics
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "tmorris" <TheRealP...@.gmail.com> wrote in message
> news:1177340474.292787.166810@.q75g2000hsh.googlegroups.com...
>

Query Plan in SQL Server 2005 SP2

Hello,

The query included at the end of this post seems to use the 'wrong' index when executing and takes 2+ minutes to run. When I provide an index hint, it runs in under 1 second. This happens in both the Production and Development environments; both servers run SQL Server 2005 SP2.

When I run both the queries together in Query Analyzer (or SSMS), the Query Cost (relative to the batch) value for the query with the index hint = 83.24%.

Profiler stats for Original Query:

Duration = 130484 ms

CPU = 111141 ms

Reads = 85470

Profiler stats for Index Hint Query:

Duration = 64 ms

CPU = 687 ms

Reads = 5558

Statistics are updated every night (w/Fullscan)

Indexes are rebuilt/defragmented daily based on fragmentation levels.

What am I missing here/how do I fix this performance issue?

Doesn't Profiler take into account the "Subtree Cost" for the Index Seek (which Query Analyzer & SSMS consider to be so expensive)? What Events/Columns do I need to include in the Profiler trace to see this statistic when the query is executing?

Thanks much,

Smitha

QUERY:

select min(AccessLevel)
from Groups-- WITH(INDEX = ByClassID)
where name = 'Student Leader'
and classid in (2067,2063,2069,2070,2079,2072,2073,2074,2075,2076,2077,2073,2079,2030,2039,2032,201,2034,2035,2036,2037,201,2039,2090,
202,2092,2093,2094,2095,2096,2097,2093,202,24,2909,2902,2903,2904,2905,25,2907,2903,2909,220,229,222,223,224,225,226,227,223,229,2920,2929,2922,2923,2924,22,2926,2927,2923,2929,2930,26,2932,291,2934,2935,28,2937,291,
26,2940,2949,211,2943,2944,2945,2946,2947,2943,2949,27,2959,2952,2953,2954,2955,2956,2957,2953,2959,2960,2969,2962,2963,2964,2965,2966,2967,2963,2969,2970,2979,2972,2973,2974,2975,2976,2977,2973,2979,2930,26,2932,291,2934,2935,28,
2937,291,26,220,229,222,223,224,225,226,227,223,229,2200,2209,2202,2203,2204,2205,34736,34739,34749,34742,34743,34744,34745,34746,34747,34743,34749,3471,34759,34392,34393,34396,34397,34962,34937,3491,346,3420,3429,3423,3424,35360,35695,35696,35709,
1056,1057,1223,4107,1256,1257,1259,1269,1262,1263,1265,1267,1263,1269,1270,1272,1273,1274,1275,1276,1277,1273,1279,1230,1239,1232,121,1234,1235,1236,1237,121,1290,122,1292,1293,1294,1295,1296,1293,122,1300,1309,1302,1303,1304,1305,1306,
1307,1494,1459,1469,1462,1466,1467,1469,1470,1472,1474,1475,1477,1432,141,1435,1436,1437,1439,1494,1496,1497,142,113,115,117,113,119,1590,152,1592,1593,1594,1595,1596,1593,152,1520,1529,1522,1523,1524,1525,1527,1539,151,1534,1536,
151,1540,1543,1544,1545,1547,1572,1573,1574,1576,1577,1573,1579,1539,53969,64763,67735,67736,63967,1529,1530,1532,151,1534,1535,1536,1537,151,1539,1540,1549,1542,1543,1545,1546,1547,1543,1549,151,1559,1553,1555,1557,1553,1559,1560,1569,1562,
1563,1564,1565,1566,1567,1563,1569,1570,1579,1572,1573,1574,1575,1576,1577,1573,1579,1530,1539,1532,151,1534,1535,1536,152,1609,1602,1603,1604,1605,1606,1607,1603,1609,1690,162,1692,1693,1694,1695,1696,1697,1693,162,1629,1622,1635,1637,
1640,1642,1646,1649,1652,1657,1669,1664,1667,1679,1634,1637,1693,1703,1706,172,1795,1793,1722,1726,1764,1392,1393,1394,1395,1396,132,1320,1329,1322,1323,3629,3622,3623,3624,3625,3626,3627,3623,3629,36200,36209,36202,36203,36204,36205,36206,36207,
36203,36209,36290,37093,37094,37095,37096,37097,37093,3702,37020,37029,37022,37023,37024,37025,37026,37027,37023,37029,37030,37039,37032,3701,37034,37035,37036,37037,3701,37039,37040,37049,37042,37043,37044,37045,37046,37047,37043,37049,3701,37059,37052,37053,37054,37055,37056,37057,
37053,37059,37060,37069,37062,37063,37064,37065,37066,37067,37063,37069,37070,37079,37072,37073,37074,37075,37076,37077,37073,37079,37030,37039,37032,3701,37034,37035,37036,37037,3701,37039,37090,3702,37092,37093,37094,37095,37096,37097,37093,3702,374,37909,37902,37903,37904,37905,
375,37907,37903,37909,3720,3729,3722,3723,3724,37320,37329,37322,37323,37324,37325,37326,37327,37329,3710,3719,3712,3713,3714,3715,3716,3717,3713,37340,37349,37342,37343,37344,37345,37346,37347,37343,37349,3731,37359,37352,37353,37354,37356,37357,37353,37359,37360,37369,
37362,37363,37364,37365,37366,37367,37363,37369,3715,3716,3717,3713,3719,37340,37349,37342,37343,37344,37345,37346,37347,37343,37349,3731,37359,37352,37353,37354,37355,37356,37357,37353,37359,37360,37369,37362,37363,37364,37365,37366,37367,37363,37369,37370,37379,37372,37373,37374,
37375,37376,37377,37373,37379,3710,3719,3712,3713,3714,3715,3716,3717,3713,3719,37390,3732,37392,37393,37394,37395,37396,37397,37393,3732,374,37909,37902,37903,37904,37905,375,37907,37903,37909,3720,3729,3722,3723,3724,3725,3726,3727,3723,3729,37920,37929,37922,
37923,37924,372,37926,37927,37923,37929,37930,376,37932,3791,37934,37935,378,37937,3791,376,37940,37949,3711,37943,37944,37945,37946,37947,37943,37949,377,37959,37952,37953,37954,37955,37956,37957,37953,37959,37960,37969,37962,37963,37964,37965,37966,37967,37963,37969,37970,
37979,37972,37973,37974,37975,37976,37977,37973,37979,37930,376,37932,3791,37934,37935,378,37937,3791,376,3720,3729,3722,3723,3724,3725,3726,3727,3723,3729,1000,1009,1002,1003,1004,1005,1006,1007,1003,1009,1090,102,1092,1093,1094,1095,1096,1097,1093,
102,1020,1029,1022,1023,1024,1025,1026,1027,1023,1029,1030,1039,1032,101,1034,1035,1036,1037,101,1039,1040,1049,1042,1043,1044,1045,1046,1047,1043,1049,101,1059,1052,1053,1054,1055,1056,1057,1053,191,1934,1935,18,1937,191,16,1940,
1949,111,1943,1944,1945,1946,1947,1943,1949,17,1959,1952,1953,1954,1955,1956,1957,1953,1959,1960,1969,1962,1963,1964,1965,1966,1967,1963,1969,1970,1979,1972,1973,1974,1975,1976,1977,1973,1979,1930,16,1932,191,1934,1935,18,1937,191,
16,120,129,122,123,124,125,126,127,123,129,1200,1209,1202,1203,1204,1205,1206,1207,1203,1209,1290,122,1292,1293,1294,1295,1296,1297,1293,122,1220,1229,1222,1223,1224,1225,1226,1227,1223,1229,1230,1239,1232,121,1234,1235,1236,
1237,121,1239,1240,1249,1242,1243,1244,1245,1246,1247,1243,1249,121,1259,1252,1253,1254,1255,1256,1257,1253,1259,1260,1269,1262,1263,1264,1265,1266,1267,1263,1269,1270,1279,1272,1273,1274,1275,1276,1277,1273,1279,1230,1239,1232,121,1234,
1235,1236,1237,121,1239,1290,122,1292,1293,1294,1295,1296,1297,1293,122,1300,1309,1302,1303,1304,1305,1306,1307,1303,1309,1390,132,1392,1393,1394,1395,1396,1397,1393,132,1320,1329,1322,1323,1324,1325,1326,1327,1323,1329,110,119,112,
113,114,115,116,117,113,119,1340,1349,1342,1343,1344,1345,1346,1347,1343,1349,131,1359,1352,1353,1354,1355,1356,1357,1353,1359,1360,1369,1362,1363,1364,1365,1366,1367,1363,1369,1370,1379,1372,1373,1374,1375,1377,1373,1379,110,119,
112,113,115,116,117,113,119,132,1392,1393,1729,1723,9441,94497,94493,9442,9410,9419,9412,9413,9414,9415,9416,9417,9413,9419,94590,9452,94593,94594,94595,94596,94597,95576,97396,97909,93057,4640,4653,4654,4656,4657,4653,4659,4660,4669,4662,
4663,4664,4665,4666,4669,4670,4679,4672,4673,4675,4676,4677,4639,4632,461,4634,4635,4636,4637,4639,5290,522,5294,5295,5296,5370,90742,20953,20959,2239,2234,222,2292,2293,2294,2296,2297,222,29209,29204,29205,29206,
29207,29203,29209,29292,29294,29295,29297,29223,29225,29226,29223,29229,29230,29239,2921,29234,23903,23909,2320,24393,2521,25252,25253,25256,25253,25269,25262,25264,25266,25267,25269,25270,25279,25272,25273,25274,25276,25277,25273,25230,25239,25232,
25235,25237,2521,25239,25290,25292,25293,25294,2790,2793,272,2729,2749,2745,2746,271,2759,2753,2755,2753,2759,2760,2762,2764,2766,2769,2770,2779,2772,2777,93029,8747,8743,8749,871,8759,8752,8755,8757,8769,8764,8765,
8766,8769,8770,8779,8773,8774,8775,8776,8777,8773,8779,8730,8739,8732,871,8734,8739,8790,872,8792,8795,8797,8793,872,8300,8309,8303,8304,8306,8307,8309,8394,8396,8397,832,8322,8326,8323,810,819,812,813,
814,937952,6045,6097,93203,93206,619,617,632,6395,6400,6403,6406,6490,6497,6422,6425,6423,6432,6434,6435,6437,6457,6453,6459,6460,6463,6464,6465,6466,6469,6470,6472,6473,6475,6477,6473,6430,6432,6434,6435,6437,
641,6439,6490,642,6492,6493,6494,6495,6497,6493,642,9370,9379,9372,9373,9374,9375,9376,9377,9373,9379,6590,6592,6594,6593,6520,6523,6524,6527,6523,6529,6539,651,6534,6535,6536,6539,6540,6544,6545,6547,6549,
6559,6552,6553,6555,6556,6557,6553,6560,6569,6564,6565,6567,6563,6570,6579,6572,6573,6574,6575,6577,6573,6530,651,6534,651,6590,652,6592,6594,6596,6597,6593,652,6609,6632,661,6634,6635,6636,6637,661,6647,
6643,6659,6652,6653,6654,6655,6656,6657,6653,6659,6669,6663,6664,6665,6666,6667,6663,6670,6672,6673,6675,6676,6677,6630,6632,661,6636,661,6693,6694,6695,6696,6697,6693,6700,6702,6703,6704,11439,11440,11449,11442,
11443,11444,11669,11679,11676,11677,1161,11635,11637,11639,1162,11697,11693,11709,11709,11790,11723,11727,11732,11735,11737,11749,11744,11743,1171,11300,11306,11307,11309,1132,11977,94170,94179,94172,945454,945934,946464,946465,946466,94123,94123,94139,
94134,94135,94143,9411,94153,94155,94157,7239,7232,721,7692,7694,7696,7697,7693,7703,7704,7705,7329,7324,7326,714,716,719,7359,7352,7359,7363,7367,7379,719,712,713,714,715,716,717,713,719,7390,732,7392,
7393,7394,7395,7396,7397,7393,732,74,7909,7902,7903,7929,7924,7923,7937,7947,7959,7953,7955,7957,7959,7962,7963,7964,7966,7967,7963,7969,7932,791,7935,791,722,723,9549,9543,952600,952629,952627,952629,952657,952653,
952662,952690,952697,952797,95272,952720,952724,952729,954597,954964,955669,955790,95572,955792,95629,957069)

INDEX Definition:

CREATE INDEX ByClassID ON Groups(ClassID) INCLUDE(Name,AccessLevel)

You might want to look up some of Tom Phillips replies to a number of previous questions about lengthy IN conditions.

I'm afraid I didn't get good results. I found these two previous posts, but you have SP2 installed and I'm afraid that in this case I don't have much help to offer; sorry. :-(

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1525484&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=990564&SiteID=1

|||

Thanks Kent- I just read the posts...it's just that there is a better/faster way to execute the query but the optimzer doesn't seem to think so. I don't want to second guess the optimizer and add query hints everywhere - but 2 minutes vs under 1 second is a huge problem when a user is waiting for a screen to show... any other ideas/suggestions will be very welcome.

Thanks again.

|||

Unfortunately, we do not do a good job of optimizing queries that contain large number of value in the IN list. Part of the problem is the high compilation costs as the number of values increases. This affects plan choices also. If you provide index hint then lot of the logic is short circuited. In this case, it just so happens that using the index results in a good plan. It is hard to say if that will be case for all possible IN list values.

Next, I am not sure how you are sending the query to SQL Server. It is very inefficient to send queries like this. Is this being generated from an application? How is the user able to select this many values? There are also multiple problems apart from the performance issue. For each such unique query, there will be a unique plan because of the difference in the IN list values. This affects your plan cache size too. And it looks like the size of your SQL statement can be very large so it affects performance and also inhibits different connections from sharing the same plan.

You should consider the following options:

1. Create a temporary table and optionally index the value column

2. Use BULK INSERT or BCP or BulkCopy API to dump the IN list values into temporary table

3. Write the query to use the temporary table. The query plan can now be shared across connections and independent of the values in the temporary table

You can also create a SP that takes the IN list values as a blob (string of bytes) and parse it using SUBSTRING/numbers table. The link below describes lot of these techniques.

http://www.sommarskog.se/arrays-in-sql.html

So can you describe your query generation process more?

|||

The user selects the list of IDs. The application then creates a dynamic SQL script with these values, and passes it to the database. A lot of such t-sql has been moved from application code into stored procedures/functions but there's still some that will need to be moved eventually, and some that cannot be moved because of the nature of the functionality. For these I've been trying to replace with the sp_executesql construct to improve plan reuse.

In the meantime - this is great information. I just tested by inserting the data into a temp table and running the SELECT. Ran in under 1 second and used the best index for the query. If I can't get the code moved to the DB, I will ask that the data be placed in a table before using in the IN construct.

I made sure I planned my indexing strategy, implemented it, maintain indexes, etc but didn't quite understand why they weren't being used. This is probably the culprit for several performance issues that did not seem to make sense before this.

Thanks very much,

Smitha

|||What you describe is exactly my situation. I have NO, REPEAT NO, control over the SQL query which gets generated by the report writter. The user's can select any number of values for the IN clause. Some work, some don't.

To MS, I would point out the EXACT same query runs in 10 seconds on SQL 2000 SP4 and I cancelled the query under SQL 2005 SP1 after 1 hour. So it is definitally a problem with SQL 2005.

This needs to be fixed. I have no work around, except the have the user select less items and run the report multiple times.

|||

Thanks for responding, Tom; I know that you have had a lot of heartburn over this issue.

Bless you, Uma.

|||Thanks Kent.

I should add, this is a weekly occurance in my company since changing to SQL 2005. I have been testing SP2 and do not see it fixed in SP2 either.

This needs to be fixed, and the standard MS answer of "don't do that" doesn't fix it.

|||

I am following up internally on some of the bugs filed for this particular issue. I will find out if we are going to do anything about it in SQL Server 2008.

|||Thank you

Query Plan in SQL Server 2005 SP2

Hello,

When I run both the queries together in Query Analyzer (or SSMS), the Query Cost (relative to the batch) value for the query with the index hint = 83.24%.

Profiler stats for Original Query:

Duration = 130484 ms

CPU = 111141 ms

Reads = 85470

Profiler stats for Index Hint Query:

Duration = 64 ms

CPU = 687 ms

Reads = 5558

Statistics are updated every night (w/Fullscan)

Indexes are rebuilt/defragmented daily based on fragmentation levels.

What am I missing here/how do I fix this performance issue?

Thanks much,

Smitha

QUERY:

INDEX Definition:

CREATE INDEX ByClassID ON Groups(ClassID) INCLUDE(Name,AccessLevel)

You might want to look up some of Tom Phillips replies to a number of previous questions about lengthy IN conditions.

I'm afraid I didn't get good results. I found these two previous posts, but you have SP2 installed and I'm afraid that in this case I don't have much help to offer; sorry. :-(

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1525484&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=990564&SiteID=1

|||

Thanks again.

|||

You should consider the following options:

1. Create a temporary table and optionally index the value column

2. Use BULK INSERT or BCP or BulkCopy API to dump the IN list values into temporary table

3. Write the query to use the temporary table. The query plan can now be shared across connections and independent of the values in the temporary table

You can also create a SP that takes the IN list values as a blob (string of bytes) and parse it using SUBSTRING/numbers table. The link below describes lot of these techniques.

http://www.sommarskog.se/arrays-in-sql.html

So can you describe your query generation process more?

|||

Thanks very much,

Smitha

To MS, I would point out the EXACT same query runs in 10 seconds on SQL 2000 SP4 and I cancelled the query under SQL 2005 SP1 after 1 hour. So it is definitally a problem with SQL 2005.

This needs to be fixed. I have no work around, except the have the user select less items and run the report multiple times.

|||

Thanks for responding, Tom; I know that you have had a lot of heartburn over this issue.

Bless you, Uma.

|||Thanks Kent.

I should add, this is a weekly occurance in my company since changing to SQL 2005. I have been testing SP2 and do not see it fixed in SP2 either.

This needs to be fixed, and the standard MS answer of "don't do that" doesn't fix it.

|||

I am following up internally on some of the bugs filed for this particular issue. I will find out if we are going to do anything about it in SQL Server 2008.

|||Thank you

Query Plan Guides don't seem to work

We have a couple of ad hoc queries in our app that we need to force parameterization. There queries can't be put in sp's because they come from a 3rd party app. The goal is to reduce the amount of cache being accumulated. We are on sql server 2005. The queries in question look like this:

select * from TABLE1 where SEQUENCE_NUMBER = '<some literal>'

When i query syscacheobjects i see thousands of compiled plans for that single query with different literal values in the WHERE clause. I want to force parameterization and make it reuse one plan. Seems to best way to do this is to turn FORCED PARAMETERIZATION on for the db or to create plan guides. Both don't seem to be working.

I turned FORCED PARAMETERIZATION on for the db, then cleared proc cache (DBCC FREEPROCCACHE). I query syscacheobjects and it's empty. I run a test script with 10 queries similiar to the one above, passing a different literal into the where clause. I would expect there to be only one compiled plan for the paramitized version of the query but again there are 10 compiled plans for that query.

I then turned SIMPLE PARAMETERIZATION on for the db and created a plan guide for the query:

DECLARE @.stmt nvarchar(max);
DECLARE @.params nvarchar(max);
EXEC sp_get_query_template
N'select * from TABLE1 where SEQUENCE_NUMBER = ''%''',
@.stmt OUTPUT,
@.params OUTPUT;
EXEC sp_create_plan_guide
N'Templat1',
@.stmt,
N'TEMPLATE',
NULL,
@.params,
N'OPTION(PARAMETERIZATION FORCED)';

I again clear the cache and syscacheobjects is empty. I run my test script. This time i can a row for the plan guide BUT there is still 10 rows for the queries in my test script. I expected to just see the row for the plan guide, indicating that it's using that compiled plan for all the queries but this isn't the case.

Has anyone used query plan guides? Is my testing correct, should i expect LESS rows to accumulate in syscacheobjects? I can query sys.plan_guides and see that my plan guide is created and enabled but from my tests it doesn't seem like it's being used when i run my tests. Any advise on how to get this going?

thanks,
Dave

Did you see any entry where cacheobjtype = 'Compiled Plan' and objtype = 'Prepared' and sql = '(@.0 varchar(8000) )select * from TABLE1 where SEQUENCE_NUMBER = @.0'?

If yes, then it is working. Check the number for column [usecounts], it should be 10.

If you are not qualifying the table with the schema / owner, then the plan generated will not be share among multiple users. You can check column [uid] and see if it is different from -2.

AMB

|||thanks for the response hunchback! I AM seeing an entry in syscacheobjects where cacheobjtype = 'Compiled Plan' and objtype = 'Prepared' and sql = '(@.0 varchar(8000) )select * from TABLE1 where SEQUENCE_NUMBER = @.0'

Below are the results of:
select cacheobjtype, objtype, usecounts, sql from syscacheobjects
where sql like '%TABLE1%' order by sql

cacheobjtype objtype usecount sql
Compiled Plan Prepared 10 (@.0 varchar(8000))select * from dbo.TABLE1 where SEQUENCE_NUMBER = @.0
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '1'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '10'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '2'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '3'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '4'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '5'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '6'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '7'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '8'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '9'

Any idea why is it compiling a plan for each query even though it's using the Plan Guide? When i run Profiler and check out the ExplainPlan XML i don't see any references to the TemplatePlan which was also making me think that it wasn't using the plan guide.
|||

What about the [usecounts] associated to the ('Compiled Plan', 'Prepared')?

SQL Server has to compile the Adhoc query anyway, in order to get the query tree and be able to separate the constant values that will be pass to the parameters. Based on the resources available and cost of compilation, SQL Server could save or not those compiled plans.

Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

Plan Cache Concepts Explained

http://blogs.msdn.com/sqlprogrammability/archive/2007/01/08/plan-cache-concepts-explained.aspx

AMB

|||I'm running into the same behavior as tenatiousd is reporting.

I create the template plan guide and setup tests to watch SQL Server behavior (syscacheobjects). When I execute a SQL statement that matches the plan guide, it seems to be "referencing" the Prepared Plan (refcount goes up), but creates an Ad Hoc compiled plan and (re)uses that instead. Everything else that matters for plan resuse is the same (user id, execution context, qualified table names, etc.), but it doesn't reuse the Prepared Plan defined from the template plan guide. If I change the parameters, it references the Prepared Plan, but compiles a new Ad Hoc plan and continues to reuse the ad hoc plan over and over (as long as the parameters are the same).

The resulting behavior is that the Prepared Plan that matches the SQL statement is not reused and the plan cache would continue to grow with new ad hoc plans being added as the parameter values change.

Why isn't the Prepared Plan being reused? Will the Prepared Plan only be reused if there is enough pressure on the Plan Cache and it can't/won't store the ad hoc plan?
|||

Hi Nickvalo,

What about [usecounts] for the "Prepared" execution plan, is it being incremented?

AMB

|||The first time I execute a SQL statement that has a template plan guide (after issuing a DBCC freeproccache), this is what happens:

1. The usecount for the Prepared Plan goes up by one and it's refcount goes up by 2 (or 3).
2. The usecount for the ad hoc plan goes up by one (a new one gets created for each unique set of parameter values).

Then if I execute that same SQL statement (same parameter values) 5 times in a row (without clearing the plan cache), the usecount for the *ad hoc* plan goes up by 5. So, essentially it appears that the prepared plan is used somehow the first time, but so is an ad hoc plan for that same statement ... then only the ad hoc plan is used when the same parameters are used in the SQL statement. My expectation was that there would not be an ad hoc plan prepared and cached if the template plan guide matched the SQL statement and forced parameterization.
|||

Nickvalo,

I which somebody from the Microsoft SQL Server group, in charge of "plan guides", could answer this question.

My guess is that the cost of generating an execution plan for that statement is low and the resources available are high, so putting the plan in the cache can help. See if you can download a stress tool and do the test with more connections (create more strees, consume more, and reduce resources available).

Support Tools Available For Stress Testing & Performance Analysis

http://www.microsoft.com/downloads/details.aspx?familyid=5691ab53-893a-4aaf-b4a6-9a8bb9669a8b&displaylang=en

AMB

|||

Hello,

maybe this page: http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

especially the Appendix A section can help you to find out why there is no parametrisation

regards,

Peter

sql

Query Plan Guides don't seem to work

select * from TABLE1 where SEQUENCE_NUMBER = '<some literal>'

I then turned SIMPLE PARAMETERIZATION on for the db and created a plan guide for the query:

thanks,
Dave

Did you see any entry where cacheobjtype = 'Compiled Plan' and objtype = 'Prepared' and sql = '(@.0 varchar(8000) )select * from TABLE1 where SEQUENCE_NUMBER = @.0'?
If yes, then it is working. Check the number for column [usecounts], it should be 10.
If you are not qualifying the table with the schema / owner, then the plan generated will not be share among multiple users. You can check column [uid] and see if it is different from -2.

AMB

What about the [usecounts] associated to the ('Compiled Plan', 'Prepared')?

Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

Plan Cache Concepts Explained

http://blogs.msdn.com/sqlprogrammability/archive/2007/01/08/plan-cache-concepts-explained.aspx

AMB

|||I'm running into the same behavior as tenatiousd is reporting.

Why isn't the Prepared Plan being reused? Will the Prepared Plan only be reused if there is enough pressure on the Plan Cache and it can't/won't store the ad hoc plan?
|||

Hi Nickvalo,

What about [usecounts] for the "Prepared" execution plan, is it being incremented?

AMB

|||The first time I execute a SQL statement that has a template plan guide (after issuing a DBCC freeproccache), this is what happens:

Nickvalo,

I which somebody from the Microsoft SQL Server group, in charge of "plan guides", could answer this question.

Support Tools Available For Stress Testing & Performance Analysis

http://www.microsoft.com/downloads/details.aspx?familyid=5691ab53-893a-4aaf-b4a6-9a8bb9669a8b&displaylang=en

AMB

|||

Hello,

maybe this page: http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

especially the Appendix A section can help you to find out why there is no parametrisation

regards,

Peter

Query plan different in different instances

Hi there,
I am not sure if this post is relevant to this forum, but I am
desperate to find an answer for my problem.
Here goes my problem, I have a query
SELECT
BPE.[EmpLastNm]+'
'+ISNULL(BPE.[EmpFirstNm],'')+'('+ISNULL(BPE.[EmpFamiliarNm],'')+')' AS
[ContactName]
,[EmpStatusCd]
,BPE.BusnPartEmpId
FROM
[dbo].[tblBusnPartEmp] AS BPE
inner JOIN [dbo].[tblDivBranchBusnPartEmp] AS DBBPE ON
BPE.[BusnPartEmpId]=DBBPE.[BusnPartEmpId]
inner JOIN [dbo].[tblDivBranch] AS DB ON
DB.[DivbranchId] = DBBPE.[DivbranchId]
WHERE
DB.[BusnPartId] = 2068--@.BusnPartId
AND DB.[DivNo] = '000'
and this query has different query plan in dev and test env.
I checked on statistics of this table, fragmentation of the table and
all look similar with no difference.
I am totally lost on what to be done next.
Query Plan in Dev. Env:
StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument
DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize
TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
-----------
-- -- -- --
-------
-- --
-- -- --
-- -- --
SELECT
BPE.[EmpLastNm]+'
'+ISNULL(BPE.[EmpFirstNm],'')+'('+ISNULL(BPE.[EmpFamiliarNm],'')+')' AS
[ContactName]
-- ,[EmpStatusCd]
--,[dbo]. 33 1 0 NULL NULL 1 NULL 43156.262 NULL NULL NULL 92.666832
NULL NULL SELECT 0 NULL
|--Compute Scalar(DEFINE

[Expr1003]=[BPE].[EmpLastNm]+'
'+isnull([BPE].[EmpFirstNm], '')+'('+isnull([BPE].[EmpFamiliarNm],
'')+')')) 33 2 1 Compute Scalar Compute Scalar
DEFINE

[Expr1003]=[BPE].[EmpLastNm]+' '+isnull([BPE].[EmpFirstNm],
'')+'('+isnull([BPE].[EmpFamiliarNm], '')+')')
[Expr1003]=[BPE].[EmpLastNm]+' '+isnull([BPE].[EmpFirstNm],
'')+'('+isnull([BPE].[EmpFamiliarNm], '')+')' 43156.262 0.0
4.3156263E-3 168 92.666832 [BPE].[BusnPartEmpId], [Expr1003] NULL
PLAN_ROW 0 1.0
|--Hash Match(Inner Join,
HASH

[DBBPE].[BusnPartEmpId])=([BPE].[BusnPartEmpId])) 33 3 2 Hash
Match Inner Join HASH

[DBBPE].[BusnPartEmpId])=([BPE].[BusnPartEmpId])
NULL 43156.262 0.0 5.5712514 97 92.662514 [BPE].[EmpFamiliarNm],
[BPE].[EmpFirstNm], [BPE].[EmpLastNm], [BPE].[BusnPartEmpId] NULL
PLAN_ROW 0 1.0
|--Nested Loops(Inner Join, OUTER REFERENCES

[DB].[DivBranchId]) WITH
PREFETCH) 33 4 3 Nested Loops Inner Join OUTER
REFERENCES

[DB].[DivBranchId]) WITH PREFETCH NULL 43156.262 0.0
0.18039317 1247 1.6252334 [DBBPE].[BusnPartEmpId] NULL PLAN_ROW 0 1.0
| |--Filter(WHERE

[DB].[DivNo]='000')) 33 6 4 Filter Filter
WHERE

[DB].[DivNo]='000') NULL 141.84932 0.0 1.5263824E-4 1233
0.97286189 [DB].[DivBranchId] NULL PLAN_ROW 0 1.0
| | |--Bookmark Lookup(BOOKMARK

[Bmk1002]),
OBJECT

[EDGE].[dbo].[tblDivBranch] AS [DB]) WITH PREFETCH) 33 7 6
Bookmark Lookup Bookmark Lookup BOOKMARK

[Bmk1002]),
OBJECT

[EDGE].[dbo].[tblDivBranch] AS [DB]) WITH PREFETCH
[DB].[DivBranchId], [DB].[DivNo] 317.99634 0.96872675 3.4979597E-4 1233
0.97270924 [DB].[DivBranchId], [DB].[DivNo] NULL PLAN_ROW 0 1.0
| | |--Index Seek(OBJECT

[EDGE].[dbo].[tblDivBranch].[IDXDivBranch1]
AS [DB]), SEEK

[DB].[BusnPartId]=2068) ORDERED FORWARD) 33 9 7 Index
Seek Index Seek OBJECT

[EDGE].[dbo].[tblDivBranch].[IDXDivBranch1] AS
[DB]), SEEK

[DB].[BusnPartId]=2068) ORDERED FORWARD [Bmk1002]
317.99634 3.2034251E-3 4.2924995E-4 19 3.6326749E-3 [Bmk1002] NULL
PLAN_ROW 0 1.0
| |--Index
Seek(OBJECT

[EDGE].[dbo].[tblDivBranchBusnPartEmp].[XPKDivBranchBusnPartEmp]
AS [DBBPE]), SEEK

[DBBPE].[DivBranchId]=[DB].[DivBranchId]) ORDERED
FORWARD) 33 13 4 Index Seek Index Seek
OBJECT

[EDGE].[dbo].[tblDivBranchBusnPartEmp].[XPKDivBranchBusnPartEmp]
AS [DBBPE]), SEEK

[DBBPE].[DivBranchId]=[DB].[DivBranchId]) ORDERED
FORWARD [DBBPE].[BusnPartEmpId] 304.24017 3.2034251E-3 4.1407693E-4 23
0.47197837 [DBBPE].[BusnPartEmpId] NULL PLAN_ROW 0 141.84932
|--Table Scan(OBJECT

[EDGE].[dbo].[tblBusnPartEmp] AS [BPE])) 33 14 3
Table Scan Table Scan OBJECT

[EDGE].[dbo].[tblBusnPartEmp] AS [BPE])
[BPE].[EmpFamiliarNm], [BPE].[EmpFirstNm], [BPE].[EmpLastNm],
[BPE].[BusnPartEmpId] 1114682.0 84.239799 1.2262287 3282 85.466026
[BPE].[EmpFamiliarNm], [BPE].[EmpFirstNm], [BPE].[EmpLastNm],
[BPE].[BusnPartEmpId] NULL PLAN_ROW 0 1.0
Query plan in test env.:
StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument
DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize
TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
-----------
-- -- -- --
-------
-- --
-- -- --
-- --
-- --
SELECT
BPE.[EmpLastNm]+'
'+ISNULL(BPE.[EmpFirstNm],'')+'('+ISNULL(BPE.[EmpFamiliarNm],'')+')' AS
[ContactName]
,[EmpStatusCd]
,BPE.BusnPar 17 1 0 NULL NULL 1 NULL 4826.1626 NULL NULL NULL 10.43982
NULL NULL SELECT 0 NULL
|--Compute Scalar(DEFINE

[Expr1003]=[BPE].[EmpLastNm]+'
'+isnull([BPE].[EmpFirstNm], '')+'('+isnull([BPE].[EmpFamiliarNm],
'')+')')) 17 2 1 Compute Scalar Compute Scalar
DEFINE

[Expr1003]=[BPE].[EmpLastNm]+' '+isnull([BPE].[EmpFirstNm],
'')+'('+isnull([BPE].[EmpFamiliarNm], '')+')')
[Expr1003]=[BPE].[EmpLastNm]+' '+isnull([BPE].[EmpFirstNm],
'')+'('+isnull([BPE].[EmpFamiliarNm], '')+')' 4826.1626 0.0
4.8261625E-4 172 10.43982 [BPE].[EmpStatusCd], [BPE].[BusnPartEmpId],
[Expr1003] NULL PLAN_ROW 0 1.0
|--Bookmark Lookup(BOOKMARK

[Bmk1000]),
OBJECT

[EDGE].[dbo].[tblBusnPartEmp] AS [BPE]) WITH PREFETCH) 17 3 2
Bookmark Lookup Bookmark Lookup BOOKMARK

[Bmk1000]),
OBJECT

[EDGE].[dbo].[tblBusnPartEmp] AS [BPE]) WITH PREFETCH
[BPE].[EmpFamiliarNm], [BPE].[EmpFirstNm], [BPE].[EmpLastNm],
[BPE].[EmpStatusCd], [BPE].[BusnPartEmpId] 4826.1626 2.2874451
5.308779E-3 270 10.439338 [BPE].[EmpFamiliarNm], [BPE].[EmpFirstNm],
[BPE].[EmpLastNm], [BPE].[EmpStatusCd], [BPE].[BusnPartEmpId] NULL
PLAN_ROW 0 1.0
|--Parallelism(Gather Streams) 17 5 3 Parallelism Gather Streams NULL
NULL 4826.1626 0.0 3.8873836E-2 23 8.1465836 [Bmk1000] NULL PLAN_ROW -1
1.0
|--Nested Loops(Inner Join, OUTER REFERENCES

[DBBPE].[BusnPartEmpId])
WITH PREFETCH) 17 6 5 Nested Loops Inner Join OUTER
REFERENCES

[DBBPE].[BusnPartEmpId]) WITH PREFETCH NULL 4826.1626 0.0
0.01008668 23 8.1077099 [Bmk1000] NULL PLAN_ROW -1 1.0
|--Sort(ORDER BY

[DBBPE].[BusnPartEmpId] ASC)) 17 8 6 Sort Sort ORDER
BY

[DBBPE].[BusnPartEmpId] ASC) NULL 4826.1626 5.6306305E-3
4.6113774E-2 11 1.034305 [DBBPE].[BusnPartEmpId] NULL PLAN_ROW -1 1.0
| |--Nested Loops(Inner Join, OUTER REFERENCES

[DB].[DivBranchId])
WITH PREFETCH) 17 9 8 Nested Loops Inner Join OUTER
REFERENCES

[DB].[DivBranchId]) WITH PREFETCH NULL 4826.1626 0.0
0.01008668 43 0.98256058 [DBBPE].[BusnPartEmpId] NULL PLAN_ROW -1 1.0
| |--Filter(WHERE

[DB].[DivNo]='000')) 17 11 9 Filter Filter
WHERE

[DB].[DivNo]='000') NULL 15.442919 0.0 7.5600001E-5 29
0.96141553 [DB].[DivBranchId] NULL PLAN_ROW -1 1.0
| | |--Bookmark Lookup(BOOKMARK

[Bmk1002]),
OBJECT

[EDGE].[dbo].[tblDivBranch] AS [DB]) WITH PREFETCH) 17 12 11
Bookmark Lookup Bookmark Lookup BOOKMARK

[Bmk1002]),
OBJECT

[EDGE].[dbo].[tblDivBranch] AS [DB]) WITH PREFETCH
[DB].[DivBranchId], [DB].[DivNo] 315.0 0.95935196 1.7324999E-4 29
0.96133989 [DB].[DivBranchId], [DB].[DivNo] NO
STATS

[tblDivBranch].[DivNo]) PLAN_ROW -1 1.0
| | |--Index Seek(OBJECT

[EDGE].[dbo].[tblDivBranch].[IDXDivBranch1]
AS [DB]), SEEK

[DB].[BusnPartId]=2068) ORDERED FORWARD) 17 14 12 Index
Seek Index Seek OBJECT

[EDGE].[dbo].[tblDivBranch].[IDXDivBranch1] AS
[DB]), SEEK

[DB].[BusnPartId]=2068) ORDERED FORWARD [Bmk1002] 315.0
1.6017125E-3 2.129725E-4 19 1.8146849E-3 [Bmk1002] NULL PLAN_ROW -1 1.0
| |--Index
Seek(OBJECT

[EDGE].[dbo].[tblDivBranchBusnPartEmp].[XPKDivBranchBusnPartEmp]
AS [DBBPE]), SEEK

[DBBPE].[DivBranchId]=[DB].[DivBranchId]) ORDERED
FORWARD) 17 18 9 Index Seek Index Seek
OBJECT

[EDGE].[dbo].[tblDivBranchBusnPartEmp].[XPKDivBranchBusnPartEmp]
AS [DBBPE]), SEEK

[DBBPE].[DivBranchId]=[DB].[DivBranchId]) ORDERED
FORWARD [DBBPE].[BusnPartEmpId] 312.5162 3.2034251E-3 4.2320538E-4 23
0.01105837 [DBBPE].[BusnPartEmpId] NULL PLAN_ROW -1 15.442919
|--Index Seek(OBJECT

[EDGE].[dbo].[tblBusnPartEmp].[XPKBusnPartEmp] AS
[BPE]), SEEK

[BPE].[BusnPartEmpId]=[DBBPE].[BusnPartEmpId]) ORDERED
FORWARD) 17 19 6 Index Seek Index Seek
OBJECT

[EDGE].[dbo].[tblBusnPartEmp].[XPKBusnPartEmp] AS [BPE]),
SEEK

[BPE].[BusnPartEmpId]=[DBBPE].[BusnPartEmpId]) ORDERED FORWARD
[Bmk1000] 1.0 3.2034251E-3 7.9603E-5 19 7.0633183 [Bmk1000] NULL
PLAN_ROW -1 4826.1626
If you notice the query plan are different by a major physical
operation. Test env. query plan uses index seek where as dev
environment query plan uses a table scan. Why? I am totally confused.
I checked on indexes also, and in both env. it is the same.
For your quick reference here are the index details too
index_name index_description index_keys
------
---------
-----------
IDXBusnPartEmp1 nonclustered located on PRIMARY EmpNTLogin
IX_tblBusnPartEmp nonclustered located on PRIMARY EdgeUserTypeCd
XPKBusnPartEmp nonclustered, unique, primary key located on PRIMARY
BusnPartEmpId
Can somebody help me on this issue? The sql instances sit on different
system having same hardware, server configuration. The service packs
are also same.
Thanks in advance.
Hi
Run DBCC DROPCLEANBUFFERS and run it again
"VIKING" <msrviking@.gmail.com> wrote in message
news:1163641697.241152.88950@.i42g2000cwa.googlegro ups.com...
> Hi there,
> I am not sure if this post is relevant to this forum, but I am
> desperate to find an answer for my problem.
> Here goes my problem, I have a query
> SELECT
> BPE.[EmpLastNm]+'
> '+ISNULL(BPE.[EmpFirstNm],'')+'('+ISNULL(BPE.[EmpFamiliarNm],'')+')' AS
> [ContactName]
> ,[EmpStatusCd]
> ,BPE.BusnPartEmpId
> FROM
> [dbo].[tblBusnPartEmp] AS BPE
> inner JOIN [dbo].[tblDivBranchBusnPartEmp] AS DBBPE ON
> BPE.[BusnPartEmpId]=DBBPE.[BusnPartEmpId]
> inner JOIN [dbo].[tblDivBranch] AS DB ON
> DB.[DivbranchId] = DBBPE.[DivbranchId]
> WHERE
> DB.[BusnPartId] = 2068--@.BusnPartId
> AND DB.[DivNo] = '000'
> and this query has different query plan in dev and test env.
> I checked on statistics of this table, fragmentation of the table and
> all look similar with no difference.
> I am totally lost on what to be done next.
> Query Plan in Dev. Env:
> StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument
> DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize
> TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
> -----------
> -- -- -- --
> --
> -------
> ----
> -- --
> -- -- --
> ----
> -- -- --
> --
> SELECT
> BPE.[EmpLastNm]+'
> '+ISNULL(BPE.[EmpFirstNm],'')+'('+ISNULL(BPE.[EmpFamiliarNm],'')+')' AS
> [ContactName]
> -- ,[EmpStatusCd]
> --,[dbo]. 33 1 0 NULL NULL 1 NULL 43156.262 NULL NULL NULL 92.666832
> NULL NULL SELECT 0 NULL
> |--Compute Scalar(DEFINE

[Expr1003]=[BPE].[EmpLastNm]+'
> '+isnull([BPE].[EmpFirstNm], '')+'('+isnull([BPE].[EmpFamiliarNm],
> '')+')')) 33 2 1 Compute Scalar Compute Scalar
> DEFINE

[Expr1003]=[BPE].[EmpLastNm]+' '+isnull([BPE].[EmpFirstNm],
> '')+'('+isnull([BPE].[EmpFamiliarNm], '')+')')
> [Expr1003]=[BPE].[EmpLastNm]+' '+isnull([BPE].[EmpFirstNm],
> '')+'('+isnull([BPE].[EmpFamiliarNm], '')+')' 43156.262 0.0
> 4.3156263E-3 168 92.666832 [BPE].[BusnPartEmpId], [Expr1003] NULL
> PLAN_ROW 0 1.0
> |--Hash Match(Inner Join,
> HASH

[DBBPE].[BusnPartEmpId])=([BPE].[BusnPartEmpId])) 33 3 2 Hash
> Match Inner Join HASH

[DBBPE].[BusnPartEmpId])=([BPE].[BusnPartEmpId])
> NULL 43156.262 0.0 5.5712514 97 92.662514 [BPE].[EmpFamiliarNm],
> [BPE].[EmpFirstNm], [BPE].[EmpLastNm], [BPE].[BusnPartEmpId] NULL
> PLAN_ROW 0 1.0
> |--Nested Loops(Inner Join, OUTER REFERENCES

[DB].[DivBranchId]) WITH
> PREFETCH) 33 4 3 Nested Loops Inner Join OUTER
> REFERENCES

[DB].[DivBranchId]) WITH PREFETCH NULL 43156.262 0.0
> 0.18039317 1247 1.6252334 [DBBPE].[BusnPartEmpId] NULL PLAN_ROW 0 1.0
> | |--Filter(WHERE

[DB].[DivNo]='000')) 33 6 4 Filter Filter
> WHERE

[DB].[DivNo]='000') NULL 141.84932 0.0 1.5263824E-4 1233
> 0.97286189 [DB].[DivBranchId] NULL PLAN_ROW 0 1.0
> | | |--Bookmark Lookup(BOOKMARK

[Bmk1002]),
> OBJECT

[EDGE].[dbo].[tblDivBranch] AS [DB]) WITH PREFETCH) 33 7 6
> Bookmark Lookup Bookmark Lookup BOOKMARK

[Bmk1002]),
> OBJECT

[EDGE].[dbo].[tblDivBranch] AS [DB]) WITH PREFETCH
> [DB].[DivBranchId], [DB].[DivNo] 317.99634 0.96872675 3.4979597E-4 1233
> 0.97270924 [DB].[DivBranchId], [DB].[DivNo] NULL PLAN_ROW 0 1.0
> | | |--Index Seek(OBJECT

[EDGE].[dbo].[tblDivBranch].[IDXDivBranch1]
> AS [DB]), SEEK

[DB].[BusnPartId]=2068) ORDERED FORWARD) 33 9 7 Index
> Seek Index Seek OBJECT

[EDGE].[dbo].[tblDivBranch].[IDXDivBranch1] AS
> [DB]), SEEK

[DB].[BusnPartId]=2068) ORDERED FORWARD [Bmk1002]
> 317.99634 3.2034251E-3 4.2924995E-4 19 3.6326749E-3 [Bmk1002] NULL
> PLAN_ROW 0 1.0
> | |--Index
> Seek(OBJECT

[EDGE].[dbo].[tblDivBranchBusnPartEmp].[XPKDivBranchBusnPartEmp]
> AS [DBBPE]), SEEK

[DBBPE].[DivBranchId]=[DB].[DivBranchId]) ORDERED
> FORWARD) 33 13 4 Index Seek Index Seek
> OBJECT

[EDGE].[dbo].[tblDivBranchBusnPartEmp].[XPKDivBranchBusnPartEmp]
> AS [DBBPE]), SEEK

[DBBPE].[DivBranchId]=[DB].[DivBranchId]) ORDERED
> FORWARD [DBBPE].[BusnPartEmpId] 304.24017 3.2034251E-3 4.1407693E-4 23
> 0.47197837 [DBBPE].[BusnPartEmpId] NULL PLAN_ROW 0 141.84932
> |--Table Scan(OBJECT

[EDGE].[dbo].[tblBusnPartEmp] AS [BPE])) 33 14 3
> Table Scan Table Scan OBJECT

[EDGE].[dbo].[tblBusnPartEmp] AS [BPE])
> [BPE].[EmpFamiliarNm], [BPE].[EmpFirstNm], [BPE].[EmpLastNm],
> [BPE].[BusnPartEmpId] 1114682.0 84.239799 1.2262287 3282 85.466026
> [BPE].[EmpFamiliarNm], [BPE].[EmpFirstNm], [BPE].[EmpLastNm],
> [BPE].[BusnPartEmpId] NULL PLAN_ROW 0 1.0
>
> Query plan in test env.:
> StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument
> DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize
> TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
> -----------
> -- -- -- --
> --
> -------
> ----
> -- --
> -- -- --
> ----
> -- --
> -- --
> SELECT
> BPE.[EmpLastNm]+'
> '+ISNULL(BPE.[EmpFirstNm],'')+'('+ISNULL(BPE.[EmpFamiliarNm],'')+')' AS
> [ContactName]
> ,[EmpStatusCd]
> ,BPE.BusnPar 17 1 0 NULL NULL 1 NULL 4826.1626 NULL NULL NULL 10.43982
> NULL NULL SELECT 0 NULL
> |--Compute Scalar(DEFINE

[Expr1003]=[BPE].[EmpLastNm]+'
> '+isnull([BPE].[EmpFirstNm], '')+'('+isnull([BPE].[EmpFamiliarNm],
> '')+')')) 17 2 1 Compute Scalar Compute Scalar
> DEFINE

[Expr1003]=[BPE].[EmpLastNm]+' '+isnull([BPE].[EmpFirstNm],
> '')+'('+isnull([BPE].[EmpFamiliarNm], '')+')')
> [Expr1003]=[BPE].[EmpLastNm]+' '+isnull([BPE].[EmpFirstNm],
> '')+'('+isnull([BPE].[EmpFamiliarNm], '')+')' 4826.1626 0.0
> 4.8261625E-4 172 10.43982 [BPE].[EmpStatusCd], [BPE].[BusnPartEmpId],
> [Expr1003] NULL PLAN_ROW 0 1.0
> |--Bookmark Lookup(BOOKMARK

[Bmk1000]),
> OBJECT

[EDGE].[dbo].[tblBusnPartEmp] AS [BPE]) WITH PREFETCH) 17 3 2
> Bookmark Lookup Bookmark Lookup BOOKMARK

[Bmk1000]),
> OBJECT

[EDGE].[dbo].[tblBusnPartEmp] AS [BPE]) WITH PREFETCH
> [BPE].[EmpFamiliarNm], [BPE].[EmpFirstNm], [BPE].[EmpLastNm],
> [BPE].[EmpStatusCd], [BPE].[BusnPartEmpId] 4826.1626 2.2874451
> 5.308779E-3 270 10.439338 [BPE].[EmpFamiliarNm], [BPE].[EmpFirstNm],
> [BPE].[EmpLastNm], [BPE].[EmpStatusCd], [BPE].[BusnPartEmpId] NULL
> PLAN_ROW 0 1.0
> |--Parallelism(Gather Streams) 17 5 3 Parallelism Gather Streams NULL
> NULL 4826.1626 0.0 3.8873836E-2 23 8.1465836 [Bmk1000] NULL PLAN_ROW -1
> 1.0
> |--Nested Loops(Inner Join, OUTER REFERENCES

[DBBPE].[BusnPartEmpId])
> WITH PREFETCH) 17 6 5 Nested Loops Inner Join OUTER
> REFERENCES

[DBBPE].[BusnPartEmpId]) WITH PREFETCH NULL 4826.1626 0.0
> 0.01008668 23 8.1077099 [Bmk1000] NULL PLAN_ROW -1 1.0
> |--Sort(ORDER BY

[DBBPE].[BusnPartEmpId] ASC)) 17 8 6 Sort Sort ORDER
> BY

[DBBPE].[BusnPartEmpId] ASC) NULL 4826.1626 5.6306305E-3
> 4.6113774E-2 11 1.034305 [DBBPE].[BusnPartEmpId] NULL PLAN_ROW -1 1.0
> | |--Nested Loops(Inner Join, OUTER REFERENCES

[DB].[DivBranchId])
> WITH PREFETCH) 17 9 8 Nested Loops Inner Join OUTER
> REFERENCES

[DB].[DivBranchId]) WITH PREFETCH NULL 4826.1626 0.0
> 0.01008668 43 0.98256058 [DBBPE].[BusnPartEmpId] NULL PLAN_ROW -1 1.0
> | |--Filter(WHERE

[DB].[DivNo]='000')) 17 11 9 Filter Filter
> WHERE

[DB].[DivNo]='000') NULL 15.442919 0.0 7.5600001E-5 29
> 0.96141553 [DB].[DivBranchId] NULL PLAN_ROW -1 1.0
> | | |--Bookmark Lookup(BOOKMARK

[Bmk1002]),
> OBJECT

[EDGE].[dbo].[tblDivBranch] AS [DB]) WITH PREFETCH) 17 12 11
> Bookmark Lookup Bookmark Lookup BOOKMARK

[Bmk1002]),
> OBJECT

[EDGE].[dbo].[tblDivBranch] AS [DB]) WITH PREFETCH
> [DB].[DivBranchId], [DB].[DivNo] 315.0 0.95935196 1.7324999E-4 29
> 0.96133989 [DB].[DivBranchId], [DB].[DivNo] NO
> STATS

[tblDivBranch].[DivNo]) PLAN_ROW -1 1.0
> | | |--Index Seek(OBJECT

[EDGE].[dbo].[tblDivBranch].[IDXDivBranch1]
> AS [DB]), SEEK

[DB].[BusnPartId]=2068) ORDERED FORWARD) 17 14 12 Index
> Seek Index Seek OBJECT

[EDGE].[dbo].[tblDivBranch].[IDXDivBranch1] AS
> [DB]), SEEK

[DB].[BusnPartId]=2068) ORDERED FORWARD [Bmk1002] 315.0
> 1.6017125E-3 2.129725E-4 19 1.8146849E-3 [Bmk1002] NULL PLAN_ROW -1 1.0
> | |--Index
> Seek(OBJECT

[EDGE].[dbo].[tblDivBranchBusnPartEmp].[XPKDivBranchBusnPartEmp]
> AS [DBBPE]), SEEK

[DBBPE].[DivBranchId]=[DB].[DivBranchId]) ORDERED
> FORWARD) 17 18 9 Index Seek Index Seek
> OBJECT

[EDGE].[dbo].[tblDivBranchBusnPartEmp].[XPKDivBranchBusnPartEmp]
> AS [DBBPE]), SEEK

[DBBPE].[DivBranchId]=[DB].[DivBranchId]) ORDERED
> FORWARD [DBBPE].[BusnPartEmpId] 312.5162 3.2034251E-3 4.2320538E-4 23
> 0.01105837 [DBBPE].[BusnPartEmpId] NULL PLAN_ROW -1 15.442919
> |--Index Seek(OBJECT

[EDGE].[dbo].[tblBusnPartEmp].[XPKBusnPartEmp] AS
> [BPE]), SEEK

[BPE].[BusnPartEmpId]=[DBBPE].[BusnPartEmpId]) ORDERED
> FORWARD) 17 19 6 Index Seek Index Seek
> OBJECT

[EDGE].[dbo].[tblBusnPartEmp].[XPKBusnPartEmp] AS [BPE]),
> SEEK

[BPE].[BusnPartEmpId]=[DBBPE].[BusnPartEmpId]) ORDERED FORWARD
> [Bmk1000] 1.0 3.2034251E-3 7.9603E-5 19 7.0633183 [Bmk1000] NULL
> PLAN_ROW -1 4826.1626
> If you notice the query plan are different by a major physical
> operation. Test env. query plan uses index seek where as dev
> environment query plan uses a table scan. Why? I am totally confused.
> I checked on indexes also, and in both env. it is the same.
> For your quick reference here are the index details too
> index_name index_description index_keys
> ------
> ---------
> -----------
> IDXBusnPartEmp1 nonclustered located on PRIMARY EmpNTLogin
> IX_tblBusnPartEmp nonclustered located on PRIMARY EdgeUserTypeCd
> XPKBusnPartEmp nonclustered, unique, primary key located on PRIMARY
> BusnPartEmpId
>
> Can somebody help me on this issue? The sql instances sit on different
> system having same hardware, server configuration. The service packs
> are also same.
> Thanks in advance.
>

Friday, March 23, 2012

Query Plans differs

Query plan variation

Query plan utilization vs CPU time...

Query plan utilization vs CPU time...

Query plan re-use on views?

Query plan question

Query Plan Question

query plan on sql server 2005

Query plan miscalculates row count causing system slow down?

Query plan miscalculates row count causing system slow down?

Query Plan in SQL Server 2005 SP2

Query Plan in SQL Server 2005 SP2

Query Plan Guides don't seem to work

Query Plan Guides don't seem to work

Query plan different in different instances

Query longer in SP

Blog Archive

About Me