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

Partitioning large tables ONLINE Expand / Collapse
Author
Message
Posted Monday, September 17, 2012 10:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 25, 2013 3:37 PM
Points: 125, Visits: 438
We have a very big table we are trying to get partitioned on all of our servers.
All of the testing seems to indicate that this will take about 12 hours, which is longer than our maximum outage window (6 hours) that we can schedule.

So now we are investigating doing the partitioning 'ONLINE' during a low customer activity period.
Anyone done this before? Any tips or comments on what works best? Thanks
Post #1360322
Posted Monday, September 17, 2012 10:52 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,331, Visits: 15,269
I've done this several times before. You need capacity to store two copies of the table.

-create a partitioned table of the same structure called table_ptn.
-Add a trigger to maintain the new table
-Backfill the data into table_ptn

Then

exec sp_rename 'table', 'table_old'
GO
CREATE VIEW [table] AS SELECT * FROM table_ptn
GO

or

exec sp_rename 'table', 'table_old'
GO
exec sp_rename 'table_ptn', 'table'
GO

The actual switch will take milliseconds as its a meta data change
Post #1360339
Posted Monday, September 17, 2012 12:44 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 25, 2013 3:37 PM
Points: 125, Visits: 438
We discussed that as an option. Create a shadow partition table and gradually crawl through the original table to partition the data over time. Then we will just rename tables when we are done.

But the original table can be updated also if users change their data. So we would have to track updates also during the process, perhaps with a trigger to a 3rd table. Then sync it all at the end. Does that make sense? Thanks
Post #1360402
Posted Monday, September 17, 2012 12:58 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,331, Visits: 15,269
Why?

Use the trigger on the source table to insert/update/delete in the "shadow" table.

Any additional tables and logic is adding work and complicating a simple setup
Post #1360408
Posted Monday, September 17, 2012 1:01 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,331, Visits: 15,269
I should have asked already. What are your reasons for partitioning this table?
Post #1360410
Posted Monday, September 17, 2012 1:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 25, 2013 3:37 PM
Points: 125, Visits: 438
Reason = query performance on the table with partition elimination. Also index maintenance going forward.

I understand what you are saying with the update trigger directly to the new partition table.
The problem I see with that is if an update to the original table happens, but that row is not yet on the partitioned version of the table, there is nothing for the trigger to update. That was the thinking with having a 3rd table track those changes, then have a brief outage to sync the updates and swap the table names.

Thanks
Post #1360438
Posted Monday, September 17, 2012 3:46 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 4,609, Visits: 11,010
Any reasons why you shouldn't let SQL Server do that for you?
If you have Enterprise Edition, you can rebuild the clustered index ONLINE on the partition scheme.
Watch your tempdb/user db space usage (depends if you have sort on tempdb on) and log growth.


--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1360481
Posted Monday, September 17, 2012 3:59 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 25, 2013 3:37 PM
Points: 125, Visits: 438
We are testing the ONLINE partitioning process this week. But if we do it that way, it is definitely a performance hit. So we are trying to figure out how big that performance hit will be. If the performance hit is such that for 12-18 hours many things will timeout, then that is not going to work for us.

If that is the case, then we might have to do the partitioning in a 'shadow' table and just gradually build it on the side. That would lead to a 3rd table to track changes (updates) that happen in the source table while the new partitioned 'shadow' table is being constructed. Then at the end have a brief outage to sync the two tables, then rename tables to swap them.

Any other ideas? Thanks for any suggestions.

Just some background, yes it is Enterprise Edition 2008.
256 GB RAM, plenty of horsepower with the latest Netapp SAN and Cisco UCS servers.
Post #1360486
Posted Tuesday, September 18, 2012 3:36 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,331, Visits: 15,269
JamesMorrison (9/17/2012)

I understand what you are saying with the update trigger directly to the new partition table.
The problem I see with that is if an update to the original table happens, but that row is not yet on the partitioned version of the table, there is nothing for the trigger to update. That was the thinking with having a 3rd table track those changes, then have a brief outage to sync the updates and swap the table names.


Assuming you are still migrating the data into the shadow table it doesnt matter if the row is there or not on an update. You still update the live row.
Post #1360649
Posted Tuesday, September 18, 2012 3:38 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,331, Visits: 15,269
JamesMorrison (9/17/2012)
We are testing the ONLINE partitioning process this week. But if we do it that way, it is definitely a performance hit. So we are trying to figure out how big that performance hit will be. If the performance hit is such that for 12-18 hours many things will timeout, then that is not going to work for us.


With the hardware you mention the performance hit will be minimal provided youre partitioned table has the correct primary key. I've done this on 400GB+ tables with many billions of rows and database TPS over 10,000.
Post #1360650
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse