Index Statistics are Missing

SQL Server 2000, Enterprise Addition. 2 CPU server, 4 with
hyperthreading. 4G memory (3 used by SQL), plenty of hard drive space,
database/log/tempdb files distributed across multiple spindles.
I've got a table with 3GB/a bit over 41 million rows. It has two
non-clustered indexes. There are no statistics for these indexes
(sysIndexes.statblob is null). If I drop and recreate the indexes,
statistics are recalculated... but if (when, it's part of a regular
job) I run UPDATE STATISTICS, they disappear (get nulled out again).
Moreover, when I have statistics on one of these indexes, it is not
used by the query compiler ("select col, count(*) group by col" where
col is indexed is processed by a table scan, not an index scan).
Putting in a table hint to force usage gets the index scan, and better
performance.
This just cropped up recently. I've restored and played around with a
copy of the database from last week (only fractionally smaller), and it
does not exhibit this behavior (statistics exist, do not get wiped when
recalced).
Nosing around, I've found that there are several tables (all containing
data) that appear to have the same problem, though I've only got the
one big guy. Any ideas what can cause this behavior?
PhilipMaybe the indexes were created with the STATISTICS_NORECOMPUTE option? I
haven't tested it but it sounds feasible.
Andrew J. Kelly SQL MVP
<philip.kelley@.gmail.com> wrote in message
news:1147793033.972043.153060@.u72g2000cwu.googlegroups.com...
> SQL Server 2000, Enterprise Addition. 2 CPU server, 4 with
> hyperthreading. 4G memory (3 used by SQL), plenty of hard drive space,
> database/log/tempdb files distributed across multiple spindles.
> I've got a table with 3GB/a bit over 41 million rows. It has two
> non-clustered indexes. There are no statistics for these indexes
> (sysIndexes.statblob is null). If I drop and recreate the indexes,
> statistics are recalculated... but if (when, it's part of a regular
> job) I run UPDATE STATISTICS, they disappear (get nulled out again).
> Moreover, when I have statistics on one of these indexes, it is not
> used by the query compiler ("select col, count(*) group by col" where
> col is indexed is processed by a table scan, not an index scan).
> Putting in a table hint to force usage gets the index scan, and better
> performance.
> This just cropped up recently. I've restored and played around with a
> copy of the database from last week (only fractionally smaller), and it
> does not exhibit this behavior (statistics exist, do not get wiped when
> recalced).
> Nosing around, I've found that there are several tables (all containing
> data) that appear to have the same problem, though I've only got the
> one big guy. Any ideas what can cause this behavior?
> Philip
>|||In my tests, I dropped and recreated my "new" indexes several times
(scripted in Query Analyzer, not in any GUI tool. I did not specify
that option; is there any way this could have been configured as the
default for this command? Once built, the indexes had statistics. As
soon as I issued UPDATE STATISTICS, they disappeared.
Philip|||There is no default that I am aware of. Are you running any trace flags?
What does DBCC TRACESTATUS(-1) give you? What service pack are you on? If
you are not on the latest I would suggest testing to see if that may help.
Andrew J. Kelly SQL MVP
<philip.kelley@.gmail.com> wrote in message
news:1147881230.970958.76080@.38g2000cwa.googlegroups.com...
> In my tests, I dropped and recreated my "new" indexes several times
> (scripted in Query Analyzer, not in any GUI tool. I did not specify
> that option; is there any way this could have been configured as the
> default for this command? Once built, the indexes had statistics. As
> soon as I issued UPDATE STATISTICS, they disappeared.
> Philip
>|||We are not intentionally running any trace flags. When I run DBCC
TRACESTATUS (-1), I get back:
Trace option(s) not enabled for this connection. Use 'DBCC TRACEON()'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
which I'd guess means that no trace flags are running.
The Production server where the database was hosted is a two-node
active/passive cluster, running service pack 4; the box I'm doing the
testing on is a single server, sp3, standard edition. (The server is
slated to be rebuilt any day now.)
Philip|||Sorry but not sure what else to tell you other than to open a case with MS
PSS if this continues.
Andrew J. Kelly SQL MVP
<philip.kelley@.gmail.com> wrote in message
news:1147987042.392899.6900@.j33g2000cwa.googlegroups.com...
> We are not intentionally running any trace flags. When I run DBCC
> TRACESTATUS (-1), I get back:
> Trace option(s) not enabled for this connection. Use 'DBCC TRACEON()'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> which I'd guess means that no trace flags are running.
> The Production server where the database was hosted is a two-node
> active/passive cluster, running service pack 4; the box I'm doing the
> testing on is a single server, sp3, standard edition. (The server is
> slated to be rebuilt any day now.)
> Philip
>