Index Corruption

Hi,
Does anyone know of an obvious reason for indexes to get corrupt or have the
statistics get really out of wack' We had a problem today where the
performance just went down the toilet.. It was really obvious that a
particular frequent query was taking 1000 times longer than normal.. The
query plan had a new start "Scan Constants'" I dropped and recreated
the index and the problem went away..
Why would it so suddenly go bad?
Thanks
BillHi
Auto Create and Update Statistics On?
The SP could generate another query plan during the day it it finds it's
earlier estimations were wrong.
I have found that once the statistics get really out of date, queries going
souith are occuring more frequently.
There has been a bit of discussion on this in
microsoft.public.sqlserver.server and
microsoft.public.sqlserver.programming lately on it.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bill" <someone@.somewhere.com> wrote in message
news:u9uGUCkNFHA.688@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Does anyone know of an obvious reason for indexes to get corrupt or have
> the statistics get really out of wack' We had a problem today where
> the performance just went down the toilet.. It was really obvious that
> a particular frequent query was taking 1000 times longer than normal..
> The query plan had a new start "Scan Constants'" I dropped and
> recreated the index and the problem went away..
> Why would it so suddenly go bad?
> Thanks
> Bill
>|||Yep, Auto Create and Auto Update are on...
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ujKN8FkNFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Hi
> Auto Create and Update Statistics On?
> The SP could generate another query plan during the day it it finds it's
> earlier estimations were wrong.
> I have found that once the statistics get really out of date, queries
> going souith are occuring more frequently.
> There has been a bit of discussion on this in
> microsoft.public.sqlserver.server and
> microsoft.public.sqlserver.programming lately on it.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Bill" <someone@.somewhere.com> wrote in message
> news:u9uGUCkNFHA.688@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> Does anyone know of an obvious reason for indexes to get corrupt or have
>> the statistics get really out of wack' We had a problem today where
>> the performance just went down the toilet.. It was really obvious that
>> a particular frequent query was taking 1000 times longer than normal..
>> The query plan had a new start "Scan Constants'" I dropped and
>> recreated the index and the problem went away..
>> Why would it so suddenly go bad?
>> Thanks
>> Bill
>|||That is not index corruption and by reindexing you simply forced a new plan
to be generated. You could have just recompiled the proc. This happens
occasionally. You can have a value that is atypical and requires a scan
where as all the others would use a seek. If the first time the proc was
compiled or recompiled it happened to get that bad value passed in the query
plan would be right for that one time but wrong for the majority of the
others.
--
Andrew J. Kelly SQL MVP
"Bill" <someone@.somewhere.com> wrote in message
news:u9uGUCkNFHA.688@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Does anyone know of an obvious reason for indexes to get corrupt or have
> the statistics get really out of wack' We had a problem today where
> the performance just went down the toilet.. It was really obvious that
> a particular frequent query was taking 1000 times longer than normal..
> The query plan had a new start "Scan Constants'" I dropped and
> recreated the index and the problem went away..
> Why would it so suddenly go bad?
> Thanks
> Bill
>