DBfile usage: clustered

Showing posts with label clustered. Show all posts

Tuesday, March 27, 2012

DBCC SHRINKDATABASE

We previously had a problem (due to the fact the previous DBA made every
index a non-clustered instead of clustered) that caused our database file
size to grow out of control. We had databases that should have been around
10gb growing to well over 50gb. I made everything a clustered index and
reindex every table and the database size dropped to 30gb but with roughly
20gb of free space. I'm not trying to use the DBCC SHRINKDATABASE command to
reclain that free space and on some database it's dropping the size from 30gb
down to 10gb with a few gb of free space which is great, but on some it's not
reclaiming that space. I've tried using the command with the truncate only
option, specifying like 5 percent free space and I just can't find why on
some databases it will reclaim that space (more success witht he truncate
only option than the others) and other databases it's retaining like 80% of
the database size as free space. I'm using the update usage command as well
to get things in line. Any help would be appreciated. Thanks.
Hi
Try using DBCC SHRINKFILE instead
http://msdn.microsoft.com/library/de..._dbcc_8b51.asp
You may also want to check out other posts where SHRINKDATABASE has not
changed the size such as http://tinyurl.com/uz2om
John
"brogers5884" wrote:

> We previously had a problem (due to the fact the previous DBA made every
> index a non-clustered instead of clustered) that caused our database file
> size to grow out of control. We had databases that should have been around
> 10gb growing to well over 50gb. I made everything a clustered index and
> reindex every table and the database size dropped to 30gb but with roughly
> 20gb of free space. I'm not trying to use the DBCC SHRINKDATABASE command to
> reclain that free space and on some database it's dropping the size from 30gb
> down to 10gb with a few gb of free space which is great, but on some it's not
> reclaiming that space. I've tried using the command with the truncate only
> option, specifying like 5 percent free space and I just can't find why on
> some databases it will reclaim that space (more success witht he truncate
> only option than the others) and other databases it's retaining like 80% of
> the database size as free space. I'm using the update usage command as well
> to get things in line. Any help would be appreciated. Thanks.

DBCC SHRINKDATABASE

We previously had a problem (due to the fact the previous DBA made every
index a non-clustered instead of clustered) that caused our database file
size to grow out of control. We had databases that should have been around
10gb growing to well over 50gb. I made everything a clustered index and
reindex every table and the database size dropped to 30gb but with roughly
20gb of free space. I'm not trying to use the DBCC SHRINKDATABASE command to
reclain that free space and on some database it's dropping the size from 30gb
down to 10gb with a few gb of free space which is great, but on some it's not
reclaiming that space. I've tried using the command with the truncate only
option, specifying like 5 percent free space and I just can't find why on
some databases it will reclaim that space (more success witht he truncate
only option than the others) and other databases it's retaining like 80% of
the database size as free space. I'm using the update usage command as well
to get things in line. Any help would be appreciated. Thanks.Hi
Try using DBCC SHRINKFILE instead
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_8b51.asp
You may also want to check out other posts where SHRINKDATABASE has not
changed the size such as http://tinyurl.com/uz2om
John
"brogers5884" wrote:
> We previously had a problem (due to the fact the previous DBA made every
> index a non-clustered instead of clustered) that caused our database file
> size to grow out of control. We had databases that should have been around
> 10gb growing to well over 50gb. I made everything a clustered index and
> reindex every table and the database size dropped to 30gb but with roughly
> 20gb of free space. I'm not trying to use the DBCC SHRINKDATABASE command to
> reclain that free space and on some database it's dropping the size from 30gb
> down to 10gb with a few gb of free space which is great, but on some it's not
> reclaiming that space. I've tried using the command with the truncate only
> option, specifying like 5 percent free space and I just can't find why on
> some databases it will reclaim that space (more success witht he truncate
> only option than the others) and other databases it's retaining like 80% of
> the database size as free space. I'm using the update usage command as well
> to get things in line. Any help would be appreciated. Thanks.

DBCC SHRINKDATABASE

We previously had a problem (due to the fact the previous DBA made every
index a non-clustered instead of clustered) that caused our database file
size to grow out of control. We had databases that should have been around
10gb growing to well over 50gb. I made everything a clustered index and
reindex every table and the database size dropped to 30gb but with roughly
20gb of free space. I'm not trying to use the DBCC SHRINKDATABASE command t
o
reclain that free space and on some database it's dropping the size from 30g
b
down to 10gb with a few gb of free space which is great, but on some it's no
t
reclaiming that space. I've tried using the command with the truncate only
option, specifying like 5 percent free space and I just can't find why on
some databases it will reclaim that space (more success witht he truncate
only option than the others) and other databases it's retaining like 80% of
the database size as free space. I'm using the update usage command as well
to get things in line. Any help would be appreciated. Thanks.Hi
Try using DBCC SHRINKFILE instead
http://msdn.microsoft.com/library/d...
b51.asp
You may also want to check out other posts where SHRINKDATABASE has not
changed the size such as http://tinyurl.com/uz2om
John
"brogers5884" wrote:

> We previously had a problem (due to the fact the previous DBA made every
> index a non-clustered instead of clustered) that caused our database file
> size to grow out of control. We had databases that should have been aroun
d
> 10gb growing to well over 50gb. I made everything a clustered index and
> reindex every table and the database size dropped to 30gb but with roughly
> 20gb of free space. I'm not trying to use the DBCC SHRINKDATABASE command
to
> reclain that free space and on some database it's dropping the size from 3
0gb
> down to 10gb with a few gb of free space which is great, but on some it's
not
> reclaiming that space. I've tried using the command with the truncate onl
y
> option, specifying like 5 percent free space and I just can't find why on
> some databases it will reclaim that space (more success witht he truncate
> only option than the others) and other databases it's retaining like 80% o
f
> the database size as free space. I'm using the update usage command as we
ll
> to get things in line. Any help would be appreciated. Thanks.

Wednesday, March 21, 2012

DBCC pintable

Hi everyone,
I'm currently running sql 2k on win2k3, all with the
latest patches in a clustered/merge replication
environment. I'm considering running the dbcc pintable
command for one small (~38MB), but very hot table in our
database. I've never used this command before so I was
looking for any direction here or any "gotchas" I should
look out for. Any advice would be appreciated. Thanks.
Leon
Leon,
If the table is small and used a lot, you're not going to get any benefit
from pinning it; it will already be in memory anyway do to its constantly
being accessed. Remember that pinning doesn't tell SQL Server to load the
data into memory; rather, it tells it to keep it in memory once it's been
loaded by something else. And SQL Server will do that anyway if requests
keep coming in for the same data.
"Leon" <anonymous@.discussions.microsoft.com> wrote in message
news:8d6001c49680$46eddaf0$a601280a@.phx.gbl...
> Hi everyone,
> I'm currently running sql 2k on win2k3, all with the
> latest patches in a clustered/merge replication
> environment. I'm considering running the dbcc pintable
> command for one small (~38MB), but very hot table in our
> database. I've never used this command before so I was
> looking for any direction here or any "gotchas" I should
> look out for. Any advice would be appreciated. Thanks.
> Leon
|||I agree 100% with Adam here. Pinning tables usually has a more negative
effect than a positive one since it will keep any data in memory even if it
has only been accessed a single time. SQL Server usually does a much better
job at managing the cache than a human can.
Andrew J. Kelly SQL MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OB5TGSolEHA.3720@.TK2MSFTNGP12.phx.gbl...
> Leon,
> If the table is small and used a lot, you're not going to get any benefit
> from pinning it; it will already be in memory anyway do to its constantly
> being accessed. Remember that pinning doesn't tell SQL Server to load the
> data into memory; rather, it tells it to keep it in memory once it's been
> loaded by something else. And SQL Server will do that anyway if requests
> keep coming in for the same data.
>
> "Leon" <anonymous@.discussions.microsoft.com> wrote in message
> news:8d6001c49680$46eddaf0$a601280a@.phx.gbl...
>

