Tuesday, March 27, 2012

Change in Statistics

I added several indexes and statistics to a SQL 2000
database, and the next day I found that 4 of the
statistics no longer exist. Is there any way that the
auto create/update statistics could have caused this? Or
anything else, other than dropping them?
In addition, what effect would this have on my query
performance? I'm a little unsure about how the indexes
and statistics work together, but any help would be
greatly appreciated.
Thank you,
HeidiI guess somebody or some job deleted that statistics. SQL dose not delete
index or statistics by itself.
Since, SQL server dose cost based optimization, the statistics is very
important to decide the good plan. it must be up to date.
"hdsjunk" <anonymous@.discussions.microsoft.com> wrote in message
news:103d01c48bc9$682e5210$a301280a@.phx.gbl...
> I added several indexes and statistics to a SQL 2000
> database, and the next day I found that 4 of the
> statistics no longer exist. Is there any way that the
> auto create/update statistics could have caused this? Or
> anything else, other than dropping them?
> In addition, what effect would this have on my query
> performance? I'm a little unsure about how the indexes
> and statistics work together, but any help would be
> greatly appreciated.
> Thank you,
> Heidi|||Heidi,
It is likely that a job or other process, or even a person deleted these
statistics. SQL Server won't delete statistics without being asked.
If you do not have statistics on larger tables, SQL Server finds it
difficult to know what indexes to choose when compiling the query plan.
It will quite often choose the wrong index if you do not have
statistics, or if your statistics are wildly inaccurate (out-of-date).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
hdsjunk wrote:
> I added several indexes and statistics to a SQL 2000
> database, and the next day I found that 4 of the
> statistics no longer exist. Is there any way that the
> auto create/update statistics could have caused this? Or
> anything else, other than dropping them?
> In addition, what effect would this have on my query
> performance? I'm a little unsure about how the indexes
> and statistics work together, but any help would be
> greatly appreciated.
> Thank you,
> Heidi

No comments:

Post a Comment