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 size large than data

I've been going through my database looking for the largest tables. I use
sp_spaceused to determine the size.
Most of the tables use more space for data then for indexes, e.g. a
sp_spaceused weeegbon get's me:
name rows reserved data index_size unused
WEEGBON 638105 165984 KB 129960 KB 35848 KB 176 KB
Some of the tables however, use little space for data, and lot's of space
for indexes, e.g.
name rows reserved data index_size unused
WEEGBONVELD 46230 33352 KB 1824 KB 30968 KB 560 KB
sp_helpindex weegbonveld gives:
index_name index description index_keys
PK_WEEGBONTVELD nonclustered, unique, primary key located on PRIMARY
WEEGBON_ID, VELDSRT_ID
index_843202104 nonclustered, unique located on PRIMARY rowguid
_WA_Sys_VELDSRT_ID_5535A963 nonclustered, statistics, auto create located on
PRIMARY VELDSRT_ID
Can anyone, explain what causes the indexes to take up more space than the
data? And what is the _WA_Sys_VELDSRT_... index?
Kind regards,
Grtz,
BartI have a feeling that you are fooled by the size of the statistics. Read about CREATE STATISTICS in
Books Online for info on the statistics (...WA_SYS...). Statistics is not index, but it has to exist
in sysindexes, and sometimes SQL Server mis-represent so it looks like the statistics actually uses
a lot of space. It doesn't. You might want to double-check the space usage for statistics against
sysindexes.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"news.chello.nl" <na@.na.com> wrote in message news:idQnb.438447$lh.112925857@.amsnews02.chello.com...
> I've been going through my database looking for the largest tables. I use
> sp_spaceused to determine the size.
> Most of the tables use more space for data then for indexes, e.g. a
> sp_spaceused weeegbon get's me:
> name rows reserved data index_size unused
> WEEGBON 638105 165984 KB 129960 KB 35848 KB 176 KB
> Some of the tables however, use little space for data, and lot's of space
> for indexes, e.g.
> name rows reserved data index_size unused
> WEEGBONVELD 46230 33352 KB 1824 KB 30968 KB 560 KB
> sp_helpindex weegbonveld gives:
> index_name index description index_keys
> PK_WEEGBONTVELD nonclustered, unique, primary key located on PRIMARY
> WEEGBON_ID, VELDSRT_ID
> index_843202104 nonclustered, unique located on PRIMARY rowguid
> _WA_Sys_VELDSRT_ID_5535A963 nonclustered, statistics, auto create located on
> PRIMARY VELDSRT_ID
> Can anyone, explain what causes the indexes to take up more space than the
> data? And what is the _WA_Sys_VELDSRT_... index?
> Kind regards,
> Grtz,
> Bart
>
>|||If you download the free tool from http://www.sqlfe.com you will be able to
see the space allocated to all tables, indexes, and views. These can be
quickly sorted by space used. You will also be able to see where it is
allocated within the data files.
An index starting with _WA_Sys are statics automatically generated by SQL
server.
Barry McAuslin
"news.chello.nl" <na@.na.com> wrote in message
news:idQnb.438447$lh.112925857@.amsnews02.chello.com...
> I've been going through my database looking for the largest tables. I use
> sp_spaceused to determine the size.
> Most of the tables use more space for data then for indexes, e.g. a
> sp_spaceused weeegbon get's me:
> name rows reserved data index_size unused
> WEEGBON 638105 165984 KB 129960 KB 35848 KB 176 KB
> Some of the tables however, use little space for data, and lot's of space
> for indexes, e.g.
> name rows reserved data index_size unused
> WEEGBONVELD 46230 33352 KB 1824 KB 30968 KB 560 KB
> sp_helpindex weegbonveld gives:
> index_name index description index_keys
> PK_WEEGBONTVELD nonclustered, unique, primary key located on PRIMARY
> WEEGBON_ID, VELDSRT_ID
> index_843202104 nonclustered, unique located on PRIMARY rowguid
> _WA_Sys_VELDSRT_ID_5535A963 nonclustered, statistics, auto create located
on
> PRIMARY VELDSRT_ID
> Can anyone, explain what causes the indexes to take up more space than the
> data? And what is the _WA_Sys_VELDSRT_... index?
> Kind regards,
> Grtz,
> Bart
>
>sql

Index Maintenance/Calculate Log Space Required

We're resigned ourselves to the fact that the best way to maintain
recoverability and still do a complete rebuild of the indexes in our
databases is to ensure that we have adequate log space available for this
operation. My question is, how can I determine what adequate log space is?
Do I just need enough space to hold all of the indexes? How do I figure that
out?
Thanks.Depending on the database, I plan for the maintenance window to defragment the indexes with dbcc dbreindex and change the recovery model to bulk. When the this is complete I change the model to full and do a complete backup. This reduces the amount of log space needed and still maintains our recoverability.|||We had been changing the recovery model but then you still end up with a
window in which you have no recovery. Some of our backups can take a long
time. Thanks for the suggestion, though.
"Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
news:92CD5BB6-F0EE-465A-96C0-94DB8FD12ADE@.microsoft.com...
> Depending on the database, I plan for the maintenance window to defragment
the indexes with dbcc dbreindex and change the recovery model to bulk. When
the this is complete I change the model to full and do a complete backup.
This reduces the amount of log space needed and still maintains our
recoverability.|||I link it because we had been changing the recovery model to minimize the
log space required of the process. However, we would then have a window
where we couldn't recover the database to a point in time because it breaks
the log chain. Even if you do a full backup after changing the recovery
model back to full, you have still broken the log chain should you need to
recover to a point in time BEFORE the full backup finished but AFTER you
changed the recovery model to bulk-logged.
I realize that the database is available with all recovery models and is
available while rebuilding the indexes (except locks are taken which could
prevent access to some objects during the process).
So, my question remains, how would one calculate the log space required to
rebuild all of the indexes in a given database?
Thanks!
Michelle
"STurner" <anonymous@.discussions.microsoft.com> wrote in message
news:F43B24AF-7911-44C4-AB5E-012DAB29769F@.microsoft.com...
> Michelle,
> Can you clarify why you have linked recoverability of the databases with
the reindexing of the databases. I'm wanting to verify that your not asking
about availability of the databases instead of recoverablitity.
> Thanks!
> STurner|||We have the same issues with our log shipping environment.
For a clustered index it's 1.2 X the data size. But I
can't seem to remember what it is for a non-clustered
index.
>--Original Message--
>I link it because we had been changing the recovery model
to minimize the
>log space required of the process. However, we would then
have a window
>where we couldn't recover the database to a point in time
because it breaks
>the log chain. Even if you do a full backup after
changing the recovery
>model back to full, you have still broken the log chain
should you need to
>recover to a point in time BEFORE the full backup
finished but AFTER you
>changed the recovery model to bulk-logged.
>I realize that the database is available with all
recovery models and is
>available while rebuilding the indexes (except locks are
taken which could
>prevent access to some objects during the process).
>So, my question remains, how would one calculate the log
space required to
>rebuild all of the indexes in a given database?
>Thanks!
>Michelle
>"STurner" <anonymous@.discussions.microsoft.com> wrote in
message
>news:F43B24AF-7911-44C4-AB5E-012DAB29769F@.microsoft.com...
>> Michelle,
>> Can you clarify why you have linked recoverability of
the databases with
>the reindexing of the databases. I'm wanting to verify
that your not asking
>about availability of the databases instead of
recoverablitity.
>> Thanks!
>> STurner
>
>.
>|||One question that hasn't been asked yet is why are you rebuilding the
indexes in the first place?
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"michelle" <michelle@.nospam.com> wrote in message
news:e7BE8hRPEHA.2636@.TK2MSFTNGP10.phx.gbl...
> We had been changing the recovery model but then you still end up with a
> window in which you have no recovery. Some of our backups can take a long
> time. Thanks for the suggestion, though.
> "Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
> news:92CD5BB6-F0EE-465A-96C0-94DB8FD12ADE@.microsoft.com...
> > Depending on the database, I plan for the maintenance window to
defragment
> the indexes with dbcc dbreindex and change the recovery model to bulk.
When
> the this is complete I change the model to full and do a complete backup.
> This reduces the amount of log space needed and still maintains our
> recoverability.
>

Index Maintenance/Calculate Log Space Required

