Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rebuild Online Index in Partationed Table


Rebuild Online Index in Partationed Table

Author
Message
pawana.paul
pawana.paul
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 354
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,
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45432 Visits: 39942
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
pawana.paul
pawana.paul
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 354
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
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45432 Visits: 39942
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. :-P

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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
pawana.paul
pawana.paul
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 354
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
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45432 Visits: 39942
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
pawana.paul
pawana.paul
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 354
Thanks Jeff.

Yes, they are on different filegroups.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45432 Visits: 39942
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
pawana.paul
pawana.paul
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 354
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
Ed Wagner
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10342 Visits: 9606
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search