Poor performace re-attaching DB after a detach

I was real surprised to see the query performance goes real bad once I
detach a database and turn around the re-attach the database. I did the
usual detach with checking the 'Update Statistics'.
I have confirmed that doing a detach without updating the statistics and
then re-attaching does not change the query plan or degrade the performance.
This is counter-intuitive and has me worried because accurate statistics
should give the best performance not the worst.
Can anyone comment on this behavior ? Thanks, Mac
I have used SQL Server for almost 5 years now and have never
seen the behavior you describe. Are you sure you are accurately
testing the speed up on detach? You may want to review the profiler
results to see exactly what is happening.
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.learncsharp.net/home/listings.aspx
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:d0ii9v$1qf6$1@.si05.rsvl.unisys.com...
>I was real surprised to see the query performance goes real bad once I
> detach a database and turn around the re-attach the database. I did the
> usual detach with checking the 'Update Statistics'.
> I have confirmed that doing a detach without updating the statistics and
> then re-attaching does not change the query plan or degrade the
> performance.
> This is counter-intuitive and has me worried because accurate statistics
> should give the best performance not the worst.
> Can anyone comment on this behavior ? Thanks, Mac
>
|||Thanks for responding. I did check the query plan before and after
detach/re-attach and the query plan changed for worse after I did an Update
Statistics. I have two tables involved in the query. Both tables have
primary keys.
Table A has a very small population where as Table B has a very large
population.
Before setting update statistics, the query plan shows that Table A is
processed as table scan followed by Table B as indexed search. After the
update statistics, the query plan starts table scan on Table B followed by
indexed search on table A and hence the poor performance.
The point I see here is that the query optimizer does not check the
population size to choose the right table to start the table scan. I
suppose I have no choice but use query Hint to tell optimizer how to
proceed.
I should say that in the join query I have, there is no constant key value
to use. So, one table has to be picked as table scan. Had the query
optimizer selected table A as table scan, it would have retrieved the value
it needs to do the index search on Table B.
"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:ue02Td3IFHA.576@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> I have used SQL Server for almost 5 years now and have never
> seen the behavior you describe. Are you sure you are accurately
> testing the speed up on detach? You may want to review the profiler
> results to see exactly what is happening.
> --
> 2005 Microsoft MVP C#
> Robbe Morris
> http://www.robbemorris.com
> http://www.learncsharp.net/home/listings.aspx
>
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:d0ii9v$1qf6$1@.si05.rsvl.unisys.com...
the
>
|||It might be a cacheing artifact. I'd bet that detatching the database
flushes the database pages from cache (or at least makes them
immediately available for flushing). Does the query plan change when
you re-attach or does the performance just go south? Does the slowness
persist or does performance pick back up after a while?
Mac Vazehgoo wrote:
> *I was real surprised to see the query performance goes real bad once
> I
> detach a database and turn around the re-attach the database. I did
> the
> usual detach with checking the 'Update Statistics'.
> I have confirmed that doing a detach without updating the statistics
> and
> then re-attaching does not change the query plan or degrade the
> performance.
> This is counter-intuitive and has me worried because accurate
> statistics
> should give the best performance not the worst.
> Can anyone comment on this behavior ? Thanks, Mac *
revdrwebb
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message946476.html
|||Have you tried updating the stats WITH FULLSCAN?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:d0iur4$22m3$1@.si05.rsvl.unisys.com...
Thanks for responding. I did check the query plan before and after
detach/re-attach and the query plan changed for worse after I did an Update
Statistics. I have two tables involved in the query. Both tables have
primary keys.
Table A has a very small population where as Table B has a very large
population.
Before setting update statistics, the query plan shows that Table A is
processed as table scan followed by Table B as indexed search. After the
update statistics, the query plan starts table scan on Table B followed by
indexed search on table A and hence the poor performance.
The point I see here is that the query optimizer does not check the
population size to choose the right table to start the table scan. I
suppose I have no choice but use query Hint to tell optimizer how to
proceed.
I should say that in the join query I have, there is no constant key value
to use. So, one table has to be picked as table scan. Had the query
optimizer selected table A as table scan, it would have retrieved the value
it needs to do the index search on Table B.
"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:ue02Td3IFHA.576@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> I have used SQL Server for almost 5 years now and have never
> seen the behavior you describe. Are you sure you are accurately
> testing the speed up on detach? You may want to review the profiler
> results to see exactly what is happening.
> --
> 2005 Microsoft MVP C#
> Robbe Morris
> http://www.robbemorris.com
> http://www.learncsharp.net/home/listings.aspx
>
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:d0ii9v$1qf6$1@.si05.rsvl.unisys.com...
the
>
|||Yes. The query plan changes such that it does not start the query
processing with the less populated table.
I am going to use update stats with FULLSCAN as suggested by others to see
if that helps. Thanks.
"revdrwebb" <revdrwebb.1lk0p1@.mail.webservertalk.com> wrote in message
news:revdrwebb.1lk0p1@.mail.webservertalk.com...
> It might be a cacheing artifact. I'd bet that detatching the database
> flushes the database pages from cache (or at least makes them
> immediately available for flushing). Does the query plan change when
> you re-attach or does the performance just go south? Does the slowness
> persist or does performance pick back up after a while?
> Mac Vazehgoo wrote:
>
> --
> revdrwebb
> Posted via http://www.webservertalk.com
> View this thread: http://www.webservertalk.com/message946476.html
>