Query Plans differs

Have you tryed updating the statistic ?
Try doing an
exec sp_createstats
exec sp_updatestats
Peter
"Real knowledge is to know the extent of one's ignorance."
Confucius

>--Original Message--
>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
>
>.
>Yes, the statistics are updated before the run - thanks for the suggestion
though
Paul
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:277501c51ffa$c4d314e0$a501280a@.phx.gbl...
> Have you tryed updating the statistic ?
> Try doing an
> exec sp_createstats
> exec sp_updatestats
> Peter
> "Real knowledge is to know the extent of one's ignorance."
> Confucius
>
> Query Analyser would
> now when one of the
> query comes out with
> TSQL and paste it into
> as when estimated
> code only takes 800
> time :-)
> on the SP, but it|||The only other thing I can think of is to delete your sp
then immediatly re-create it, it does sound like your sp
has gone a bit odd.
BTW my other sugestion about updating the stats was
garbage (when I had a chance to think about it)
Peter
"A man is never more truthful than when he acknowledges
himself a liar."
Mark Twain

>--Original Message--
>Yes, the statistics are updated before the run - thanks
for the suggestion
>though
>Paul
>"Peter The Spate" <anonymous@.discussions.microsoft.com>
wrote in message
>news:277501c51ffa$c4d314e0$a501280a@.phx.gbl...
ignorance."
while
explicit
>
>.
>|||I've tried re-creating the SP a couple of times with no effect - as I said
it's really odd as this procedure has been in production since November and
it started behaving oddly a couple of days ago, with no real change in the
underlying data volume or distribution :-(
Paul
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:3c9a01c5200f$5676c630$a601280a@.phx.gbl...
> The only other thing I can think of is to delete your sp
> then immediatly re-create it, it does sound like your sp
> has gone a bit odd.
> BTW my other sugestion about updating the stats was
> garbage (when I had a chance to think about it)
> Peter
> "A man is never more truthful than when he acknowledges
> himself a liar."
> Mark Twain
>
>
> for the suggestion
> wrote in message
> ignorance."
> while
> explicit

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)
>