Alter Index...Rebuild

Running SQL 2005, SP1
In migrating over to SQL 2005, we also took on a different indexing strategy
,
going from DBCC DBREINDEX to ALTER INDEX...REBUILD.
There are a couple of things that are worrisome. A sample of the ALTER INDEX
statement I use is included at the end of this message. I only run this on
indexes that are fragmented as per sys.dm_db_index_physical_stats. The
following are the two items of concern.
1. The weekly ALTER INDEX takes longer to complete than the weekly DBCC
DBREINDEX. I have SORT_IN_TEMPDB = ON, but there is relatively little growth
,
if any to TempDB. So I assume it may be done in memory. TempDB is on drive C
,
a set of internal SCSI disks. The data and log files are on the SAN, spread
across 26 disks.
2. The data file growth is significant (sometimes the log files, but mostly
the data files) when running ALTER INDEX. As noted in concern 1,
SORT_IN_TEMPDB = ON, so I am perplexed why there is such growth on the data
files?
SAMPLE:
ALTER INDEX [name of index] ON [name of database].dbo.[name of t
able]
REBUILD WITH ( FILLFACTOR = 90, SORT_IN_TEMPDB = ON)
Message posted via http://www.droptable.comWhen you rebuild an index with either DBREINDEX or ALTER INDEX REBUILD it
completely rebuilds the indexes (this includes the table if there is a
clustered index) by creating a new copy of them in the data file(s). This
means you need free space in the data files to hold the old and new indexes
at the same time. Only when it is completed does it drop the original
indexes. So you always need plenty of free space in the data files for
operations such as these. You have been around here long enough to know by
know you should ALWAYS have plenty of free space in the database files both
data & Log. If you are getting growth in the data files it sounds like you
shrunk it at some point. This is also a bad idea to shrink the files. The
SORT_IN_TEMPDB can help reduce time and some of the space needed in the data
files as it can serve as a work space to sort the indexes etc during the
processing. But it does not remove the need for the place to rebuild the
index itself. You say there is little growth to Tempdb during this process.
Well again if there is any growth you didn't size the files correctly to
begin with. Lack of growth of the files has nothing to do with how much work
they are performing. As long as there is room in the data files for what it
has to do there should be no growth. That is the proper and expected
behavior.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:7675a0e8fcb67@.uwe...
> Running SQL 2005, SP1
> In migrating over to SQL 2005, we also took on a different indexing
> strategy,
> going from DBCC DBREINDEX to ALTER INDEX...REBUILD.
> There are a couple of things that are worrisome. A sample of the ALTER
> INDEX
> statement I use is included at the end of this message. I only run this on
> indexes that are fragmented as per sys.dm_db_index_physical_stats. The
> following are the two items of concern.
> 1. The weekly ALTER INDEX takes longer to complete than the weekly DBCC
> DBREINDEX. I have SORT_IN_TEMPDB = ON, but there is relatively little
> growth,
> if any to TempDB. So I assume it may be done in memory. TempDB is on drive
> C,
> a set of internal SCSI disks. The data and log files are on the SAN,
> spread
> across 26 disks.
> 2. The data file growth is significant (sometimes the log files, but
> mostly
> the data files) when running ALTER INDEX. As noted in concern 1,
> SORT_IN_TEMPDB = ON, so I am perplexed why there is such growth on the
> data
> files?
> SAMPLE:
> ALTER INDEX [name of index] ON [name of database].dbo.[name of
table]
> REBUILD WITH ( FILLFACTOR = 90, SORT_IN_TEMPDB = ON)
> --
> Message posted via http://www.droptable.com
>