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 & statistics

sql2000
is there some statistics that will show me if some index is missing?
i mean, according to table usage, queries etc, that overall performance
[respond time] would be improved if some column being indexed
i've read that server may refuse to use some existing index during query if
estimated that no performance would be gained?
is server able to by itself create and use temporary index if estimate that
flat table scan is time & disk i/o wasting?
any comments?
thnxSQL Server 2005 keep track of index usage over time and also can suggest you to create new useful
indexes (through some dynamic management views). 2000 do not have that functionality.
> i've read that server may refuse to use some existing index during query if estimated that no
> performance would be gained?
Yes, SQL Server estimate cost for different ways of executing a query and will use the plan it
considers the cheapest.
> is server able to by itself create and use temporary index if estimate that flat table scan is
> time & disk i/o wasting?
To create an index, it would have to scan all data. IF the alternative is a table scan in the first
place, why scan all data, create the index, if the alternative was to scan all data in the first
place? Having said that, there are strategies that SQL Server can use which are similar to creating
an index on the fly.
Say you have a join between two tables, and if you were to execute this so that "for each row in
tableA, look for matches in tableB". Now, tableB would be scanned over *several times*. SQL Server
6.5 and earlier could create a "temporary" index on tableB. As of 7.0, we have more modern methods,
like a "hash join", which at a *very* high level could be considered like creating a temporary
index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:%23W2tx1M1HHA.3788@.TK2MSFTNGP02.phx.gbl...
> sql2000
> is there some statistics that will show me if some index is missing?
> i mean, according to table usage, queries etc, that overall performance [respond time] would be
> improved if some column being indexed
> i've read that server may refuse to use some existing index during query if estimated that no
> performance would be gained?
> is server able to by itself create and use temporary index if estimate that flat table scan is
> time & disk i/o wasting?
> any comments?
> thnx
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> je
napisao u poruci interesnoj
grupi:FE5810C8-F471-4028-A5AF-B7734630503C@.microsoft.com...
>> is server able to by itself create and use temporary index if estimate
>> that flat table scan is time & disk i/o wasting?
> To create an index, it would have to scan all data. IF the alternative is
> a table scan in the first place, why scan all data, create the index, if
> the alternative was to scan all data in the first place?
no! no!
i meant in relation with statistics [having history of usage]
if some query is repeatedly [many times per day] used as flat scan, f.e
like:
select *
from tbl1
where fld1 between val1 and val2
is sql server able to recognize that having index on fld1 may *dramaticaly*
improve performances?|||Hi,
I think you need to consider using the Index Tuning Wizard to help you
out here -
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tunesql.mspx.
Jonathan
sali wrote:
> sql2000
> is there some statistics that will show me if some index is missing?
> i mean, according to table usage, queries etc, that overall performance
> [respond time] would be improved if some column being indexed
> i've read that server may refuse to use some existing index during query if
> estimated that no performance would be gained?
> is server able to by itself create and use temporary index if estimate that
> flat table scan is time & disk i/o wasting?
> any comments?
> thnx
>|||SQL Server 2005 will recognize that fact and you can query the dynamic management views to get this
historical information, and create indexes based on that information. SQL Server 2005 will not
create indexes by itself.
SQL Server 2000 does not have any such functionality, so Index Tuning Wizard, as suggested by
Jonathan can be a tool to consider.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sali" <sali@.euroherc.hr> wrote in message news:uSvVWrO1HHA.6072@.TK2MSFTNGP03.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> je napisao u poruci interesnoj
> grupi:FE5810C8-F471-4028-A5AF-B7734630503C@.microsoft.com...
>> is server able to by itself create and use temporary index if estimate that flat table scan is
>> time & disk i/o wasting?
>> To create an index, it would have to scan all data. IF the alternative is a table scan in the
>> first place, why scan all data, create the index, if the alternative was to scan all data in the
>> first place?
> no! no!
> i meant in relation with statistics [having history of usage]
> if some query is repeatedly [many times per day] used as flat scan, f.e like:
> select *
> from tbl1
> where fld1 between val1 and val2
> is sql server able to recognize that having index on fld1 may *dramaticaly* improve performances?
>
>