Cluster Index change on a table with 1 billion rows

Question

Cluster Index change on a table with 1 billion rows

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

sql-lover SSCoach Points: 18530 More actions · Answer 1

GilaMonster (7/21/2016)
What time that the table becomes a heap? I don't think anyone's suggesting dropping the cluster and then, as a second step, creating a new one.

With the 2nd method I mentioned on the 1st post of my thread, which was dropping Indexes online, then recreate online. I think Jeff clarified and confirm the table will certainly be a heap during some amount of time, if that approach is taken.

sql-lover SSCoach Points: 18530 More actions · Answer 2

j-1064772 (7/21/2016)
I am very curious to learn how you proceeded once you have completed it. Please get back to us on this.
Regards

It won't happen soon, we are still testing, but I certainly will circle back and will post what I did.

So far, these two are my prefered choices or methods:

1)

Do a table swap/migration, as I initially though

2)

Use CREATE / DROP_EXISTING with ONLINE, as Gail suggested.

I will compare those two and analyze completion time.

sql-lover SSCoach Points: 18530 More actions · Answer 3

Wow

I am testing the logic only with a smaller client which same table but its only 30GB of size (whole database is about 90 GB) . The CREATE INDEX statement with DROP_EXISTING and ONLINE enable has taken 1hr so far and still not done. It is consuming 138GB of Tlog (we are on SIMPLE recovery model) and Tlog file still growing.

I'm afraid that for my 6 billion rows table this is not the way to go.

-- UPDATE --

The CI conversion only, took 1 hr 1/2 to complete and required 160GB of extra space for the Tlog.

webrunner SSC-Dedicated Points: 31885 More actions · Answer 4

I know this is a very old thread, but regarding your last statement "The CI conversion only, took 1 hr 1/2 to complete and required 160GB of extra space for the Tlog." Do you mean for the 30 GB test or for the whole 6-billion-row table?

I'm curious because I am dealing with a similar issue.

Thanks for any help.

-- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html