Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Rebuild Online Index in Partationed Table Expand / Collapse
Author
Message
Posted Monday, August 11, 2014 11:23 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 11:21 PM
Points: 57, Visits: 252
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,
Post #1601986
Posted Monday, August 11, 2014 4:16 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:00 AM
Points: 37,099, Visits: 31,650
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1602106
Posted Monday, August 11, 2014 4:52 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 11:21 PM
Points: 57, Visits: 252
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 )))
;
Post #1602110
Posted Monday, August 11, 2014 5:14 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:00 AM
Points: 37,099, Visits: 31,650
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1602111
Posted Monday, August 11, 2014 5:21 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 11:21 PM
Points: 57, Visits: 252
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
Post #1602113
Posted Tuesday, August 12, 2014 1:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:00 AM
Points: 37,099, Visits: 31,650
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1602450
Posted Tuesday, August 12, 2014 2:52 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 11:21 PM
Points: 57, Visits: 252
Thanks Jeff.

Yes, they are on different filegroups.



Post #1602489
Posted Tuesday, August 12, 2014 7:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:00 AM
Points: 37,099, Visits: 31,650
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1602535
Posted Tuesday, August 12, 2014 9:17 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 11:21 PM
Points: 57, Visits: 252
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.
Post #1602547
Posted Wednesday, August 13, 2014 5:18 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 4,339, Visits: 3,387
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
Post #1602661
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse