InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 20:40:37 GMT20http://www.sqlservercentral.com/Forums/Topic1360322-391-1.aspx[quote][b]JamesMorrison (9/17/2012)[/b][hr]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.[/quote]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.Tue, 18 Sep 2012 03:38:56 GMTMysteryJimbohttp://www.sqlservercentral.com/Forums/Topic1360322-391-1.aspx[quote][b]JamesMorrison (9/17/2012)[/b][hr]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.[/quote]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.Tue, 18 Sep 2012 03:36:07 GMTMysteryJimbohttp://www.sqlservercentral.com/Forums/Topic1360322-391-1.aspxWe 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.Mon, 17 Sep 2012 15:59:59 GMTJamesMorrisonhttp://www.sqlservercentral.com/Forums/Topic1360322-391-1.aspxAny 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.Mon, 17 Sep 2012 15:46:12 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1360322-391-1.aspxReason = 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.ThanksMon, 17 Sep 2012 13:53:16 GMTJamesMorrisonhttp://www.sqlservercentral.com/Forums/Topic1360322-391-1.aspxI should have asked already. What are your reasons for partitioning this table?Mon, 17 Sep 2012 13:01:30 GMTMysteryJimbohttp://www.sqlservercentral.com/Forums/Topic1360322-391-1.aspxWhy?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 setupMon, 17 Sep 2012 12:58:39 GMTMysteryJimbohttp://www.sqlservercentral.com/Forums/Topic1360322-391-1.aspxWe 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? ThanksMon, 17 Sep 2012 12:44:05 GMTJamesMorrisonhttp://www.sqlservercentral.com/Forums/Topic1360322-391-1.aspxI'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_ptnThen[code="sql"]exec sp_rename 'table', 'table_old'GOCREATE VIEW [table] AS SELECT * FROM table_ptnGO[/code]or[code="sql"]exec sp_rename 'table', 'table_old'GOexec sp_rename 'table_ptn', 'table'GO[/code]The actual switch will take milliseconds as its a meta data changeMon, 17 Sep 2012 10:52:02 GMTMysteryJimbohttp://www.sqlservercentral.com/Forums/Topic1360322-391-1.aspxWe 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? ThanksMon, 17 Sep 2012 10:36:57 GMTJamesMorrison