DBCC pintable

Hi everyone,
I'm currently running sql 2k on win2k3, all with the
latest patches in a clustered/merge replication
environment. I'm considering running the dbcc pintable
command for one small (~38MB), but very hot table in our
database. I've never used this command before so I was
looking for any direction here or any "gotchas" I should
look out for. Any advice would be appreciated. Thanks.
LeonLeon,
If the table is small and used a lot, you're not going to get any benefit
from pinning it; it will already be in memory anyway do to its constantly
being accessed. Remember that pinning doesn't tell SQL Server to load the
data into memory; rather, it tells it to keep it in memory once it's been
loaded by something else. And SQL Server will do that anyway if requests
keep coming in for the same data.
"Leon" <anonymous@.discussions.microsoft.com> wrote in message
news:8d6001c49680$46eddaf0$a601280a@.phx.gbl...
> Hi everyone,
> I'm currently running sql 2k on win2k3, all with the
> latest patches in a clustered/merge replication
> environment. I'm considering running the dbcc pintable
> command for one small (~38MB), but very hot table in our
> database. I've never used this command before so I was
> looking for any direction here or any "gotchas" I should
> look out for. Any advice would be appreciated. Thanks.
> Leon|||I agree 100% with Adam here. Pinning tables usually has a more negative
effect than a positive one since it will keep any data in memory even if it
has only been accessed a single time. SQL Server usually does a much better
job at managing the cache than a human can.
--
Andrew J. Kelly SQL MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OB5TGSolEHA.3720@.TK2MSFTNGP12.phx.gbl...
> Leon,
> If the table is small and used a lot, you're not going to get any benefit
> from pinning it; it will already be in memory anyway do to its constantly
> being accessed. Remember that pinning doesn't tell SQL Server to load the
> data into memory; rather, it tells it to keep it in memory once it's been
> loaded by something else. And SQL Server will do that anyway if requests
> keep coming in for the same data.
>
> "Leon" <anonymous@.discussions.microsoft.com> wrote in message
> news:8d6001c49680$46eddaf0$a601280a@.phx.gbl...
> > Hi everyone,
> > I'm currently running sql 2k on win2k3, all with the
> > latest patches in a clustered/merge replication
> > environment. I'm considering running the dbcc pintable
> > command for one small (~38MB), but very hot table in our
> > database. I've never used this command before so I was
> > looking for any direction here or any "gotchas" I should
> > look out for. Any advice would be appreciated. Thanks.
> >
> > Leon
>sql

Wednesday, March 7, 2012

DBCC DBREINDEX unexpected results

Normally, after I use DBCC DBREINDEX, I can be sure that Scan Density on a clustered or non-clustered index is very good - eg. 99% or 100%. However, I have one database where there are a number of indexes that are not showing any improvement in Scan Density after running DBCC DBREINDEX. In on case, a clustered index, I run it on two days in succession and Scan Density actually go worse! Can anyone give me a reason for this? Can anyone suggest how to fix it?

CliveI think I found the problem. Look like a newly modified maintenance proc wasn't doing what it should have been.

Clive

DBCC DBREINDEX failure

