Developer Forum

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.yqcomputer.com/
http://www.yqcomputer.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.







the

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
.


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.







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.