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
>

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
>

Index Creation = 100% CPU Utilization

I have a large table in a production environment that I needs to have an
index created on it. Its already got a clustered index on it, and I need to
create a non-clustered index. On creation, the CPU usage hits 100%, which
brings response time of the application over reasonable limits. Is there any
way to limit the CPU Usage for this task, or is there any other workaround to
create this index without bringing the application offline?
SQL Server 2000 SP3
P4 - 4 x 1GB
4GB Memory
Thanks in adavance
Set the DOP at the server level to 2 processors. That way there will be two
for the index creation and 2 for everyone else. It will take longer for the
index to be created but it won't affect the other users as much.
Andrew J. Kelly SQL MVP
"g$" <g@.discussions.microsoft.com> wrote in message
news:EF7C237E-1135-4E9D-AB10-450B2C031815@.microsoft.com...
> I have a large table in a production environment that I needs to have an
> index created on it. Its already got a clustered index on it, and I need
to
> create a non-clustered index. On creation, the CPU usage hits 100%, which
> brings response time of the application over reasonable limits. Is there
any
> way to limit the CPU Usage for this task, or is there any other workaround
to
> create this index without bringing the application offline?
> SQL Server 2000 SP3
> P4 - 4 x 1GB
> 4GB Memory
> Thanks in adavance

Index Creation = 100% CPU Utilization

I have a large table in a production environment that I needs to have an
index created on it. Its already got a clustered index on it, and I need to
create a non-clustered index. On creation, the CPU usage hits 100%, which
brings response time of the application over reasonable limits. Is there any
way to limit the CPU Usage for this task, or is there any other workaround to
create this index without bringing the application offline?
SQL Server 2000 SP3
P4 - 4 x 1GB
4GB Memory
Thanks in adavanceSet the DOP at the server level to 2 processors. That way there will be two
for the index creation and 2 for everyone else. It will take longer for the
index to be created but it won't affect the other users as much.
--
Andrew J. Kelly SQL MVP
"g$" <g@.discussions.microsoft.com> wrote in message
news:EF7C237E-1135-4E9D-AB10-450B2C031815@.microsoft.com...
> I have a large table in a production environment that I needs to have an
> index created on it. Its already got a clustered index on it, and I need
to
> create a non-clustered index. On creation, the CPU usage hits 100%, which
> brings response time of the application over reasonable limits. Is there
any
> way to limit the CPU Usage for this task, or is there any other workaround
to
> create this index without bringing the application offline?
> SQL Server 2000 SP3
> P4 - 4 x 1GB
> 4GB Memory
> Thanks in adavance