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
>

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, MacI 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...
> 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
> >
> >
>|||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...
> 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
> >
> >
>|||Let us know how that turns out.
--
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: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...
>> 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
>> >
>> >
>>
>|||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:
> > *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
>|||I am afraid the update stats WITH FULLSCAN did not improve the performance.
I will post a close example of what I have just so you all know what the
problem might be.
Thanks for the suggestions.
"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:u86uSE5IFHA.2744@.TK2MSFTNGP15.phx.gbl...
> Let us know how that turns out.
> --
> 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: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...
> >> 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
> >> >
> >> >
> >>
> >>
> >
> >
>

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, MacIt might be a cacheing artifact. I'd bet that detatching the database flush
es the database pages from cache (or at least makes them immediately availab
le 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 p
ick back up after a while?

quote:

---

Originally posted by Mac Vazehgoo
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...
> 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[vbcol=seagreen]
>|||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...
> 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[vbcol=seagreen]
>|||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
>

Poking a hole in Vista Firewall for sqlserver login

I've tried letting in 1433,1434 443,444 and I still can't get through. When I turn off the firewall, it works. What Am I missing?

I don't have Vista with SQL Server to test on (I guess luckily :) ), but this might help you?

http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx

|||

I read the article but it did not help. since I can connect without a firewall, i know it's port related. I'ved tried 1433 and 1434 with UDP as it suggests, but I still can not connect

|||Are you sure your SQL is using 1433 port? Check this in "SQL Server Configuration Manager". Then test with telnet to the SQL Service from the client, if telnet succeeds, we can go further. You can take a look at this post:
http://forums.asp.net/thread/1289341.aspx

Pleeeease help! Urgent! DBCC Reindex left table inaccesible

Hello there!
Where to turn if not to this group when one is in dire need and all
other resources have been tried? =)
But ok - now for the serious part. Our maintenance plan failed to
execute yesterday, a certain table 'memberContacts' couldn't be
reindexed and was left inaccesible!!! :(
Ok, so currently 'memberContacts' has about 800 000 rows, not that much
really. The failure is related to indexex, and if we look at the table
using 'sp_helpIndex' - we get the following (where I suppose that the
autogenerated '== ?' and '?8 ' seems like bad moves) :
---
== ? clustered, hypothetical, auto create located on PRIMARY
ContactDate, ReturnDate, OfficeID
hind_18203215_10A_3A nonclustered, hypothetical, auto create located on
PRIMARY OfficeID, ContactDate
hind_18203215_10A_4A nonclustered, hypothetical, auto create located on
PRIMARY OfficeID, ReturnDate
hind_18203215_10A_9A nonclustered, hypothetical, auto create located on
PRIMARY OfficeID, TempLockedByAdminUser
hind_18203215_3A_10A nonclustered, hypothetical, auto create located on
PRIMARY ContactDate, OfficeID
hind_18203215_3A_4A nonclustered, hypothetical, auto create located on
PRIMARY ContactDate, ReturnDate
hind_18203215_3A_4A_10A nonclustered, hypothetical, auto create
located on PRIMARY ContactDate, ReturnDate, OfficeID
hind_18203215_3A_4A_9A nonclustered, hypothetical, auto create
located on PRIMARY ContactDate, ReturnDate, TempLockedByAdminUser
hind_18203215_3A_9A nonclustered, hypothetical, auto create located on
PRIMARY ContactDate, TempLockedByAdminUser
hind_18203215_4A_10A nonclustered, hypothetical, auto create located on
PRIMARY ReturnDate, OfficeID
hind_18203215_4A_3A nonclustered, hypothetical, auto create located on
PRIMARY ReturnDate, ContactDate
hind_18203215_4A_9A nonclustered, hypothetical, auto create located on
PRIMARY ReturnDate, TempLockedByAdminUser
hind_18203215_9A_10A nonclustered, hypothetical, auto create located on
PRIMARY TempLockedByAdminUser, OfficeID
hind_18203215_9A_4A nonclustered, hypothetical, auto create located on
PRIMARY TempLockedByAdminUser, ReturnDate
hind_c_18203215_10A clustered, hypothetical, auto create located on
PRIMARY OfficeID
hind_c_18203215_3A clustered, hypothetical, auto create located on
PRIMARY ContactDate
hind_c_18203215_4A clustered, hypothetical, auto create located on
PRIMARY ReturnDate
hind_c_18203215_9A clustered, hypothetical, auto create located on
PRIMARY TempLockedByAdminUser
IX_ContactDate nonclustered located on PRIMARY ContactDate
IX_MemberID nonclustered located on PRIMARY MemberID
IX_ReturnDate nonclustered located on PRIMARY ReturnDate
IX_TempLockedByAdminUser nonclustered located on PRIMARY
TempLockedByAdminUser
?8 clustered, hypothetical, auto create located on PRIMARY
ContactDate, ReturnDate
MemberContacts1 clustered located on PRIMARY
TempLockedByAdminUser, ReturnDate
PK_MemberContacts nonclustered, unique, primary key located on PRIMARY
MemberContactID
---
Now removing the badly named autogenerated indexes probably should solve
the problem. But since these are named quite inproperly the can't be
deleted. And any 'selects' or other attempts to view the table resulst
in :
---
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection Broken
---
So how do I go about removing the bad indexes?
MANY MANY MANY THANX (and a very merry x-mas to you all)
Regards,
JohanJust found the answer - in article
http://support.microsoft.com/default.aspx?
scid=http://support.microsoft.com:80/support/kb/articles/Q293/1/77.ASP&N
oWebContent=1
But since this only recognizes the problem with 'hind%'-indexes you'll
have to adjust statement to include (for example) '== '. This problems
seems to have been a well known fact for sql7 and "known" for sql2000.
After scanning this newsgroup it seems that I'm not the only one
experiencing this problem, and it seems to have risen to "fame" with
sp3a...
2 words - "fuck" & "microsoft"...
Peace,
Johan
In article <MPG.1a479cba5768a7ea989680@.news.internet5.net>, johan@.fap.se
says...
> Hello there!
> Where to turn if not to this group when one is in dire need and all
> other resources have been tried? =)
> But ok - now for the serious part. Our maintenance plan failed to
> execute yesterday, a certain table 'memberContacts' couldn't be
> reindexed and was left inaccesible!!! :(
> Ok, so currently 'memberContacts' has about 800 000 rows, not that much
> really. The failure is related to indexex, and if we look at the table
> using 'sp_helpIndex' - we get the following (where I suppose that the
> autogenerated '== ?' and '?8 ' seems like bad moves) :
> ---
> == ? clustered, hypothetical, auto create located on PRIMARY
> ContactDate, ReturnDate, OfficeID
> hind_18203215_10A_3A nonclustered, hypothetical, auto create located on
> PRIMARY OfficeID, ContactDate
> hind_18203215_10A_4A nonclustered, hypothetical, auto create located on
> PRIMARY OfficeID, ReturnDate
> hind_18203215_10A_9A nonclustered, hypothetical, auto create located on
> PRIMARY OfficeID, TempLockedByAdminUser
> hind_18203215_3A_10A nonclustered, hypothetical, auto create located on
> PRIMARY ContactDate, OfficeID
> hind_18203215_3A_4A nonclustered, hypothetical, auto create located on
> PRIMARY ContactDate, ReturnDate
> hind_18203215_3A_4A_10A nonclustered, hypothetical, auto create
> located on PRIMARY ContactDate, ReturnDate, OfficeID
> hind_18203215_3A_4A_9A nonclustered, hypothetical, auto create
> located on PRIMARY ContactDate, ReturnDate, TempLockedByAdminUser
> hind_18203215_3A_9A nonclustered, hypothetical, auto create located on
> PRIMARY ContactDate, TempLockedByAdminUser
> hind_18203215_4A_10A nonclustered, hypothetical, auto create located on
> PRIMARY ReturnDate, OfficeID
> hind_18203215_4A_3A nonclustered, hypothetical, auto create located on
> PRIMARY ReturnDate, ContactDate
> hind_18203215_4A_9A nonclustered, hypothetical, auto create located on
> PRIMARY ReturnDate, TempLockedByAdminUser
> hind_18203215_9A_10A nonclustered, hypothetical, auto create located on
> PRIMARY TempLockedByAdminUser, OfficeID
> hind_18203215_9A_4A nonclustered, hypothetical, auto create located on
> PRIMARY TempLockedByAdminUser, ReturnDate
> hind_c_18203215_10A clustered, hypothetical, auto create located on
> PRIMARY OfficeID
> hind_c_18203215_3A clustered, hypothetical, auto create located on
> PRIMARY ContactDate
> hind_c_18203215_4A clustered, hypothetical, auto create located on
> PRIMARY ReturnDate
> hind_c_18203215_9A clustered, hypothetical, auto create located on