When I run the following DBCC command to reindex all indexes (in most cases
only one: clustered) on a table...
DBCC DBREINDEX (<table_name>, '', 10)
It reports the following error:
Could not allocate space for object ... in database ... because the
'PRIMARY' filegroup is full.
This is strange as the primary filegroup, which consists of three physical
files spread across three physical drives, have ample space for the database
to grow. This had been verified by updating the stats and running
sp_spacesused.
The reindexing had originally been done through the once weekly DB
optimization job, but after repeated failures due to the log file being full,
I decided to separate the reindexing of all tables into specific groups, by
running them in batches, once a day over the week, with no batch repeated
more than once a week.
That seems to have resolved the log file full issue, but I still see the
above error when the reindexing occurs against larger tables.
The database has grown to twice its size since I've replace the Opt. job
with manual DBCC DBREINDEX.
The only DB options set on the database are:
trunc. log on chkpt. (SIMPLE recovery mode),
torn page detection,
auto create statistics, and
auto update statistics
Any ideas what I could be doing wrong here?
TIA.Repeat post. My apologies... it seemed that my original post had gone into
limbo (timeout issue) so I had reposted.
"Rob" wrote:
> When I run the following DBCC command to reindex all indexes (in most cases
> only one: clustered) on a table...
> DBCC DBREINDEX (<table_name>, '', 10)
> It reports the following error:
> Could not allocate space for object ... in database ... because the
> 'PRIMARY' filegroup is full.
> This is strange as the primary filegroup, which consists of three physical
> files spread across three physical drives, have ample space for the database
> to grow. This had been verified by updating the stats and running
> sp_spacesused.
> The reindexing had originally been done through the once weekly DB
> optimization job, but after repeated failures due to the log file being full,
> I decided to separate the reindexing of all tables into specific groups, by
> running them in batches, once a day over the week, with no batch repeated
> more than once a week.
> That seems to have resolved the log file full issue, but I still see the
> above error when the reindexing occurs against larger tables.
> The database has grown to twice its size since I've replace the Opt. job
> with manual DBCC DBREINDEX.
> The only DB options set on the database are:
> trunc. log on chkpt. (SIMPLE recovery mode),
> torn page detection,
> auto create statistics, and
> auto update statistics
> Any ideas what I could be doing wrong here?
> TIA.|||Rob,
The reindex function will need data + (2 x Index space) to create an index.
You might want to examine the SORT_IN_TEMPDB option of the CREATE INDEX
statement, and look at dropping and recreating the indexes instead of the
DBCC DBREINDEX statement.
-- Bill
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:6F079FC8-012F-4E24-8E07-645F1A197B25@.microsoft.com...
> Repeat post. My apologies... it seemed that my original post had gone into
> limbo (timeout issue) so I had reposted.
> "Rob" wrote:
>> When I run the following DBCC command to reindex all indexes (in most
>> cases
>> only one: clustered) on a table...
>> DBCC DBREINDEX (<table_name>, '', 10)
>> It reports the following error:
>> Could not allocate space for object ... in database ... because the
>> 'PRIMARY' filegroup is full.
>> This is strange as the primary filegroup, which consists of three
>> physical
>> files spread across three physical drives, have ample space for the
>> database
>> to grow. This had been verified by updating the stats and running
>> sp_spacesused.
>> The reindexing had originally been done through the once weekly DB
>> optimization job, but after repeated failures due to the log file being
>> full,
>> I decided to separate the reindexing of all tables into specific groups,
>> by
>> running them in batches, once a day over the week, with no batch repeated
>> more than once a week.
>> That seems to have resolved the log file full issue, but I still see the
>> above error when the reindexing occurs against larger tables.
>> The database has grown to twice its size since I've replace the Opt. job
>> with manual DBCC DBREINDEX.
>> The only DB options set on the database are:
>> trunc. log on chkpt. (SIMPLE recovery mode),
>> torn page detection,
>> auto create statistics, and
>> auto update statistics
>> Any ideas what I could be doing wrong here?
>> TIA.|||Thanks Bill.
I've been wondering if reindexing would do any good at all given that most
of my tables only have one clustered index. If it's clustered then there
shouldn't be any reason to drop and recreate the index, right... as data and
index pages are always kept in a sorted order.
"AlterEgo" wrote:
> Rob,
> The reindex function will need data + (2 x Index space) to create an index.
> You might want to examine the SORT_IN_TEMPDB option of the CREATE INDEX
> statement, and look at dropping and recreating the indexes instead of the
> DBCC DBREINDEX statement.
> -- Bill
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:6F079FC8-012F-4E24-8E07-645F1A197B25@.microsoft.com...
> > Repeat post. My apologies... it seemed that my original post had gone into
> > limbo (timeout issue) so I had reposted.
> >
> > "Rob" wrote:
> >
> >> When I run the following DBCC command to reindex all indexes (in most
> >> cases
> >> only one: clustered) on a table...
> >>
> >> DBCC DBREINDEX (<table_name>, '', 10)
> >>
> >> It reports the following error:
> >>
> >> Could not allocate space for object ... in database ... because the
> >> 'PRIMARY' filegroup is full.
> >>
> >> This is strange as the primary filegroup, which consists of three
> >> physical
> >> files spread across three physical drives, have ample space for the
> >> database
> >> to grow. This had been verified by updating the stats and running
> >> sp_spacesused.
> >>
> >> The reindexing had originally been done through the once weekly DB
> >> optimization job, but after repeated failures due to the log file being
> >> full,
> >> I decided to separate the reindexing of all tables into specific groups,
> >> by
> >> running them in batches, once a day over the week, with no batch repeated
> >> more than once a week.
> >>
> >> That seems to have resolved the log file full issue, but I still see the
> >> above error when the reindexing occurs against larger tables.
> >>
> >> The database has grown to twice its size since I've replace the Opt. job
> >> with manual DBCC DBREINDEX.
> >>
> >> The only DB options set on the database are:
> >>
> >> trunc. log on chkpt. (SIMPLE recovery mode),
> >> torn page detection,
> >> auto create statistics, and
> >> auto update statistics
> >>
> >> Any ideas what I could be doing wrong here?
> >>
> >> TIA.
>
>|||> as data and
> index pages are always kept in a sorted order.
Logically, yes, the linked list is logically sorted. But as you follow the pointers in the linked
list, you can jump back and forth ion the disk. On other words, a clustered index can definitely be
fragmented. Whether or not you suffer from the fragmentation, and how much is another question:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:6234B4EC-CF94-4DC8-9C2E-AF12AC12CB57@.microsoft.com...
> Thanks Bill.
> I've been wondering if reindexing would do any good at all given that most
> of my tables only have one clustered index. If it's clustered then there
> shouldn't be any reason to drop and recreate the index, right... as data and
> index pages are always kept in a sorted order.
> "AlterEgo" wrote:
>> Rob,
>> The reindex function will need data + (2 x Index space) to create an index.
>> You might want to examine the SORT_IN_TEMPDB option of the CREATE INDEX
>> statement, and look at dropping and recreating the indexes instead of the
>> DBCC DBREINDEX statement.
>> -- Bill
>> "Rob" <Rob@.discussions.microsoft.com> wrote in message
>> news:6F079FC8-012F-4E24-8E07-645F1A197B25@.microsoft.com...
>> > Repeat post. My apologies... it seemed that my original post had gone into
>> > limbo (timeout issue) so I had reposted.
>> >
>> > "Rob" wrote:
>> >
>> >> When I run the following DBCC command to reindex all indexes (in most
>> >> cases
>> >> only one: clustered) on a table...
>> >>
>> >> DBCC DBREINDEX (<table_name>, '', 10)
>> >>
>> >> It reports the following error:
>> >>
>> >> Could not allocate space for object ... in database ... because the
>> >> 'PRIMARY' filegroup is full.
>> >>
>> >> This is strange as the primary filegroup, which consists of three
>> >> physical
>> >> files spread across three physical drives, have ample space for the
>> >> database
>> >> to grow. This had been verified by updating the stats and running
>> >> sp_spacesused.
>> >>
>> >> The reindexing had originally been done through the once weekly DB
>> >> optimization job, but after repeated failures due to the log file being
>> >> full,
>> >> I decided to separate the reindexing of all tables into specific groups,
>> >> by
>> >> running them in batches, once a day over the week, with no batch repeated
>> >> more than once a week.
>> >>
>> >> That seems to have resolved the log file full issue, but I still see the
>> >> above error when the reindexing occurs against larger tables.
>> >>
>> >> The database has grown to twice its size since I've replace the Opt. job
>> >> with manual DBCC DBREINDEX.
>> >>
>> >> The only DB options set on the database are:
>> >>
>> >> trunc. log on chkpt. (SIMPLE recovery mode),
>> >> torn page detection,
>> >> auto create statistics, and
>> >> auto update statistics
>> >>
>> >> Any ideas what I could be doing wrong here?
>> >>
>> >> TIA.
>>|||Thanks Tibor.
Assumming that we choose not to run reindexing, is there a way to reclaim
the space chewed up by the indexes, without dropping the indexes?
"Tibor Karaszi" wrote:
> > as data and
> > index pages are always kept in a sorted order.
> Logically, yes, the linked list is logically sorted. But as you follow the pointers in the linked
> list, you can jump back and forth ion the disk. On other words, a clustered index can definitely be
> fragmented. Whether or not you suffer from the fragmentation, and how much is another question:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:6234B4EC-CF94-4DC8-9C2E-AF12AC12CB57@.microsoft.com...
> > Thanks Bill.
> >
> > I've been wondering if reindexing would do any good at all given that most
> > of my tables only have one clustered index. If it's clustered then there
> > shouldn't be any reason to drop and recreate the index, right... as data and
> > index pages are always kept in a sorted order.
> >
> > "AlterEgo" wrote:
> >
> >> Rob,
> >>
> >> The reindex function will need data + (2 x Index space) to create an index.
> >> You might want to examine the SORT_IN_TEMPDB option of the CREATE INDEX
> >> statement, and look at dropping and recreating the indexes instead of the
> >> DBCC DBREINDEX statement.
> >>
> >> -- Bill
> >>
> >> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> >> news:6F079FC8-012F-4E24-8E07-645F1A197B25@.microsoft.com...
> >> > Repeat post. My apologies... it seemed that my original post had gone into
> >> > limbo (timeout issue) so I had reposted.
> >> >
> >> > "Rob" wrote:
> >> >
> >> >> When I run the following DBCC command to reindex all indexes (in most
> >> >> cases
> >> >> only one: clustered) on a table...
> >> >>
> >> >> DBCC DBREINDEX (<table_name>, '', 10)
> >> >>
> >> >> It reports the following error:
> >> >>
> >> >> Could not allocate space for object ... in database ... because the
> >> >> 'PRIMARY' filegroup is full.
> >> >>
> >> >> This is strange as the primary filegroup, which consists of three
> >> >> physical
> >> >> files spread across three physical drives, have ample space for the
> >> >> database
> >> >> to grow. This had been verified by updating the stats and running
> >> >> sp_spacesused.
> >> >>
> >> >> The reindexing had originally been done through the once weekly DB
> >> >> optimization job, but after repeated failures due to the log file being
> >> >> full,
> >> >> I decided to separate the reindexing of all tables into specific groups,
> >> >> by
> >> >> running them in batches, once a day over the week, with no batch repeated
> >> >> more than once a week.
> >> >>
> >> >> That seems to have resolved the log file full issue, but I still see the
> >> >> above error when the reindexing occurs against larger tables.
> >> >>
> >> >> The database has grown to twice its size since I've replace the Opt. job
> >> >> with manual DBCC DBREINDEX.
> >> >>
> >> >> The only DB options set on the database are:
> >> >>
> >> >> trunc. log on chkpt. (SIMPLE recovery mode),
> >> >> torn page detection,
> >> >> auto create statistics, and
> >> >> auto update statistics
> >> >>
> >> >> Any ideas what I could be doing wrong here?
> >> >>
> >> >> TIA.
> >>
> >>
> >>
>|||I'm not sure what you mean by "space chewed up by the indexes".
Perhaps you refer to "internal fragmentation", where page splits in an index lead to less than 100%
full pages? Internal fragmentation doesn't have to be bad, because a page will have room for some
rows before the next page split. This is why we have the FILLFACTOR option. But having pages not
full lead to more pages, of course.
I recommend you read the white paper I referred to. It has good details and recommendations.
The ways to "reorganize" an index are:
DBCC DBREINDEX
DBCC INDEXDEFRAG
Export, empty table, import (preferably data in same order as index).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:A12B55E8-950E-4E6F-BAFE-40EF61C51C20@.microsoft.com...
> Thanks Tibor.
> Assumming that we choose not to run reindexing, is there a way to reclaim
> the space chewed up by the indexes, without dropping the indexes?
> "Tibor Karaszi" wrote:
>> > as data and
>> > index pages are always kept in a sorted order.
>> Logically, yes, the linked list is logically sorted. But as you follow the pointers in the linked
>> list, you can jump back and forth ion the disk. On other words, a clustered index can definitely
>> be
>> fragmented. Whether or not you suffer from the fragmentation, and how much is another question:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Rob" <Rob@.discussions.microsoft.com> wrote in message
>> news:6234B4EC-CF94-4DC8-9C2E-AF12AC12CB57@.microsoft.com...
>> > Thanks Bill.
>> >
>> > I've been wondering if reindexing would do any good at all given that most
>> > of my tables only have one clustered index. If it's clustered then there
>> > shouldn't be any reason to drop and recreate the index, right... as data and
>> > index pages are always kept in a sorted order.
>> >
>> > "AlterEgo" wrote:
>> >
>> >> Rob,
>> >>
>> >> The reindex function will need data + (2 x Index space) to create an index.
>> >> You might want to examine the SORT_IN_TEMPDB option of the CREATE INDEX
>> >> statement, and look at dropping and recreating the indexes instead of the
>> >> DBCC DBREINDEX statement.
>> >>
>> >> -- Bill
>> >>
>> >> "Rob" <Rob@.discussions.microsoft.com> wrote in message
>> >> news:6F079FC8-012F-4E24-8E07-645F1A197B25@.microsoft.com...
>> >> > Repeat post. My apologies... it seemed that my original post had gone into
>> >> > limbo (timeout issue) so I had reposted.
>> >> >
>> >> > "Rob" wrote:
>> >> >
>> >> >> When I run the following DBCC command to reindex all indexes (in most
>> >> >> cases
>> >> >> only one: clustered) on a table...
>> >> >>
>> >> >> DBCC DBREINDEX (<table_name>, '', 10)
>> >> >>
>> >> >> It reports the following error:
>> >> >>
>> >> >> Could not allocate space for object ... in database ... because the
>> >> >> 'PRIMARY' filegroup is full.
>> >> >>
>> >> >> This is strange as the primary filegroup, which consists of three
>> >> >> physical
>> >> >> files spread across three physical drives, have ample space for the
>> >> >> database
>> >> >> to grow. This had been verified by updating the stats and running
>> >> >> sp_spacesused.
>> >> >>
>> >> >> The reindexing had originally been done through the once weekly DB
>> >> >> optimization job, but after repeated failures due to the log file being
>> >> >> full,
>> >> >> I decided to separate the reindexing of all tables into specific groups,
>> >> >> by
>> >> >> running them in batches, once a day over the week, with no batch repeated
>> >> >> more than once a week.
>> >> >>
>> >> >> That seems to have resolved the log file full issue, but I still see the
>> >> >> above error when the reindexing occurs against larger tables.
>> >> >>
>> >> >> The database has grown to twice its size since I've replace the Opt. job
>> >> >> with manual DBCC DBREINDEX.
>> >> >>
>> >> >> The only DB options set on the database are:
>> >> >>
>> >> >> trunc. log on chkpt. (SIMPLE recovery mode),
>> >> >> torn page detection,
>> >> >> auto create statistics, and
>> >> >> auto update statistics
>> >> >>
>> >> >> Any ideas what I could be doing wrong here?
>> >> >>
>> >> >> TIA.
>> >>
>> >>
>> >>
>>

