Rebuild Online Index in Partationed Table

Question

Rebuild Online Index in Partationed Table

pawana.paul

SSCommitted

Points: 1686
More actions
August 11, 2014 at 11:23 am

#288913

All,
Need your help.
I joined new environment. There is one table of 1.6 TB which has 16 partations. SQL Server Version is 2008 R2. We are planning to perform maintenance (Rebuild and update stats). Is it possible to rebuild online index and update statistics only on the active partation? Please provide me the script if we can.
Thanks in advance,

Viewing 15 posts - 1 through 15 (of 20 total)

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

pawana.paul (8/11/2014)
All,
Need your help.
I joined new environment. There is one table of 1.6 TB which has 16 partations. SQL Server Version is 2008 R2. We are planning to perform maintenance (Rebuild and update stats). Is it possible to rebuild online index and update statistics only on the active partation? Please provide me the script if we can.
Thanks in advance,

My first suggestion would be to look around on your server. It's difficult for me to imagine that someone would setup partitions on such a large table and not also setup the ability to maintain it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pawana.paul SSCommitted Points: 1686 More actions · Answer 2

Hello Jeff,

Thanks for the response.

We don't have scheduled maintenance due to the nature of environment and will be done on planned adhoc basis. The earlier partations are in good shape and need to do maintenance on active partation. I am looking at the possibility of online rebuild of active partation in SQL 2008 R2 and updating the statistics on that partation. I did google and found the below syntax for SQL 2014. It will be helpful if I get something similar for SQL 2008 R2.

ALTER INDEX IX_TransactionHistory_TransactionDate

ON Production.TransactionHistory

REBUILD Partition = 5

WITH ( ONLINE = ON ( WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 minutes, ABORT_AFTER_WAIT = SELF )))

;

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

pawana.paul (8/11/2014)
We don't have scheduled maintenance due to the nature of environment...

Heh... I'm afraid to ask what such an environment might be, so I won't ask. 😛

ONLINE rebuilds of partitioned indexes aren't available in 2008. That first came out in 2014 (IIRC). The best you'll be able to do online is a REORGANIZE, which is always online. The biggest differences between a REBUILD and REORGANIZE are 1) REORGANIZE won't rebuild the B-TREE and 2) REORGANIZE is always fully logged regardless of the Recovery Model being used.

According to your claim of 1.6TB over 16 partitions, a REORGANIZE of the clustered index on such a table would cause the log file to blow out to more an 100GB. Of course, that would also get backed up to tape.

There is a very complicated way to do this using SELECT INTO/SWITCH (out and in)/MERGE/DROP but it would cause the MDF to blow out like the log file would have and, considering the size of the partition, the fix would be worse than the problem in this case.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pawana.paul SSCommitted Points: 1686 More actions · Answer 4

Thank you Jeff,

Can we do offline rebuild of partation in SQL 2008 Version with the below syntax?

ALTER INDEX IX_TransactionHistory_TransactionDate

ON Production.TransactionHistory

REBUILD Partition = 5

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Yes. Just a quick question, though... are these partitions each in a separate filegroup or not?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pawana.paul SSCommitted Points: 1686 More actions · Answer 6

pawana.paul

SSCommitted

Points: 1686

August 12, 2014 at 2:52 pm

#1737367

Thanks Jeff.

Yes, they are on different filegroups.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Different file groups aren't so bad. If they're on different files, you might end up with a lot of wasted space, though. When you rebuild an index for anything over 128 extents (about 8 Megabytes... a pretty small thing), SQL Server will build a new index first, then drop the old one. With a clustered index, that means basically doubling the size of the file.

That's ok if you're still adding data to that particular file. I've actually written a 2 hour lecture on what to do if that's not the case and you need to squeeze the partition/file down as small as you can before you set it to READ_ONLY.

There are some tricks you can do on normal tables like disabling an index and then rebuilding it (doesn't work on clustered indexes, though) or doing a CREATE WITH DROP EXISTING but none of those things will work on a partitioned table partition.

Also be advised that a REBUILD will also be fully logged if the database is in the FULL RECOVERY model.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pawana.paul SSCommitted Points: 1686 More actions · Answer 8

Thank you so very much Jeff. This info. helps.

Is it possible to share 2 hours lecture you written on this topic? Please send me on pawana.paul@gmail.com, if possible.

Thanks very much for all your help.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 9

If you ever get the chance to see Jeff's lecture on this, I'd recommend that you take advantage of the opportunity. I've seen it and it's just what you'd expect - very intense, well researched, technically solid and really good.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

pawana.paul SSCommitted Points: 1686 More actions · Answer 10

Hello Ed,

Please let me know what it would take to attend Jeff's lecture.

Thanks in advance.

pawana.paul SSCommitted Points: 1686 More actions · Answer 11

Hello Ed,

Please let me know what it would take to attend Jeff's lecture.

Thanks in advance.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 12

pawana.paul (8/13/2014)
Hello Ed,
Please let me know what it would take to attend Jeff's lecture.
Thanks in advance.

The next time he offers it at a SQL Saturday or PASS event, attend it. Where are you located?

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

pawana.paul SSCommitted Points: 1686 More actions · Answer 13

I am in Bay Area, CA. Please let me know details on attending his lecture.

Regards,

Ed Wagner SSC Guru Points: 287026 More actions · Answer 14

pawana.paul (8/13/2014)
I am in Bay Area, CA. Please let me know details on attending his lecture.
Regards,

Well, it's a bit of a haul for you, but I see he's submitted a session for SQL Saturday #315 on 10/4/2014 in Pittsburgh, PA. See http://www.sqlsaturday.com/315/schedule.aspx for more details. I don't know if they've confirmed the session list yet, but you can probably contact the event administrators if you have questions.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions