clustered index rebuilds and performance hit

Hi
We're about to implement some clustered index changes on tables with 10s of
millions of rows.
I would like to know what the implications will be on:
SELECT
INSERT
UPDATE
DELETE
during the duration of the index changes/rebuilds. We have to plan for this
and want our users to know exactly what to expect i.e. what they will and
will not be able to do in the database.
thanks..u
-- cranfield, DBA
You mention both "change" and rebuild. Which is it? Removing one clustered index and adding it back
on some other column? Or defragmenting the clustered index?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:4B7E08BE-683D-4642-870F-539222435337@.microsoft.com...
> Hi
> We're about to implement some clustered index changes on tables with 10s of
> millions of rows.
> I would like to know what the implications will be on:
> SELECT
> INSERT
> UPDATE
> DELETE
> during the duration of the index changes/rebuilds. We have to plan for this
> and want our users to know exactly what to expect i.e. what they will and
> will not be able to do in the database.
> thanks..u
> --
> -- cranfield, DBA
|||if you drop and recreate OR if you use DBCC DBREINDEX, then the table will
be exclusively locked for the duration.
IF you just defrag it via DBCC IndexDefrag, then there will only be minor
impact.
Greg Jackson
PDX, Oregon
|||Hi Tibor
We are removing the clustered index and creating a new one on a different key.
We will be creating the old clustered index as non-clustered. These changes
are required due to a logic change in our application.
thanks for the reply.
alan cranfield
"Tibor Karaszi" wrote:

> You mention both "change" and rebuild. Which is it? Removing one clustered index and adding it back
> on some other column? Or defragmenting the clustered index?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:4B7E08BE-683D-4642-870F-539222435337@.microsoft.com...
>
>
|||Any time you drop, create or re-create a clustered index the table will be
unavailable for the duration of the event. Your best bet is to follow these
steps:
1. stop all access to this table
2. drop all the nonclustered indexes
3. drop the clustered index
4. Create the new clustered index
5. Recreate the nonclustered indexes
If you just drop the clustered index first it will recreate all the
nonclustered indexes. Then when you create a new clustered index it will
rebuild all the nonclustered indexes again.
Andrew J. Kelly SQL MVP
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:18AB918A-44F4-4B91-8F40-1CFA8B5DEC40@.microsoft.com...[vbcol=seagreen]
> Hi Tibor
> We are removing the clustered index and creating a new one on a different
> key.
> We will be creating the old clustered index as non-clustered. These
> changes
> are required due to a logic change in our application.
> thanks for the reply.
> alan cranfield
> "Tibor Karaszi" wrote: