Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We have a database which is nearly 3TB in size, out of which 8 tables together are 1.5TB which we want to partition based on year.

But as we will be partitioning over a terabyte of data, this will be very time consuming and would impact other databses/users on system.

Keeping that in mind, how should we partition the table based on date to keep processing/data movement quick.

Any suggestions on approach - means move to temp table then maybe use the SWITCH partition function?


migration rejected from Jul 23 '13 at 9:52

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as unclear what you're asking by Jon Seigel, RolandoMySQLDBA, Paul White, dezso, Mark Storey-Smith Jul 23 '13 at 9:52

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

Not sure if you're asking "should I partition", "how to partition" or "how to partition not to slow down work of other users while partitioning" ? – makciook Jul 18 '13 at 9:51
we need to partition and we have written scripts for it. By creating clustered index it will move into respective partition. Just want to know if there is any other approach - for e.g shoul i move to temp table data by year and then may be use switch.merge – Conrad Jagger Jul 18 '13 at 10:11
Are all the (proposed) partitions except for the current year read-only? Or is there still write activity in the historical data? – Jon Seigel Jul 18 '13 at 12:43
If your system is not a 24x7 system you don't really have a problem. I just recently partitioned a 300gb table in about 15 minutes. Test your scripts on a test server and you will probably find a similar result. Worst case you can partition each of your 8 tables one at a time over the course of a couple of weeks. – Kenneth Fisher Jul 18 '13 at 13:15
In addition to @Jon's question, is the writing to the current year insert only, or do you also perform updates? Could those updates be suspended temporarily? There are lots of clever ways to minimize / eliminate impact to users, but we need more details about how the data is handled and what the users are doing. – Aaron Bertrand Jul 18 '13 at 13:26

You can use the sliding window technique for partitioning i.e. transfer a partition from a source table to a destination table especially when you are looking into archiving data to a cold table or static table.

Best tool available to automate this is SQL Server Partition Management from codeplex. It has command-line options as well.

A picture is worth thousand words ! Below example will explain the steps for implementing partitioning :


enter image description here

Picture reference

enter image description here

Some good references :

I don't see how that would help with partitioning an hitherto unpartitioned table. – TToni Jul 18 '13 at 13:36
@TToni What makes you think that it wont help ? Have you tried the partition management utility ? – Kin Jul 18 '13 at 13:41
All your pictures show how to transfer data from one partitioned table to another (based on the same partition column). This is IMHO not what the OP asked for. – TToni Jul 18 '13 at 13:48
@TToni ah now I see where the confusion comes from. I have pointed out links on how to do it and the utility as well. Pictures just show how the data will be transferred. All the prep work like creating staging tables and switching out and in can be done by using Partition Management Utility. – Kin Jul 18 '13 at 13:53
I still don't see how that answers any of the OP's concerns though. Does the tool generate less server load on the initial partitioning than if you do it directly through T-SQL? If not or not sure, what's the point? – TToni Jul 18 '13 at 14:30

Just applying the partition to the clustered index is probably your best option. The majority of the physical data has to be moved anyway (assuming that you put the older data into another file group - which you should). So using temp tables etc. would accomplish nothing that the SQL Server doesn't do internally already - probably more efficiently as if you do it manually.

Your I/O system will see mainly sequential writes as the new structure is written, so even if you are hard disk based this operation should be pretty fast. Depending on how much re-sorting the system has to do you may see heavy use of the tempdb though. Can you show us the estimated Execution Plan of at least one of the partition statements?