SQL Server - How To Change a Live Clustered Index Without Blocking

Asked By Charles Law on 12-Jul-08 05:36 AM
Anyone who has been following my tribulation with a particularly sticky
query might not be surprised to see this post.

I am now in the position where I want to update a live system by exchanging
one clustered index for another, on a table that gets several updates per
second.

What is the best way to do this? By best, I mean the way that allows the
updates to continue unhindered. Stopping the events is an impossibility;
they must continue to be recorded.

TIA

Charles




Tibor Karaszi replied on 12-Jul-08 07:14 AM
As of SQL Server 2005, you can do both CREATE, ALTER and DROP INDEX using an ONLINE option -
assuming you are on Enterprise Edition. The data is available through the "data shuffling" period
with restrictive locks only during super-short time periods in beginning and end of command.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
Charles Law replied on 12-Jul-08 08:00 AM
Hi Tibor

Thanks for the reply. Is this feature _only_ available with Enterprise
Edition? We are using 2005 Standard Edition.

Charles
Stuart Ainsworth replied on 15-Jul-08 08:17 PM
ng

I haven't done a lot of DBA work in the high availability arena (so
take this with a grain of salt), but if you have a data requirement of
zero downtime, then your hindrance seems to be a symptom of a larger
problem; you either need some sort of failover option or a maintenance
window.  Requiring databases to run 24/7/365 without one of these is
not a good idea :)

Sorry that I don't have a real suggestion; just an observation.
Stu
Charles Law replied on 12-Jul-08 09:11 AM
Hi Stu

We have a failover option, but not perhaps this type of maintenance window.
The failover solution still relies on the same database, so even if we
failed over, it would still be attaching to the same database.

I take your point entirely.

Cheers

Charles



I haven't done a lot of DBA work in the high availability arena (so
take this with a grain of salt), but if you have a data requirement of
zero downtime, then your hindrance seems to be a symptom of a larger
problem; you either need some sort of failover option or a maintenance
window.  Requiring databases to run 24/7/365 without one of these is
not a good idea :)

Sorry that I don't have a real suggestion; just an observation.
Stu
Plamen Ratchev replied on 12-Jul-08 10:32 AM
Yes, on-line index operations are available only in SQL Server 2005
Enterprise Edition. See the note around the ONLINE option:
http://msdn.microsoft.com/en-us/library/ms188783.aspx

HTH,

Plamen Ratchev
http://www.SQLStudio.com
Charles Law replied on 12-Jul-08 10:44 AM
Thanks for the confirmation; that is a shame though.

Charles
Erland Sommarskog replied on 12-Jul-08 01:01 PM
Charles Law (blank@nowhere.com) writes:

The best way if you only have Standard Edition, is to wait until the next
maintenance window.

Doing this live without no disruption is no kids game. The only way I can
think of doing it is something like:

1) Create a table to holds updates.
2) Create an empty copy of the table with the new clustered index.
3) Do BEGIN TRANSACTION SELECT COUNT(*) FROM tbl WITH HOLDLOCK on the
old table.
4) Add a trigger on the table that copies all changes to the updates table.
5) Start an INSERT from the live table to he new table, using snapshot
isolation.
6) Rollback the transaction started in step 3.
7) Wait until 5 completes.
8) Again lock the table as in step 3.
9) Merge in the updates from the main table.
10) Rename the tables, and move referencing foreign keys.
11) Drop the old table.
12) COMMIT.

You may want to test this rigorously before you do it. You should probably
automate it all, so that the moments when the writers are blocked become
as short as possible.

If the table has a timestamp column or something else by which you can
specify the updates that comes after the copying to the new table, you
don't need the trigger.

I don't know how long time it would take to develop and test this
arrangment. Maybe it would be cheaper to get Enterprise Edition. Then
again, to install it, you need to wait until the next maintenance
window...

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Charles Law replied on 12-Jul-08 01:50 PM
Hi Erland

I thought it wouldn't be trivial ;-) I was starting to think along the same
lines, that is direct updates to another table while the maintenance is
going on, direct them back at the end, and then merge the records from the
other table with the main one. I will keep a copy of your procedure though
as it is obviously more complete.

Thanks.

Charles
Erland Sommarskog replied on 12-Jul-08 05:46 PM
Charles Law (blank@nowhere.com) writes:

Complete and complete... As complete as a sketch on the back of a
napkin can be. There are a probably a few things I overlooked.

I stress again this is a complex task with high risks and it takes
skill and care to do it successfully and not create a mess. A real
complex part is the merge-back, but this can be considerable easier if
there are simple means to find the changed rows without the trigger. That
depends on the nature of the table, of which you obviously know more about
than I do.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Stuart Ainsworth replied on 15-Jul-08 08:17 PM
w.

Charles, are you the DBA or the developer?  I only ask because if
you're not the DBA, then perhaps they have some method of sneaking in
some maintenance times.  After all, hard drives will go bad eventually
(wink, wink).

If you are the DBA, then you need to read up on various failover
methodologies for databases and SAN's and prepare yourself for a heart-
to-heart with your employer regarding those options.  If you don't
plan downtime, it will happen without planning.  Again, I'm not an
expert on high-availability stuff, but I know that there must be ways
to keep multiple database servers in sync so that you can take one
down for maintenance and keep running the other.

Stu
Charles Law replied on 13-Jul-08 05:30 PM
Hi Stu

I understand the SAN area is covered, and that there is a regular
maintenance window, so I'm looking at the possibility of using the next one
for this update. It looks like it will work out ok :-)

Thanks.

Charles



Charles, are you the DBA or the developer?  I only ask because if
you're not the DBA, then perhaps they have some method of sneaking in
some maintenance times.  After all, hard drives will go bad eventually
(wink, wink).

If you are the DBA, then you need to read up on various failover
methodologies for databases and SAN's and prepare yourself for a heart-
to-heart with your employer regarding those options.  If you don't
plan downtime, it will happen without planning.  Again, I'm not an
expert on high-availability stuff, but I know that there must be ways
to keep multiple database servers in sync so that you can take one
down for maintenance and keep running the other.

Stu
EricRussel replied on 14-Jul-08 12:17 PM
Perhaps this is just a one time mass update, but if this key going to get
routinely updated, then perhaps it is just not a good candidate for a
clustered index.
Charles Law replied on 14-Jul-08 04:47 PM
Hi Eric

Because it was looking like it would be difficult to make the change, I
tested performance leaving the index as-is, and found that it performs
exactly the same, so I won't now be changing the live index after all. Just
as well, I think.

Cheers

Charles