We're resigned ourselves to the fact that the best way to maintain
recoverability and still do a complete rebuild of the indexes in our
databases is to ensure that we have adequate log space available for this
operation. My question is, how can I determine what adequate log space is?
Do I just need enough space to hold all of the indexes? How do I figure that
out?
Thanks.
Michelle,
Can you clarify why you have linked recoverability of the databases with the reindexing of the databases. I'm wanting to verify that your not asking about availability of the databases instead of recoverablitity.
Thanks!
STurner
|||Depending on the database, I plan for the maintenance window to defragment the indexes with dbcc dbreindex and change the recovery model to bulk. When the this is complete I change the model to full and do a complete backup. This reduces the amount of l
og space needed and still maintains our recoverability.
|||We had been changing the recovery model but then you still end up with a
window in which you have no recovery. Some of our backups can take a long
time. Thanks for the suggestion, though.
"Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
news:92CD5BB6-F0EE-465A-96C0-94DB8FD12ADE@.microsoft.com...
> Depending on the database, I plan for the maintenance window to defragment
the indexes with dbcc dbreindex and change the recovery model to bulk. When
the this is complete I change the model to full and do a complete backup.
This reduces the amount of log space needed and still maintains our
recoverability.
|||I link it because we had been changing the recovery model to minimize the
log space required of the process. However, we would then have a window
where we couldn't recover the database to a point in time because it breaks
the log chain. Even if you do a full backup after changing the recovery
model back to full, you have still broken the log chain should you need to
recover to a point in time BEFORE the full backup finished but AFTER you
changed the recovery model to bulk-logged.
I realize that the database is available with all recovery models and is
available while rebuilding the indexes (except locks are taken which could
prevent access to some objects during the process).
So, my question remains, how would one calculate the log space required to
rebuild all of the indexes in a given database?
Thanks!
Michelle
"STurner" <anonymous@.discussions.microsoft.com> wrote in message
news:F43B24AF-7911-44C4-AB5E-012DAB29769F@.microsoft.com...
> Michelle,
> Can you clarify why you have linked recoverability of the databases with
the reindexing of the databases. I'm wanting to verify that your not asking
about availability of the databases instead of recoverablitity.
> Thanks!
> STurner
|||We have the same issues with our log shipping environment.
For a clustered index it's 1.2 X the data size. But I
can't seem to remember what it is for a non-clustered
index.

>--Original Message--
>I link it because we had been changing the recovery model
to minimize the
>log space required of the process. However, we would then
have a window
>where we couldn't recover the database to a point in time
because it breaks
>the log chain. Even if you do a full backup after
changing the recovery
>model back to full, you have still broken the log chain
should you need to
>recover to a point in time BEFORE the full backup
finished but AFTER you
>changed the recovery model to bulk-logged.
>I realize that the database is available with all
recovery models and is
>available while rebuilding the indexes (except locks are
taken which could
>prevent access to some objects during the process).
>So, my question remains, how would one calculate the log
space required to
>rebuild all of the indexes in a given database?
>Thanks!
>Michelle
>"STurner" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:F43B24AF-7911-44C4-AB5E-012DAB29769F@.microsoft.com...
the databases with
>the reindexing of the databases. I'm wanting to verify
that your not asking
>about availability of the databases instead of
recoverablitity.
>
>.
>
|||One question that hasn't been asked yet is why are you rebuilding the
indexes in the first place?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"michelle" <michelle@.nospam.com> wrote in message
news:e7BE8hRPEHA.2636@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> We had been changing the recovery model but then you still end up with a
> window in which you have no recovery. Some of our backups can take a long
> time. Thanks for the suggestion, though.
> "Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
> news:92CD5BB6-F0EE-465A-96C0-94DB8FD12ADE@.microsoft.com...
defragment
> the indexes with dbcc dbreindex and change the recovery model to bulk.
When
> the this is complete I change the model to full and do a complete backup.
> This reduces the amount of log space needed and still maintains our
> recoverability.
>

Index Maintenance/Calculate Log Space Required

We're resigned ourselves to the fact that the best way to maintain
recoverability and still do a complete rebuild of the indexes in our
databases is to ensure that we have adequate log space available for this
operation. My question is, how can I determine what adequate log space is?
Do I just need enough space to hold all of the indexes? How do I figure that
out?
Thanks.Michelle,
Can you clarify why you have linked recoverability of the databases with the
reindexing of the databases. I'm wanting to verify that your not asking abo
ut availability of the databases instead of recoverablitity.
Thanks!
STurner|||Depending on the database, I plan for the maintenance window to defragment t
he indexes with dbcc dbreindex and change the recovery model to bulk. When
the this is complete I change the model to full and do a complete backup. T
his reduces the amount of l
og space needed and still maintains our recoverability.|||We had been changing the recovery model but then you still end up with a
window in which you have no recovery. Some of our backups can take a long
time. Thanks for the suggestion, though.
"Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
news:92CD5BB6-F0EE-465A-96C0-94DB8FD12ADE@.microsoft.com...
> Depending on the database, I plan for the maintenance window to defragment
the indexes with dbcc dbreindex and change the recovery model to bulk. When
the this is complete I change the model to full and do a complete backup.
This reduces the amount of log space needed and still maintains our
recoverability.|||I link it because we had been changing the recovery model to minimize the
log space required of the process. However, we would then have a window
where we couldn't recover the database to a point in time because it breaks
the log chain. Even if you do a full backup after changing the recovery
model back to full, you have still broken the log chain should you need to
recover to a point in time BEFORE the full backup finished but AFTER you
changed the recovery model to bulk-logged.
I realize that the database is available with all recovery models and is
available while rebuilding the indexes (except locks are taken which could
prevent access to some objects during the process).
So, my question remains, how would one calculate the log space required to
rebuild all of the indexes in a given database?
Thanks!
Michelle
"STurner" <anonymous@.discussions.microsoft.com> wrote in message
news:F43B24AF-7911-44C4-AB5E-012DAB29769F@.microsoft.com...
> Michelle,
> Can you clarify why you have linked recoverability of the databases with
the reindexing of the databases. I'm wanting to verify that your not asking
about availability of the databases instead of recoverablitity.
> Thanks!
> STurner|||We have the same issues with our log shipping environment.
For a clustered index it's 1.2 X the data size. But I
can't seem to remember what it is for a non-clustered
index.

>--Original Message--
>I link it because we had been changing the recovery model
to minimize the
>log space required of the process. However, we would then
have a window
>where we couldn't recover the database to a point in time
because it breaks
>the log chain. Even if you do a full backup after
changing the recovery
>model back to full, you have still broken the log chain
should you need to
>recover to a point in time BEFORE the full backup
finished but AFTER you
>changed the recovery model to bulk-logged.
>I realize that the database is available with all
recovery models and is
>available while rebuilding the indexes (except locks are
taken which could
>prevent access to some objects during the process).
>So, my question remains, how would one calculate the log
space required to
>rebuild all of the indexes in a given database?
>Thanks!
>Michelle
>"STurner" <anonymous@.discussions.microsoft.com> wrote in
message
>news:F43B24AF-7911-44C4-AB5E-012DAB29769F@.microsoft.com...
the databases with[vbcol=seagreen]
>the reindexing of the databases. I'm wanting to verify
that your not asking
>about availability of the databases instead of
recoverablitity.
>
>.
>|||One question that hasn't been asked yet is why are you rebuilding the
indexes in the first place?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"michelle" <michelle@.nospam.com> wrote in message
news:e7BE8hRPEHA.2636@.TK2MSFTNGP10.phx.gbl...
> We had been changing the recovery model but then you still end up with a
> window in which you have no recovery. Some of our backups can take a long
> time. Thanks for the suggestion, though.
> "Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
> news:92CD5BB6-F0EE-465A-96C0-94DB8FD12ADE@.microsoft.com...
defragment[vbcol=seagreen]
> the indexes with dbcc dbreindex and change the recovery model to bulk.
When
> the this is complete I change the model to full and do a complete backup.
> This reduces the amount of log space needed and still maintains our
> recoverability.
>

index fillfactor

How can you identify the current space available in a clustered index? I'm
aware of the commands to rebuild the index, but I'd like to be able to
monitor how "full" an index is relative to the original fill factor of 90%.
Thanks in advance.
MarkThanks Tom. Just what I was looking for. I pasted the results below. If
I'm reading the results correctly, the "Avg. Page Density (full)" indicates
that the table is currently existing with a fill factor of about 100%. In
other words, inserts wouldn't be pretty. Correct? Moreover, if a page is
roughly 8000 bytes, and only 75 or so of those bytes are free, that's again
saying that I have 1% or less of free space ... correct again?
Thanks again.
Mark
DBCC SHOWCONTIG scanning 'my_table' table...
Table: 'my_table' (1672393027); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 166
- Extents Scanned.......................: 22
- Extent Switches.......................: 21
- Avg. Pages per Extent..................: 7.5
- Scan Density [Best Count:Actual Count]......: 95.45% [21:22]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 4.55%
- Avg. Bytes Free per Page................: 75.8
- Avg. Page Density (full)................: 99.06%
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:e7eROPRgDHA.2072@.TK2MSFTNGP10.phx.gbl...
Check out DBCC SHOWCONTIG in the BOL.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mark" <field027_nospam_@.umn.edu> wrote in message
news:#5UwdNRgDHA.2984@.TK2MSFTNGP11.phx.gbl...
How can you identify the current space available in a clustered index? I'm
aware of the commands to rebuild the index, but I'd like to be able to
monitor how "full" an index is relative to the original fill factor of 90%.
Thanks in advance.
Mark|||This is a multi-part message in MIME format.
--=_NextPart_000_00F0_01C380F6.D2157E80
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Your average page density says how full the pages are. Right after to =rebuild the index, this will be approximately equal to your fill factor. = the fact that you also have only 75 bytes free per page supports the =high page density that you have.
An insert is not necessarily going to lead to a page split. If you had =skinny rows, e.g. 20 bytes and inserted 2 rows, you would not get a page =split. Also, if the clustered index was on a monotonically increasing =value, you would be adding rows to the "end" of the table and would =simply pick up a new page.
Your table is so small that I would not worry about fragmentation.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mark" <field027_nospam_@.umn.edu> wrote in message =news:#hlqaWRgDHA.2260@.TK2MSFTNGP10.phx.gbl...
Thanks Tom. Just what I was looking for. I pasted the results below. =If
I'm reading the results correctly, the "Avg. Page Density (full)" =indicates
that the table is currently existing with a fill factor of about 100%. =In
other words, inserts wouldn't be pretty. Correct? Moreover, if a =page is
roughly 8000 bytes, and only 75 or so of those bytes are free, that's =again
saying that I have 1% or less of free space ... correct again?
Thanks again.
Mark
DBCC SHOWCONTIG scanning 'my_table' table...
Table: 'my_table' (1672393027); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 166
- Extents Scanned.......................: 22
- Extent Switches.......................: 21
- Avg. Pages per Extent..................: 7.5
- Scan Density [Best Count:Actual Count]......: 95.45% [21:22]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 4.55%
- Avg. Bytes Free per Page................: 75.8
- Avg. Page Density (full)................: 99.06%
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:e7eROPRgDHA.2072@.TK2MSFTNGP10.phx.gbl...
Check out DBCC SHOWCONTIG in the BOL.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mark" <field027_nospam_@.umn.edu> wrote in message
news:#5UwdNRgDHA.2984@.TK2MSFTNGP11.phx.gbl...
How can you identify the current space available in a clustered index? =I'm
aware of the commands to rebuild the index, but I'd like to be able to
monitor how "full" an index is relative to the original fill factor of =90%.
Thanks in advance.
Mark
--=_NextPart_000_00F0_01C380F6.D2157E80
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Your average page density says how =full the pages are. Right after to rebuild the index, this will be approximately =equal to your fill factor. the fact that you also have only 75 bytes free =per page supports the high page density that you have.
An insert is not necessarily going to =lead to a page split. If you had skinny rows, e.g. 20 bytes and inserted 2 =rows, you would not get a page split. Also, if the clustered index was =on a monotonically increasing value, you would be adding rows to the "end" of =the table and would simply pick up a new page.
Your table is so small that I would =not worry about fragmentation.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Mark" = wrote in message news:#hlqaWRgDHA.2260=@.TK2MSFTNGP10.phx.gbl...Thanks Tom. Just what I was looking for. I pasted the results =below. IfI'm reading the results correctly, the "Avg. Page Density (full)" indicatesthat the table is currently existing with a fill factor of =about 100%. Inother words, inserts wouldn't be pretty. Correct? Moreover, if a page isroughly 8000 bytes, =and only 75 or so of those bytes are free, that's againsaying that I =have 1% or less of free space ... correct again?Thanks again.MarkDBCC SHOWCONTIG scanning 'my_table' =table...Table: 'my_table' (1672393027); index ID: 1, database ID: 10TABLE level =scan performed.- Pages Scanned........................: 166- =Extents Scanned.......................: 22- Extent Switches.......................: 21- Avg. Pages per Extent..................: 7.5- Scan Density [Best Count:Actual =Count]......: 95.45% [21:22]- Logical Scan Fragmentation ..............: 0.00%- Extent Scan Fragmentation =...............: 4.55%- Avg. Bytes Free per Page................: 75.8- Avg. =Page Density (full)................: 99.06%"Tom Moreau" == wrote in messagenews:e7eROPRgDHA.2072=@.TK2MSFTNGP10.phx.gbl...Check out DBCC SHOWCONTIG in the BOL.-- Tom----=--Thomas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql"Mark" = wrote in messagenews:#5UwdNRgDHA.2984=@.TK2MSFTNGP11.phx.gbl...How can you identify the current space available in a clustered index? =I'maware of the commands to rebuild the index, but I'd like to be =able tomonitor how "full" an index is relative to the original fill =factor of 90%.Thanks in advance.Mark

