Reindexing 27GB table

On one of the database, I support has 16957722 records in a table and it's
clustered index size is 27606.30.
I used to run the Reindex job created on weekly Sunday starting at 11:00 PM.
It takes more than 7 hours.
Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am
cancelling the job.
In this situation what would you suggest to reindex the table of
such big one ( 27 GB ) ?
Appreciate your advise.
--
Thanks
S SIVAPRASADFirst you need to determine whether you benefit from reindexing. See
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.
To check frag level use DBCC SHOWCONTIG (2000) or sys.dm_db_index_physical_stats (2005).
In 2000, you have little options of you want to do this "online", your option is DBCC INDEXDEFRAG.
In 2005, you can use either ALTER INDEX with REORGANIZE (same as INDEXDEFRAG), or use ALTER INDEX
with REBUILD, ONLINE (same as DBREINDEX, except for ONLINE which is new option).
All have pro and cons, one thing to consider is the usage of transaction log space (which depends on
what type of command you run and can also be influenced by recovery model).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote in message
news:2484E248-2FCD-47F8-BACB-0AB9E71DEAC3@.microsoft.com...
> On one of the database, I support has 16957722 records in a table and it's
> clustered index size is 27606.30.
> I used to run the Reindex job created on weekly Sunday starting at 11:00 PM.
> It takes more than 7 hours.
> Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am
> cancelling the job.
> In this situation what would you suggest to reindex the table of
> such big one ( 27 GB ) ?
> Appreciate your advise.
> --
> Thanks
> S SIVAPRASAD|||It is on SQL Server 2000 Enterprise Edition with SP4 (8.00.2040) .
Database is on Full recovery model and it has enough space for Log file
growth.
DBCC SHOWCONITG() ran and rebuilding of the indexes is necessary now.
Let me know what would be the best optimal way of rebuilding the indexes.
The box is on 24x7 and I want to rebuild the index as quick as possible.
The clustered index sized around 27 GB.
--
Thanks
S SIVAPRASAD
"Tibor Karaszi" wrote:
> First you need to determine whether you benefit from reindexing. See
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.
> To check frag level use DBCC SHOWCONTIG (2000) or sys.dm_db_index_physical_stats (2005).
> In 2000, you have little options of you want to do this "online", your option is DBCC INDEXDEFRAG.
> In 2005, you can use either ALTER INDEX with REORGANIZE (same as INDEXDEFRAG), or use ALTER INDEX
> with REBUILD, ONLINE (same as DBREINDEX, except for ONLINE which is new option).
> All have pro and cons, one thing to consider is the usage of transaction log space (which depends on
> what type of command you run and can also be influenced by recovery model).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote in message
> news:2484E248-2FCD-47F8-BACB-0AB9E71DEAC3@.microsoft.com...
> > On one of the database, I support has 16957722 records in a table and it's
> > clustered index size is 27606.30.
> >
> > I used to run the Reindex job created on weekly Sunday starting at 11:00 PM.
> > It takes more than 7 hours.
> >
> > Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am
> > cancelling the job.
> >
> > In this situation what would you suggest to reindex the table of
> > such big one ( 27 GB ) ?
> >
> > Appreciate your advise.
> > --
> > Thanks
> > S SIVAPRASAD
>
>|||There's no "optimal way". There are different options, all with advantages and disadvantages. Based
on your requirements, you can weigh the advantages to the disadvantages and pick the one that suits
your particular need best. Make sure you understand the pros and cons with each method and read the
whitepaper I posted a link to.
Having said that, if the box is 24*7, indexdefrag might be better. But it can take longer time than
dbreindex (but it doesn't block nearly as much), it can produce more log records, and the
defragmentation might not be as perfect as dbreindex.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote in message
news:8A93A9FA-E357-475D-BB40-8F9BE0A43CCF@.microsoft.com...
> It is on SQL Server 2000 Enterprise Edition with SP4 (8.00.2040) .
> Database is on Full recovery model and it has enough space for Log file
> growth.
> DBCC SHOWCONITG() ran and rebuilding of the indexes is necessary now.
> Let me know what would be the best optimal way of rebuilding the indexes.
> The box is on 24x7 and I want to rebuild the index as quick as possible.
> The clustered index sized around 27 GB.
> --
> Thanks
> S SIVAPRASAD
>
> "Tibor Karaszi" wrote:
>> First you need to determine whether you benefit from reindexing. See
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.
>> To check frag level use DBCC SHOWCONTIG (2000) or sys.dm_db_index_physical_stats (2005).
>> In 2000, you have little options of you want to do this "online", your option is DBCC
>> INDEXDEFRAG.
>> In 2005, you can use either ALTER INDEX with REORGANIZE (same as INDEXDEFRAG), or use ALTER INDEX
>> with REBUILD, ONLINE (same as DBREINDEX, except for ONLINE which is new option).
>> All have pro and cons, one thing to consider is the usage of transaction log space (which depends
>> on
>> what type of command you run and can also be influenced by recovery model).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote in message
>> news:2484E248-2FCD-47F8-BACB-0AB9E71DEAC3@.microsoft.com...
>> > On one of the database, I support has 16957722 records in a table and it's
>> > clustered index size is 27606.30.
>> >
>> > I used to run the Reindex job created on weekly Sunday starting at 11:00 PM.
>> > It takes more than 7 hours.
>> >
>> > Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am
>> > cancelling the job.
>> >
>> > In this situation what would you suggest to reindex the table of
>> > such big one ( 27 GB ) ?
>> >
>> > Appreciate your advise.
>> > --
>> > Thanks
>> > S SIVAPRASAD
>>