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 optimiser choosing bad query plan

This seems to be a query optimiser problem.
This selects data into a temp table, and then use the temp table in a join...
Select a.WidgetID, Sum(a.Amount * (b.Sign)) As WidgetValue Into #temp From WidgetTransaction As a inner JOIN TransactionType As b On a.TransactionTypeID = b.TransactionTypeID inner JOIN Widget As aass On a.WidgetID = aass.WidgetID inner JOIN CurrentPeriod As cp On aass.CompanyID = cp.CompanyID Where a.BookID = 1 And b.TransactionTypeClass In ('ADDITION') And cp.UserID = 'ADMIN' And (a.ApplyDate <= cp.EndDate OR (a.TransactionTypeID = 1 And aass.DatePurchased <= cp.EndDate)) And a.BudgetID > 0 Group By a.WidgetID
Select top 61 a.WidgetID,a.WidgetRef,a.Barcode,a.WidgetDesc,a.DatePurchased
,atc3.WidgetValue As CurrencyOriginalValue
From Widget As a
left JOIN #temp As atc3 On a.WidgetID = atc3.WidgetID
Order By a.DatePurchased
... runs very fast and the query plan shows correct data volumes at each branch. Each join is a hash join .

When I try to combine the temp table into the main query via a subselect ....

Select top 61 a.WidgetID,a.WidgetRef,a.Barcode,a.WidgetDesc,a.DatePurchased
,atc3.WidgetValue As CurrencyOriginalValue
From Widget As a
left JOIN (Select a.WidgetID, Sum(a.Amount * (b.Sign)) As WidgetValue From WidgetTransaction As a inner JOIN TransactionType As b On a.TransactionTypeID = b.TransactionTypeID inner JOIN Widget As aass On a.WidgetID = aass.WidgetID inner JOIN CurrentPeriod As cp On aass.CompanyID = cp.CompanyID Where a.BookID = 1 And b.TransactionTypeClass In ('ADDITION') And cp.UserID = 'ADMIN' And (a.ApplyDate <= cp.EndDate OR (a.TransactionTypeID = 1 And aass.DatePurchased <= cp.EndDate)) And a.BudgetID > 0 Group By a.WidgetID) As atc3 On a.WidgetID = atc3.WidgetID
Order By a.DatePurchased

... this runs relatively slowly (very slowly when you have four subtables). The final join (to pull in the subquery into the main select) is showing a "Table Spool/Lazy Spool" into a nested loops join, and the intermediate table spool itself has 139,000 rows in it. Even though when I run the inner select statement on its own it correctly produces 2300 records. With four subtables each "Table Spool/Lazy Spool" join-in has 5 million rows and the query takes 58 seconds.
The main Widget table has 2300 rows and the WidgetTransaction table has 21000 rows.
The problem doesn't occur when I cut down the inner query to a much simpler select statement (even though it selects more records)
When I force it to be a HASH join the problem goes away.
OK it sounds like I've answered my own question with forcing a hash join but I want to understand WHY sql server is choosing a table spool/lazy sppol into a holding table of 139000 or even 5 million rows and is there some logic problem in my sql thats kicked this off ?

The reason is that the optimiser can't accurately assess the number of rows being returned in the subquery and so it assumes a plan based on what it thinks the row count will be.

In your first set of queries the optimiser nows how many rows are in the temp table and so can optimise the query correctly.

It could well be due to the OR clause you have

Try this

Select top 61 a.WidgetID,a.WidgetRef,a.Barcode,a.WidgetDesc,a.DatePurchased

,atc3.WidgetValue + ISNULL(act4.WidgetValue,0) As CurrencyOriginalValue

From Widget As a

left JOIN (Select a.WidgetID, Sum(a.Amount * (b.Sign)) As WidgetValue

From WidgetTransaction As a

inner JOIN TransactionType As b On a.TransactionTypeID = b.TransactionTypeID

inner JOIN CurrentPeriod As cp On aass.CompanyID = cp.CompanyID

Where a.BookID = 1

And b.TransactionTypeClass In ('ADDITION')

And cp.UserID = 'ADMIN'

And (a.ApplyDate <= cp.EndDate

)

And a.BudgetID > 0

Group By a.WidgetID) As atc3 On a.WidgetID = atc3.WidgetID

left JOIN (Select a.WidgetID, Sum(a.Amount * (b.Sign)) As WidgetValue

From WidgetTransaction As a

inner JOIN TransactionType As b On a.TransactionTypeID = b.TransactionTypeID

inner JOIN Widget As aass On a.WidgetID = aass.WidgetID

inner JOIN CurrentPeriod As cp On aass.CompanyID = cp.CompanyID

Where a.BookID = 1

And b.TransactionTypeClass In ('ADDITION')

And cp.UserID = 'ADMIN'

And a.ApplyDate > cp.EndDate

AND a.TransactionTypeID = 1

And aass.DatePurchased <= cp.EndDate

And a.BudgetID > 0

Group By a.WidgetID) As atc4 On a.WidgetID = atc3.WidgetID

Order By a.DatePurchased

Query optimiser bug?

Hi all,
I'm having a real problem with a simple query. The table in question has
about 2 million entries with a clustered index and 2 normal indicies.
This query:
select * from uk_streets where search_thoroughfare like 'agsts drv%'
works fine and fast but...
declare @.q varchar(80)
set @.q='agsts drv%'
select * from uk_streets where search_thoroughfare like @.q
takes about 20 times longer to execute. I figured it's to do with the query
plan being used by the optimiser so I've added an index hint:
select * from uk_streets (index=uk_streets) where search_thoroughfare
like 'agsts drv%'
is still very fast and uses the index but...
declare @.q varchar(80)
set @.q='agsts drv%'
select * from uk_streets (index=uk_streets) where search_thoroughfare
like @.q
ignores the hint and still takes ages (using the clustered index instead).
I'm sure that I'm missing something but it's driving me bonkers! Can anyone
shed any light on the problem please?
Thanks in advance
Jamie.
PS: Here's the table and index structure:
CREATE TABLE UK_Streets
(
locality_key int NOT NULL ,
search_thoroughfare varchar (80) NOT NULL ,
outcode varchar (4) NOT NULL ,
sector char (1) NOT NULL ,
thoroughfare_key int NOT NULL,
thoroughfare_descriptor_key int NOT NULL
)
CREATE CLUSTERED INDEX uk_streets_clustered ON UK_Streets(locality_key,
search_thoroughfare)
CREATE INDEX uk_streets ON UK_Streets(search_thoroughfare)
CREATE INDEX uk_streets_outcode ON UK_Streets(outcode,
search_thoroughfare)Try out with updating The table Statistics ,still if you get the Same
check out the fragmentation using Dbcc and if its bad rebuild the Index
and See
Thanks,
Saradhi|||Hi Saradhi
Thanks for the message - I've run DBREINDEX and it's worked! Thank you so
much - I'd already rebuilt the stats which had no effect but that's
brilliant.
Thanks again!
Cheers
Jamie.
"saradhi" <saradhiY@.gmail.com> wrote in message
news:1124984188.908657.122330@.g43g2000cwa.googlegroups.com...
> Try out with updating The table Statistics ,still if you get the Same
> check out the fragmentation using Dbcc and if its bad rebuild the Index
> and See
> Thanks,
> Saradhi
>|||I am surprised that updating statistics made any difference at all..
The problem is around using variables in your where clause... When the query
is optimized, the optimizer can not see the value of the local variable, and
therefore is unable to use index stats to determine the best index. However
when the value is a literal , index stats can be used to determine the best
index, which can greatly improve performance.
This is not an issue with parameters which are passed in to a Stored proc
and used directly in a where clause... The optimizer CAN see and optimize
for this value - this feature is called Parameter sniffing.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jamie Turner" <jamieturner@.fernhillsolutions.net> wrote in message
news:%23bKy5lXqFHA.712@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I'm having a real problem with a simple query. The table in question has
> about 2 million entries with a clustered index and 2 normal indicies.
> This query:
> select * from uk_streets where search_thoroughfare like 'agsts drv%'
> works fine and fast but...
> declare @.q varchar(80)
> set @.q='agsts drv%'
> select * from uk_streets where search_thoroughfare like @.q
> takes about 20 times longer to execute. I figured it's to do with the
> query plan being used by the optimiser so I've added an index hint:
> select * from uk_streets (index=uk_streets) where search_thoroughfare
> like 'agsts drv%'
> is still very fast and uses the index but...
> declare @.q varchar(80)
> set @.q='agsts drv%'
> select * from uk_streets (index=uk_streets) where search_thoroughfare
> like @.q
> ignores the hint and still takes ages (using the clustered index instead).
> I'm sure that I'm missing something but it's driving me bonkers! Can
> anyone shed any light on the problem please?
> Thanks in advance
> Jamie.
>
> PS: Here's the table and index structure:
> CREATE TABLE UK_Streets
> (
> locality_key int NOT NULL ,
> search_thoroughfare varchar (80) NOT NULL ,
> outcode varchar (4) NOT NULL ,
> sector char (1) NOT NULL ,
> thoroughfare_key int NOT NULL,
> thoroughfare_descriptor_key int NOT NULL
> )
> CREATE CLUSTERED INDEX uk_streets_clustered ON UK_Streets(locality_key,
> search_thoroughfare)
> CREATE INDEX uk_streets ON UK_Streets(search_thoroughfare)
> CREATE INDEX uk_streets_outcode ON UK_Streets(outcode,
> search_thoroughfare)
>