---

--=_NextPart_000_00F0_01C380F6.D2157E80--

Index Design Question

I want to create an index that will cause the cost of the query to be as low as possible and also
must minimize the space that is used by the index. What type of index/parameters I can associate when I create an Index. I already have a clustered index

Only option will be non clustered index becuase you have already one clustered index...

You can't minimize the space used by the index, it is depends on columns used to create index...

but you can minimize the space used for index creation using SORT_TEMPDB option.

|||my query is like this
select productid,
sum(qty) as totalQTY,
count(*) as total
from table1
where desqty=2
group by productid

I have clusted index already defined in the id which is the identity

I have 2 options create non clustered on desqty and include product id as the included column
second is non clustered desqty and inculde both the productid and qty as included column

which one will be the better option?|||

Try both options and study the execution plans to find out for yourself.

You should find the second option to be the most performant as the index would be a 'covering index' and the base table would not need to be touched when the data is read.

Chris

|||

Turn on statistics IO by running the statement SET STATISTICS IO ON

Then, try running the query with the graphical execution plan turned on, and study the io statistics results.

I would try to create a "covering" index by creating an index that has desqty, productid, and qty (in that order) as regular index columns rather than included columns.

|||

Admin, there are some new features in SQL 2005.. whre you can name other columns in an index, and that will help make covered indexes so SQL Server might not need to query as many pages... see teh INCLUDE parameter of a CREATE INDEX...

Also, there are some new system views... that can help isolate missing stats/indexes...

select * from sys.dm_db_missing_index_details

select * from sys.dm_db_missing_index_groups

select * from sys.dm_db_missing_index_group_stats

A sample of how to join them follows...

SELECT mig.*, statement AS table_name,

column_id, column_name, column_usage

FROM sys.dm_db_missing_index_details AS mid

CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)

INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle

ORDER BY mig.index_group_handle, mig.index_handle, column_id;

GO

Bruce

Index Design Question

I want to create an index that will cause the cost of the query to be as low as possible and also
must minimize the space that is used by the index. What type of index/parameters I can associate when I create an Index. I already have a clustered index

Only option will be non clustered index becuase you have already one clustered index...

You can't minimize the space used by the index, it is depends on columns used to create index...

but you can minimize the space used for index creation using SORT_TEMPDB option.

|||my query is like this
select productid,
sum(qty) as totalQTY,
count(*) as total
from table1
where desqty=2
group by productid

I have clusted index already defined in the id which is the identity

I have 2 options create non clustered on desqty and include product id as the included column
second is non clustered desqty and inculde both the productid and qty as included column

which one will be the better option?|||

Try both options and study the execution plans to find out for yourself.

You should find the second option to be the most performant as the index would be a 'covering index' and the base table would not need to be touched when the data is read.

Chris

|||

Turn on statistics IO by running the statement SET STATISTICS IO ON

Then, try running the query with the graphical execution plan turned on, and study the io statistics results.

I would try to create a "covering" index by creating an index that has desqty, productid, and qty (in that order) as regular index columns rather than included columns.

|||

Admin, there are some new features in SQL 2005.. whre you can name other columns in an index, and that will help make covered indexes so SQL Server might not need to query as many pages... see teh INCLUDE parameter of a CREATE INDEX...

Also, there are some new system views... that can help isolate missing stats/indexes...

select * from sys.dm_db_missing_index_details

select * from sys.dm_db_missing_index_groups

select * from sys.dm_db_missing_index_group_stats

A sample of how to join them follows...

SELECT mig.*, statement AS table_name,

column_id, column_name, column_usage

FROM sys.dm_db_missing_index_details AS mid

CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)

INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle

ORDER BY mig.index_group_handle, mig.index_handle, column_id;

GO

Bruce