DBCC DBREINDEX Behaviour

Folks,
I have a table with a clustered index and 6 non-clustered indexes. When I
issue a DBCC DBREINDEX without specifying a specific index all indexes are
rebuilt including the clustered index - this takes 3 hours to complete and
consumes 9gb of TLog space. When I issue a DBCC DBREINDEX specifying only the
clustered index (which happens to be supporting the primary key) I've read
that all non-clustered indexes are also rebuilt - this takes on 1.5 hours and
consumes 6gb of Tlog space. I have 3 questions:
1) If you use DBCC DBREINDEX without specifying an index, are the
non-clustered indexes rebuilt twice on a table with a clustered index?
2) I would like to confirm that all indexes are being rebuilt. Is the time
the index is created captured, if so, how can I view the time?
3) Why is this a logged transaction? I would have thought that SQL Server
would create the indexes without dropping the originals, and then simply swap
and drop once the index has completed.
Thanks in advance for your help.
--
Scott H.It really helps to specify what version and service pack you are using. In
this case it can make a big difference. If I remember correctly in the RTM
and maybe SP1 versions of SQL2000 it worked like this:
If you rebuild the clustered index ( CI ) all non-clustered indexes ( NCI)
were always rebuilt as well. This was due to the way in which the CI key was
appended to the end of all NCI's and would change during a rebuild.
With one of the SP's (I think SP2) that behavior changed in that if the CI
was unique and you rebuilt the CI by specifying only that index it did not
rebuild the NCI's. But if the CI was not unique they would add a uniquifer
(4 byte code) to the CI which got regenerated each time the CI was rebuilt.
Since the CI (including the uniqueifier) was appended to the end of all
NCI's they in turn needed to be rebuilt as well.
IN SQL2005 they changed the way they generated the uniqifier and it no
longer changes when the CI is rebuilt. So there is no need to rebuild all
the NCI's just because you rebuild the CI.
But to answer your specific questions a little more:
> 1) If you use DBCC DBREINDEX without specifying an index, are the
> non-clustered indexes rebuilt twice on a table with a clustered index?
No. SQL Server was smart enough in all versions to only rebuild the NCI's
once.
> 2) I would like to confirm that all indexes are being rebuilt. Is the time
> the index is created captured, if so, how can I view the time?
No but you can look at DBCC SHOWCONTIG or SHOWSTATISTICS before and after
you can see the differences and will see where to look to see if work was
done.
> 3) Why is this a logged transaction? I would have thought that SQL Server
> would create the indexes without dropping the originals, and then simply
> swap
> and drop once the index has completed.
If you are in FULL recovery mode everything is always fully logged. If you
are in Bulk-Logged or Simple mode some index create or rebuild operations
can be minimally logged. So your transaction log file may not grow very much
but when you backup the log file (if in Bulk-logged) the backup will include
all the extents changed byt he operations.
--
Andrew J. Kelly SQL MVP
"Scott H." <ScottH@.discussions.microsoft.com> wrote in message
news:A76DD6A4-4D1D-40F2-AC8E-F94C9042F42C@.microsoft.com...
> Folks,
> I have a table with a clustered index and 6 non-clustered indexes. When I
> issue a DBCC DBREINDEX without specifying a specific index all indexes are
> rebuilt including the clustered index - this takes 3 hours to complete and
> consumes 9gb of TLog space. When I issue a DBCC DBREINDEX specifying only
> the
> clustered index (which happens to be supporting the primary key) I've read
> that all non-clustered indexes are also rebuilt - this takes on 1.5 hours
> and
> consumes 6gb of Tlog space. I have 3 questions:
> 1) If you use DBCC DBREINDEX without specifying an index, are the
> non-clustered indexes rebuilt twice on a table with a clustered index?
> 2) I would like to confirm that all indexes are being rebuilt. Is the time
> the index is created captured, if so, how can I view the time?
> 3) Why is this a logged transaction? I would have thought that SQL Server
> would create the indexes without dropping the originals, and then simply
> swap
> and drop once the index has completed.
> Thanks in advance for your help.
> --
> Scott H.|||Thanks Andrew.
SQL Server 2000 SP3
The CI is unique, so it would appear to be rebuilding only the CI.
I re-ran the test in simple recovery mode, and the TLOG grew to only 190mb.
I guess in production I could put the server into single user mode, change to
simple recovery mode, run the reorg, full backup, return to full rcovery
mode. Seems like overkill. The reason I may follow this route is that we are
having disk capacity issues.
I have an application team thinking that rebuilding (DBREINDEX) this 17
million record table is a good thing to do nightly 7 days/week. I need to dig
up supporting evidence one way or the other. I'll make use of SHOWCONTIG
prior to the execution over the next few days to determine. If there is
anything else you think could be beneficial feel free to offer. I've recently
inherited the support of this instaance and there is much work to do.
Thanks for your help.
--
Thanks,
Scott H.
"Andrew J. Kelly" wrote:
> It really helps to specify what version and service pack you are using. In
> this case it can make a big difference. If I remember correctly in the RTM
> and maybe SP1 versions of SQL2000 it worked like this:
> If you rebuild the clustered index ( CI ) all non-clustered indexes ( NCI)
> were always rebuilt as well. This was due to the way in which the CI key was
> appended to the end of all NCI's and would change during a rebuild.
> With one of the SP's (I think SP2) that behavior changed in that if the CI
> was unique and you rebuilt the CI by specifying only that index it did not
> rebuild the NCI's. But if the CI was not unique they would add a uniquifer
> (4 byte code) to the CI which got regenerated each time the CI was rebuilt.
> Since the CI (including the uniqueifier) was appended to the end of all
> NCI's they in turn needed to be rebuilt as well.
> IN SQL2005 they changed the way they generated the uniqifier and it no
> longer changes when the CI is rebuilt. So there is no need to rebuild all
> the NCI's just because you rebuild the CI.
> But to answer your specific questions a little more:
> > 1) If you use DBCC DBREINDEX without specifying an index, are the
> > non-clustered indexes rebuilt twice on a table with a clustered index?
> No. SQL Server was smart enough in all versions to only rebuild the NCI's
> once.
> > 2) I would like to confirm that all indexes are being rebuilt. Is the time
> > the index is created captured, if so, how can I view the time?
> No but you can look at DBCC SHOWCONTIG or SHOWSTATISTICS before and after
> you can see the differences and will see where to look to see if work was
> done.
> > 3) Why is this a logged transaction? I would have thought that SQL Server
> > would create the indexes without dropping the originals, and then simply
> > swap
> > and drop once the index has completed.
> If you are in FULL recovery mode everything is always fully logged. If you
> are in Bulk-Logged or Simple mode some index create or rebuild operations
> can be minimally logged. So your transaction log file may not grow very much
> but when you backup the log file (if in Bulk-logged) the backup will include
> all the extents changed byt he operations.
> --
> Andrew J. Kelly SQL MVP
> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
> news:A76DD6A4-4D1D-40F2-AC8E-F94C9042F42C@.microsoft.com...
> > Folks,
> >
> > I have a table with a clustered index and 6 non-clustered indexes. When I
> > issue a DBCC DBREINDEX without specifying a specific index all indexes are
> > rebuilt including the clustered index - this takes 3 hours to complete and
> > consumes 9gb of TLog space. When I issue a DBCC DBREINDEX specifying only
> > the
> > clustered index (which happens to be supporting the primary key) I've read
> > that all non-clustered indexes are also rebuilt - this takes on 1.5 hours
> > and
> > consumes 6gb of Tlog space. I have 3 questions:
> >
> > 1) If you use DBCC DBREINDEX without specifying an index, are the
> > non-clustered indexes rebuilt twice on a table with a clustered index?
> >
> > 2) I would like to confirm that all indexes are being rebuilt. Is the time
> > the index is created captured, if so, how can I view the time?
> >
> > 3) Why is this a logged transaction? I would have thought that SQL Server
> > would create the indexes without dropping the originals, and then simply
> > swap
> > and drop once the index has completed.
> >
> > Thanks in advance for your help.
> > --
> > Scott H.
>
>|||Hi Scott
You might want to consider switching to bulk_logged mode instead of simple.
The logging should be about the same, and you won't have to do a full db
backup after, on a tlog backup. Situations like this are exactly what
bulk_logged mode is intended for, i.e. so that you can do large bulk
operations, like data loads or index rebuilds, that normally are log
intensive, and make then less log intensive. Switching to bulk_logged mode
allows your chain of tlog backups to remain intact and again, no full backup
is required.
You might want to read more details about the different recovery models in
the Books Online and also this KB article might help:
A transaction log grows unexpectedly or becomes full on a computer that is
running SQL Server
http://support.microsoft.com/kb/317375/en-us
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Scott H." <ScottH@.discussions.microsoft.com> wrote in message
news:6355DB27-5D19-4A8F-A32D-5A2588D275D7@.microsoft.com...
> Thanks Andrew.
> SQL Server 2000 SP3
> The CI is unique, so it would appear to be rebuilding only the CI.
> I re-ran the test in simple recovery mode, and the TLOG grew to only
> 190mb.
> I guess in production I could put the server into single user mode, change
> to
> simple recovery mode, run the reorg, full backup, return to full rcovery
> mode. Seems like overkill. The reason I may follow this route is that we
> are
> having disk capacity issues.
> I have an application team thinking that rebuilding (DBREINDEX) this 17
> million record table is a good thing to do nightly 7 days/week. I need to
> dig
> up supporting evidence one way or the other. I'll make use of SHOWCONTIG
> prior to the execution over the next few days to determine. If there is
> anything else you think could be beneficial feel free to offer. I've
> recently
> inherited the support of this instaance and there is much work to do.
> Thanks for your help.
> --
> Thanks,
> Scott H.
>
> "Andrew J. Kelly" wrote:
>> It really helps to specify what version and service pack you are using.
>> In
>> this case it can make a big difference. If I remember correctly in the
>> RTM
>> and maybe SP1 versions of SQL2000 it worked like this:
>> If you rebuild the clustered index ( CI ) all non-clustered indexes (
>> NCI)
>> were always rebuilt as well. This was due to the way in which the CI key
>> was
>> appended to the end of all NCI's and would change during a rebuild.
>> With one of the SP's (I think SP2) that behavior changed in that if the
>> CI
>> was unique and you rebuilt the CI by specifying only that index it did
>> not
>> rebuild the NCI's. But if the CI was not unique they would add a
>> uniquifer
>> (4 byte code) to the CI which got regenerated each time the CI was
>> rebuilt.
>> Since the CI (including the uniqueifier) was appended to the end of all
>> NCI's they in turn needed to be rebuilt as well.
>> IN SQL2005 they changed the way they generated the uniqifier and it no
>> longer changes when the CI is rebuilt. So there is no need to rebuild all
>> the NCI's just because you rebuild the CI.
>> But to answer your specific questions a little more:
>> > 1) If you use DBCC DBREINDEX without specifying an index, are the
>> > non-clustered indexes rebuilt twice on a table with a clustered index?
>> No. SQL Server was smart enough in all versions to only rebuild the NCI's
>> once.
>> > 2) I would like to confirm that all indexes are being rebuilt. Is the
>> > time
>> > the index is created captured, if so, how can I view the time?
>> No but you can look at DBCC SHOWCONTIG or SHOWSTATISTICS before and after
>> you can see the differences and will see where to look to see if work was
>> done.
>> > 3) Why is this a logged transaction? I would have thought that SQL
>> > Server
>> > would create the indexes without dropping the originals, and then
>> > simply
>> > swap
>> > and drop once the index has completed.
>> If you are in FULL recovery mode everything is always fully logged. If
>> you
>> are in Bulk-Logged or Simple mode some index create or rebuild operations
>> can be minimally logged. So your transaction log file may not grow very
>> much
>> but when you backup the log file (if in Bulk-logged) the backup will
>> include
>> all the extents changed byt he operations.
>> --
>> Andrew J. Kelly SQL MVP
>> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
>> news:A76DD6A4-4D1D-40F2-AC8E-F94C9042F42C@.microsoft.com...
>> > Folks,
>> >
>> > I have a table with a clustered index and 6 non-clustered indexes. When
>> > I
>> > issue a DBCC DBREINDEX without specifying a specific index all indexes
>> > are
>> > rebuilt including the clustered index - this takes 3 hours to complete
>> > and
>> > consumes 9gb of TLog space. When I issue a DBCC DBREINDEX specifying
>> > only
>> > the
>> > clustered index (which happens to be supporting the primary key) I've
>> > read
>> > that all non-clustered indexes are also rebuilt - this takes on 1.5
>> > hours
>> > and
>> > consumes 6gb of Tlog space. I have 3 questions:
>> >
>> > 1) If you use DBCC DBREINDEX without specifying an index, are the
>> > non-clustered indexes rebuilt twice on a table with a clustered index?
>> >
>> > 2) I would like to confirm that all indexes are being rebuilt. Is the
>> > time
>> > the index is created captured, if so, how can I view the time?
>> >
>> > 3) Why is this a logged transaction? I would have thought that SQL
>> > Server
>> > would create the indexes without dropping the originals, and then
>> > simply
>> > swap
>> > and drop once the index has completed.
>> >
>> > Thanks in advance for your help.
>> > --
>> > Scott H.
>>|||Scott,
I second Kalen's advise about using Bulklogged vs Simple if you need to go
that way. Just remember that the Log backup files will be large regardless
if you have space issues. Hopefully you are not backing up to the same drive
array as the data is on anyway. But most systems rarely require an index
rebuild nightly and certainly not for all tables in the db. There is a
sample script in BooksOnLine under DBCC SHOWCONTIG that will allow you to
only reindex or Defrag the indexes that are above a certain fragmentation
level anyway. This should dramatically cut down the tlog space requirements
as well. These articles (especially the first one) are worth reading.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Index Defrag Best Practices 2000
http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
Understanding DBCC SHOWCONTIG
http://www.sqlservercentral.com/columnists/jweisbecker/amethodologyfordeterminingfillfactors.asp
Fill Factors
http://www.sql-server-performance.com/gv_clustered_indexes.asp
Clustered Indexes
--
Andrew J. Kelly SQL MVP
"Scott H." <ScottH@.discussions.microsoft.com> wrote in message
news:6355DB27-5D19-4A8F-A32D-5A2588D275D7@.microsoft.com...
> Thanks Andrew.
> SQL Server 2000 SP3
> The CI is unique, so it would appear to be rebuilding only the CI.
> I re-ran the test in simple recovery mode, and the TLOG grew to only
> 190mb.
> I guess in production I could put the server into single user mode, change
> to
> simple recovery mode, run the reorg, full backup, return to full rcovery
> mode. Seems like overkill. The reason I may follow this route is that we
> are
> having disk capacity issues.
> I have an application team thinking that rebuilding (DBREINDEX) this 17
> million record table is a good thing to do nightly 7 days/week. I need to
> dig
> up supporting evidence one way or the other. I'll make use of SHOWCONTIG
> prior to the execution over the next few days to determine. If there is
> anything else you think could be beneficial feel free to offer. I've
> recently
> inherited the support of this instaance and there is much work to do.
> Thanks for your help.
> --
> Thanks,
> Scott H.
>
> "Andrew J. Kelly" wrote:
>> It really helps to specify what version and service pack you are using.
>> In
>> this case it can make a big difference. If I remember correctly in the
>> RTM
>> and maybe SP1 versions of SQL2000 it worked like this:
>> If you rebuild the clustered index ( CI ) all non-clustered indexes (
>> NCI)
>> were always rebuilt as well. This was due to the way in which the CI key
>> was
>> appended to the end of all NCI's and would change during a rebuild.
>> With one of the SP's (I think SP2) that behavior changed in that if the
>> CI
>> was unique and you rebuilt the CI by specifying only that index it did
>> not
>> rebuild the NCI's. But if the CI was not unique they would add a
>> uniquifer
>> (4 byte code) to the CI which got regenerated each time the CI was
>> rebuilt.
>> Since the CI (including the uniqueifier) was appended to the end of all
>> NCI's they in turn needed to be rebuilt as well.
>> IN SQL2005 they changed the way they generated the uniqifier and it no
>> longer changes when the CI is rebuilt. So there is no need to rebuild all
>> the NCI's just because you rebuild the CI.
>> But to answer your specific questions a little more:
>> > 1) If you use DBCC DBREINDEX without specifying an index, are the
>> > non-clustered indexes rebuilt twice on a table with a clustered index?
>> No. SQL Server was smart enough in all versions to only rebuild the NCI's
>> once.
>> > 2) I would like to confirm that all indexes are being rebuilt. Is the
>> > time
>> > the index is created captured, if so, how can I view the time?
>> No but you can look at DBCC SHOWCONTIG or SHOWSTATISTICS before and after
>> you can see the differences and will see where to look to see if work was
>> done.
>> > 3) Why is this a logged transaction? I would have thought that SQL
>> > Server
>> > would create the indexes without dropping the originals, and then
>> > simply
>> > swap
>> > and drop once the index has completed.
>> If you are in FULL recovery mode everything is always fully logged. If
>> you
>> are in Bulk-Logged or Simple mode some index create or rebuild operations
>> can be minimally logged. So your transaction log file may not grow very
>> much
>> but when you backup the log file (if in Bulk-logged) the backup will
>> include
>> all the extents changed byt he operations.
>> --
>> Andrew J. Kelly SQL MVP
>> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
>> news:A76DD6A4-4D1D-40F2-AC8E-F94C9042F42C@.microsoft.com...
>> > Folks,
>> >
>> > I have a table with a clustered index and 6 non-clustered indexes. When
>> > I
>> > issue a DBCC DBREINDEX without specifying a specific index all indexes
>> > are
>> > rebuilt including the clustered index - this takes 3 hours to complete
>> > and
>> > consumes 9gb of TLog space. When I issue a DBCC DBREINDEX specifying
>> > only
>> > the
>> > clustered index (which happens to be supporting the primary key) I've
>> > read
>> > that all non-clustered indexes are also rebuilt - this takes on 1.5
>> > hours
>> > and
>> > consumes 6gb of Tlog space. I have 3 questions:
>> >
>> > 1) If you use DBCC DBREINDEX without specifying an index, are the
>> > non-clustered indexes rebuilt twice on a table with a clustered index?
>> >
>> > 2) I would like to confirm that all indexes are being rebuilt. Is the
>> > time
>> > the index is created captured, if so, how can I view the time?
>> >
>> > 3) Why is this a logged transaction? I would have thought that SQL
>> > Server
>> > would create the indexes without dropping the originals, and then
>> > simply
>> > swap
>> > and drop once the index has completed.
>> >
>> > Thanks in advance for your help.
>> > --
>> > Scott H.
>>|||If your team still doesn't believe you, send me email (through the blog
below) and I'll have a con-call with you/them and convince them (I wrote
DBCC INDEXDEFRAG and SHOWCONTIG).
Cheers
--
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eHLY5EwbHHA.4176@.TK2MSFTNGP02.phx.gbl...
> Scott,
> I second Kalen's advise about using Bulklogged vs Simple if you need to go
> that way. Just remember that the Log backup files will be large regardless
> if you have space issues. Hopefully you are not backing up to the same
> drive array as the data is on anyway. But most systems rarely require an
> index rebuild nightly and certainly not for all tables in the db. There is
> a sample script in BooksOnLine under DBCC SHOWCONTIG that will allow you
> to only reindex or Defrag the indexes that are above a certain
> fragmentation level anyway. This should dramatically cut down the tlog
> space requirements as well. These articles (especially the first one) are
> worth reading.
>
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> Index Defrag Best Practices 2000
> http://www.sql-server-performance.com/dt_dbcc_showcontig.asp Understanding
> DBCC SHOWCONTIG
> http://www.sqlservercentral.com/columnists/jweisbecker/amethodologyfordeterminingfillfactors.asp
> Fill Factors
> http://www.sql-server-performance.com/gv_clustered_indexes.asp Clustered
> Indexes
> --
> Andrew J. Kelly SQL MVP
> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
> news:6355DB27-5D19-4A8F-A32D-5A2588D275D7@.microsoft.com...
>> Thanks Andrew.
>> SQL Server 2000 SP3
>> The CI is unique, so it would appear to be rebuilding only the CI.
>> I re-ran the test in simple recovery mode, and the TLOG grew to only
>> 190mb.
>> I guess in production I could put the server into single user mode,
>> change to
>> simple recovery mode, run the reorg, full backup, return to full rcovery
>> mode. Seems like overkill. The reason I may follow this route is that we
>> are
>> having disk capacity issues.
>> I have an application team thinking that rebuilding (DBREINDEX) this 17
>> million record table is a good thing to do nightly 7 days/week. I need to
>> dig
>> up supporting evidence one way or the other. I'll make use of SHOWCONTIG
>> prior to the execution over the next few days to determine. If there is
>> anything else you think could be beneficial feel free to offer. I've
>> recently
>> inherited the support of this instaance and there is much work to do.
>> Thanks for your help.
>> --
>> Thanks,
>> Scott H.
>>
>> "Andrew J. Kelly" wrote:
>> It really helps to specify what version and service pack you are using.
>> In
>> this case it can make a big difference. If I remember correctly in the
>> RTM
>> and maybe SP1 versions of SQL2000 it worked like this:
>> If you rebuild the clustered index ( CI ) all non-clustered indexes (
>> NCI)
>> were always rebuilt as well. This was due to the way in which the CI key
>> was
>> appended to the end of all NCI's and would change during a rebuild.
>> With one of the SP's (I think SP2) that behavior changed in that if the
>> CI
>> was unique and you rebuilt the CI by specifying only that index it did
>> not
>> rebuild the NCI's. But if the CI was not unique they would add a
>> uniquifer
>> (4 byte code) to the CI which got regenerated each time the CI was
>> rebuilt.
>> Since the CI (including the uniqueifier) was appended to the end of all
>> NCI's they in turn needed to be rebuilt as well.
>> IN SQL2005 they changed the way they generated the uniqifier and it no
>> longer changes when the CI is rebuilt. So there is no need to rebuild
>> all
>> the NCI's just because you rebuild the CI.
>> But to answer your specific questions a little more:
>> > 1) If you use DBCC DBREINDEX without specifying an index, are the
>> > non-clustered indexes rebuilt twice on a table with a clustered index?
>> No. SQL Server was smart enough in all versions to only rebuild the
>> NCI's
>> once.
>> > 2) I would like to confirm that all indexes are being rebuilt. Is the
>> > time
>> > the index is created captured, if so, how can I view the time?
>> No but you can look at DBCC SHOWCONTIG or SHOWSTATISTICS before and
>> after
>> you can see the differences and will see where to look to see if work
>> was
>> done.
>> > 3) Why is this a logged transaction? I would have thought that SQL
>> > Server
>> > would create the indexes without dropping the originals, and then
>> > simply
>> > swap
>> > and drop once the index has completed.
>> If you are in FULL recovery mode everything is always fully logged. If
>> you
>> are in Bulk-Logged or Simple mode some index create or rebuild
>> operations
>> can be minimally logged. So your transaction log file may not grow very
>> much
>> but when you backup the log file (if in Bulk-logged) the backup will
>> include
>> all the extents changed byt he operations.
>> --
>> Andrew J. Kelly SQL MVP
>> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
>> news:A76DD6A4-4D1D-40F2-AC8E-F94C9042F42C@.microsoft.com...
>> > Folks,
>> >
>> > I have a table with a clustered index and 6 non-clustered indexes.
>> > When I
>> > issue a DBCC DBREINDEX without specifying a specific index all indexes
>> > are
>> > rebuilt including the clustered index - this takes 3 hours to complete
>> > and
>> > consumes 9gb of TLog space. When I issue a DBCC DBREINDEX specifying
>> > only
>> > the
>> > clustered index (which happens to be supporting the primary key) I've
>> > read
>> > that all non-clustered indexes are also rebuilt - this takes on 1.5
>> > hours
>> > and
>> > consumes 6gb of Tlog space. I have 3 questions:
>> >
>> > 1) If you use DBCC DBREINDEX without specifying an index, are the
>> > non-clustered indexes rebuilt twice on a table with a clustered index?
>> >
>> > 2) I would like to confirm that all indexes are being rebuilt. Is the
>> > time
>> > the index is created captured, if so, how can I view the time?
>> >
>> > 3) Why is this a logged transaction? I would have thought that SQL
>> > Server
>> > would create the indexes without dropping the originals, and then
>> > simply
>> > swap
>> > and drop once the index has completed.
>> >
>> > Thanks in advance for your help.
>> > --
>> > Scott H.
>>
>|||Just to piggy back on this thread. I didn't realise switching from
full to bulk logged would keep the transaction log chain active.
Makes interesting reading considering we do reindexing once a week.
We then have to feed 5 reporting servers via a log shipping type of
system (written internally).
Cheers,
Clive|||Andrew/Kalen/Paul,
Thank you all for your responses. What a great user group (I'd almost
forgotten). I've been off on other assignements these past few years and am
now just getting my hands "dirty" once again with SQL Server. I have
forgotten much, but am having a great time getting back into it. I spent the
entire weekend reading/testing/playing.
Andrew, I did find that script in the BOL. I do plan on using it in its
entirely, but also stole a chunk out of it and modified it slightly. I'm
going to use it to collect and archive these stats for all
instances/databases daily.
Paul, great offer. I work for a large outsourcing company, this particular
client can be a little difficult at times to convince. I hope the information
I collect using procAutoIndex is enough, if it's not I may just take you up
on the offer. I'll share my interpretation of the results (for one or two
tables) once I get them. Perhaps you can tell me if I'm correct or not.
Kalen, I'm going to work towards automating this procedure including putting
the database into bulk-logged mode. Hopefuly this will become a weekly
procedure and can be done during the weekend where impact is reduced.
By the way, I very much enjoy your articles in SSM. Thank-you.
--
Thanks,
Scott H.
"Scott H." wrote:
> Folks,
> I have a table with a clustered index and 6 non-clustered indexes. When I
> issue a DBCC DBREINDEX without specifying a specific index all indexes are
> rebuilt including the clustered index - this takes 3 hours to complete and
> consumes 9gb of TLog space. When I issue a DBCC DBREINDEX specifying only the
> clustered index (which happens to be supporting the primary key) I've read
> that all non-clustered indexes are also rebuilt - this takes on 1.5 hours and
> consumes 6gb of Tlog space. I have 3 questions:
> 1) If you use DBCC DBREINDEX without specifying an index, are the
> non-clustered indexes rebuilt twice on a table with a clustered index?
> 2) I would like to confirm that all indexes are being rebuilt. Is the time
> the index is created captured, if so, how can I view the time?
> 3) Why is this a logged transaction? I would have thought that SQL Server
> would create the indexes without dropping the originals, and then simply swap
> and drop once the index has completed.
> Thanks in advance for your help.
> --
> Scott H.|||Thank-you Kalen.
--
Scott H.
"Kalen Delaney" wrote:
> Hi Scott
> You might want to consider switching to bulk_logged mode instead of simple.
> The logging should be about the same, and you won't have to do a full db
> backup after, on a tlog backup. Situations like this are exactly what
> bulk_logged mode is intended for, i.e. so that you can do large bulk
> operations, like data loads or index rebuilds, that normally are log
> intensive, and make then less log intensive. Switching to bulk_logged mode
> allows your chain of tlog backups to remain intact and again, no full backup
> is required.
> You might want to read more details about the different recovery models in
> the Books Online and also this KB article might help:
> A transaction log grows unexpectedly or becomes full on a computer that is
> running SQL Server
> http://support.microsoft.com/kb/317375/en-us
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
> news:6355DB27-5D19-4A8F-A32D-5A2588D275D7@.microsoft.com...
> > Thanks Andrew.
> >
> > SQL Server 2000 SP3
> >
> > The CI is unique, so it would appear to be rebuilding only the CI.
> >
> > I re-ran the test in simple recovery mode, and the TLOG grew to only
> > 190mb.
> > I guess in production I could put the server into single user mode, change
> > to
> > simple recovery mode, run the reorg, full backup, return to full rcovery
> > mode. Seems like overkill. The reason I may follow this route is that we
> > are
> > having disk capacity issues.
> >
> > I have an application team thinking that rebuilding (DBREINDEX) this 17
> > million record table is a good thing to do nightly 7 days/week. I need to
> > dig
> > up supporting evidence one way or the other. I'll make use of SHOWCONTIG
> > prior to the execution over the next few days to determine. If there is
> > anything else you think could be beneficial feel free to offer. I've
> > recently
> > inherited the support of this instaance and there is much work to do.
> >
> > Thanks for your help.
> > --
> > Thanks,
> >
> > Scott H.
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> It really helps to specify what version and service pack you are using.
> >> In
> >> this case it can make a big difference. If I remember correctly in the
> >> RTM
> >> and maybe SP1 versions of SQL2000 it worked like this:
> >>
> >> If you rebuild the clustered index ( CI ) all non-clustered indexes (
> >> NCI)
> >> were always rebuilt as well. This was due to the way in which the CI key
> >> was
> >> appended to the end of all NCI's and would change during a rebuild.
> >>
> >> With one of the SP's (I think SP2) that behavior changed in that if the
> >> CI
> >> was unique and you rebuilt the CI by specifying only that index it did
> >> not
> >> rebuild the NCI's. But if the CI was not unique they would add a
> >> uniquifer
> >> (4 byte code) to the CI which got regenerated each time the CI was
> >> rebuilt.
> >> Since the CI (including the uniqueifier) was appended to the end of all
> >> NCI's they in turn needed to be rebuilt as well.
> >>
> >> IN SQL2005 they changed the way they generated the uniqifier and it no
> >> longer changes when the CI is rebuilt. So there is no need to rebuild all
> >> the NCI's just because you rebuild the CI.
> >>
> >> But to answer your specific questions a little more:
> >>
> >> > 1) If you use DBCC DBREINDEX without specifying an index, are the
> >> > non-clustered indexes rebuilt twice on a table with a clustered index?
> >>
> >> No. SQL Server was smart enough in all versions to only rebuild the NCI's
> >> once.
> >>
> >> > 2) I would like to confirm that all indexes are being rebuilt. Is the
> >> > time
> >> > the index is created captured, if so, how can I view the time?
> >>
> >> No but you can look at DBCC SHOWCONTIG or SHOWSTATISTICS before and after
> >> you can see the differences and will see where to look to see if work was
> >> done.
> >>
> >> > 3) Why is this a logged transaction? I would have thought that SQL
> >> > Server
> >> > would create the indexes without dropping the originals, and then
> >> > simply
> >> > swap
> >> > and drop once the index has completed.
> >>
> >> If you are in FULL recovery mode everything is always fully logged. If
> >> you
> >> are in Bulk-Logged or Simple mode some index create or rebuild operations
> >> can be minimally logged. So your transaction log file may not grow very
> >> much
> >> but when you backup the log file (if in Bulk-logged) the backup will
> >> include
> >> all the extents changed byt he operations.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
> >> news:A76DD6A4-4D1D-40F2-AC8E-F94C9042F42C@.microsoft.com...
> >> > Folks,
> >> >
> >> > I have a table with a clustered index and 6 non-clustered indexes. When
> >> > I
> >> > issue a DBCC DBREINDEX without specifying a specific index all indexes
> >> > are
> >> > rebuilt including the clustered index - this takes 3 hours to complete
> >> > and
> >> > consumes 9gb of TLog space. When I issue a DBCC DBREINDEX specifying
> >> > only
> >> > the
> >> > clustered index (which happens to be supporting the primary key) I've
> >> > read
> >> > that all non-clustered indexes are also rebuilt - this takes on 1.5
> >> > hours
> >> > and
> >> > consumes 6gb of Tlog space. I have 3 questions:
> >> >
> >> > 1) If you use DBCC DBREINDEX without specifying an index, are the
> >> > non-clustered indexes rebuilt twice on a table with a clustered index?
> >> >
> >> > 2) I would like to confirm that all indexes are being rebuilt. Is the
> >> > time
> >> > the index is created captured, if so, how can I view the time?
> >> >
> >> > 3) Why is this a logged transaction? I would have thought that SQL
> >> > Server
> >> > would create the indexes without dropping the originals, and then
> >> > simply
> >> > swap
> >> > and drop once the index has completed.
> >> >
> >> > Thanks in advance for your help.
> >> > --
> >> > Scott H.
> >>
> >>
> >>
>
>

Tuesday, March 27, 2012

DBCC SHRINKDATABASE

DBCC SHRINKDATABASE

DBCC SHRINKDATABASE

Wednesday, March 21, 2012

DBCC pintable

DBCC pintable

Wednesday, March 7, 2012

DBCC DBREINDEX unexpected results

DBCC DBREINDEX failure

DBCC DBREINDEX failure

DBCC DBREINDEX Behaviour

DBCC DBREINDEX Behaviour

DBfile usage

Blog Archive

About Me