Query optimiser bug?

Hi all,
I'm having a real problem with a simple query. The table in question has
about 2 million entries with a clustered index and 2 normal indicies.
This query:
select * from uk_streets where search_thoroughfare like 'agsts drv%'
works fine and fast but...
declare @.q varchar(80)
set @.q='agsts drv%'
select * from uk_streets where search_thoroughfare like @.q
takes about 20 times longer to execute. I figured it's to do with the query
plan being used by the optimiser so I've added an index hint:
select * from uk_streets (index=uk_streets) where search_thoroughfare
like 'agsts drv%'
is still very fast and uses the index but...
declare @.q varchar(80)
set @.q='agsts drv%'
select * from uk_streets (index=uk_streets) where search_thoroughfare
like @.q
ignores the hint and still takes ages (using the clustered index instead).
I'm sure that I'm missing something but it's driving me bonkers! Can anyone
shed any light on the problem please?
Thanks in advance
Jamie.
PS: Here's the table and index structure:
CREATE TABLE UK_Streets
(
locality_key int NOT NULL ,
search_thoroughfare varchar (80) NOT NULL ,
outcode varchar (4) NOT NULL ,
sector char (1) NOT NULL ,
thoroughfare_key int NOT NULL,
thoroughfare_descriptor_key int NOT NULL
)
CREATE CLUSTERED INDEX uk_streets_clustered ON UK_Streets(locality_key,
search_thoroughfare)
CREATE INDEX uk_streets ON UK_Streets(search_thoroughfare)
CREATE INDEX uk_streets_outcode ON UK_Streets(outcode,
search_thoroughfare)Try out with updating The table Statistics ,still if you get the Same
check out the fragmentation using Dbcc and if its bad rebuild the Index
and See
Thanks,
Saradhi|||Hi Saradhi
Thanks for the message - I've run DBREINDEX and it's worked! Thank you so
much - I'd already rebuilt the stats which had no effect but that's
brilliant.
Thanks again!
Cheers
Jamie.
"saradhi" <saradhiY@.gmail.com> wrote in message
news:1124984188.908657.122330@.g43g2000cwa.googlegroups.com...
> Try out with updating The table Statistics ,still if you get the Same
> check out the fragmentation using Dbcc and if its bad rebuild the Index
> and See
> Thanks,
> Saradhi
>|||I am surprised that updating statistics made any difference at all..
The problem is around using variables in your where clause... When the query
is optimized, the optimizer can not see the value of the local variable, and
therefore is unable to use index stats to determine the best index. However
when the value is a literal , index stats can be used to determine the best
index, which can greatly improve performance.
This is not an issue with parameters which are passed in to a Stored proc
and used directly in a where clause... The optimizer CAN see and optimize
for this value - this feature is called Parameter sniffing.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jamie Turner" <jamieturner@.fernhillsolutions.net> wrote in message
news:%23bKy5lXqFHA.712@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I'm having a real problem with a simple query. The table in question has
> about 2 million entries with a clustered index and 2 normal indicies.
> This query:
> select * from uk_streets where search_thoroughfare like 'agsts drv%'
> works fine and fast but...
> declare @.q varchar(80)
> set @.q='agsts drv%'
> select * from uk_streets where search_thoroughfare like @.q
> takes about 20 times longer to execute. I figured it's to do with the
> query plan being used by the optimiser so I've added an index hint:
> select * from uk_streets (index=uk_streets) where search_thoroughfare
> like 'agsts drv%'
> is still very fast and uses the index but...
> declare @.q varchar(80)
> set @.q='agsts drv%'
> select * from uk_streets (index=uk_streets) where search_thoroughfare
> like @.q
> ignores the hint and still takes ages (using the clustered index instead).
> I'm sure that I'm missing something but it's driving me bonkers! Can
> anyone shed any light on the problem please?
> Thanks in advance
> Jamie.
>
> PS: Here's the table and index structure:
> CREATE TABLE UK_Streets
> (
> locality_key int NOT NULL ,
> search_thoroughfare varchar (80) NOT NULL ,
> outcode varchar (4) NOT NULL ,
> sector char (1) NOT NULL ,
> thoroughfare_key int NOT NULL,
> thoroughfare_descriptor_key int NOT NULL
> )
> CREATE CLUSTERED INDEX uk_streets_clustered ON UK_Streets(locality_key,
> search_thoroughfare)
> CREATE INDEX uk_streets ON UK_Streets(search_thoroughfare)
> CREATE INDEX uk_streets_outcode ON UK_Streets(outcode,
> search_thoroughfare)
>

Query optimiser bug?

Hi all,
I'm having a real problem with a simple query. The table in question has
about 2 million entries with a clustered index and 2 normal indicies.
This query:
select * from uk_streets where search_thoroughfare like 'agsts drv%'
works fine and fast but...
declare @.q varchar(80)
set @.q='agsts drv%'
select * from uk_streets where search_thoroughfare like @.q
takes about 20 times longer to execute. I figured it's to do with the query
plan being used by the optimiser so I've added an index hint:
select * from uk_streets (index=uk_streets) where search_thoroughfare
like 'agsts drv%'
is still very fast and uses the index but...
declare @.q varchar(80)
set @.q='agsts drv%'
select * from uk_streets (index=uk_streets) where search_thoroughfare
like @.q
ignores the hint and still takes ages (using the clustered index instead).
I'm sure that I'm missing something but it's driving me bonkers! Can anyone
shed any light on the problem please?
Thanks in advance
Jamie.
PS: Here's the table and index structure:
CREATE TABLE UK_Streets
(
locality_key int NOT NULL ,
search_thoroughfare varchar (80) NOT NULL ,
outcode varchar (4) NOT NULL ,
sector char (1) NOT NULL ,
thoroughfare_key int NOT NULL,
thoroughfare_descriptor_key int NOT NULL
)
CREATE CLUSTERED INDEX uk_streets_clustered ON UK_Streets(locality_key,
search_thoroughfare)
CREATE INDEX uk_streets ON UK_Streets(search_thoroughfare)
CREATE INDEX uk_streets_outcode ON UK_Streets(outcode,
search_thoroughfare)
Try out with updating The table Statistics ,still if you get the Same
check out the fragmentation using Dbcc and if its bad rebuild the Index
and See
Thanks,
Saradhi
|||Hi Saradhi
Thanks for the message - I've run DBREINDEX and it's worked! Thank you so
much - I'd already rebuilt the stats which had no effect but that's
brilliant.
Thanks again!
Cheers
Jamie.
"saradhi" <saradhiY@.gmail.com> wrote in message
news:1124984188.908657.122330@.g43g2000cwa.googlegr oups.com...
> Try out with updating The table Statistics ,still if you get the Same
> check out the fragmentation using Dbcc and if its bad rebuild the Index
> and See
> Thanks,
> Saradhi
>
|||I am surprised that updating statistics made any difference at all..
The problem is around using variables in your where clause... When the query
is optimized, the optimizer can not see the value of the local variable, and
therefore is unable to use index stats to determine the best index. However
when the value is a literal , index stats can be used to determine the best
index, which can greatly improve performance.
This is not an issue with parameters which are passed in to a Stored proc
and used directly in a where clause... The optimizer CAN see and optimize
for this value - this feature is called Parameter sniffing.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jamie Turner" <jamieturner@.fernhillsolutions.net> wrote in message
news:%23bKy5lXqFHA.712@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I'm having a real problem with a simple query. The table in question has
> about 2 million entries with a clustered index and 2 normal indicies.
> This query:
> select * from uk_streets where search_thoroughfare like 'agsts drv%'
> works fine and fast but...
> declare @.q varchar(80)
> set @.q='agsts drv%'
> select * from uk_streets where search_thoroughfare like @.q
> takes about 20 times longer to execute. I figured it's to do with the
> query plan being used by the optimiser so I've added an index hint:
> select * from uk_streets (index=uk_streets) where search_thoroughfare
> like 'agsts drv%'
> is still very fast and uses the index but...
> declare @.q varchar(80)
> set @.q='agsts drv%'
> select * from uk_streets (index=uk_streets) where search_thoroughfare
> like @.q
> ignores the hint and still takes ages (using the clustered index instead).
> I'm sure that I'm missing something but it's driving me bonkers! Can
> anyone shed any light on the problem please?
> Thanks in advance
> Jamie.
>
> PS: Here's the table and index structure:
> CREATE TABLE UK_Streets
> (
> locality_key int NOT NULL ,
> search_thoroughfare varchar (80) NOT NULL ,
> outcode varchar (4) NOT NULL ,
> sector char (1) NOT NULL ,
> thoroughfare_key int NOT NULL,
> thoroughfare_descriptor_key int NOT NULL
> )
> CREATE CLUSTERED INDEX uk_streets_clustered ON UK_Streets(locality_key,
> search_thoroughfare)
> CREATE INDEX uk_streets ON UK_Streets(search_thoroughfare)
> CREATE INDEX uk_streets_outcode ON UK_Streets(outcode,
> search_thoroughfare)
>