This is one of those recent crazy cases where I was working.
Customer had a table of 1.5 TB in size with multiple partition (yearly partitioning strategy). Even though Microsoft always recommends that the last partition should be empty, they could not follow the strategy and added data in the last partition and it was more than 100GB at the time of call.
When they further tried using SPLIT function to create a new empty Partition so that they can move the 2011 data to a new partition, it was taking lot of IO and it was completing after almost 20+ hours. They wanted to know what was actually happening in the Partition Split and how to reduce the long processing time.
Well this was one of those requests where recommendations were not followed and when there is an issue, explanation are expected. I really did not have any other option except looking at some internals. Mainly I referred few articles written by:
Kimberly Tripp: http://msdn.microsoft.com/library/ms345146.aspx
Kalen Delaney: http://sqlblog.com/blogs/kalen_delaney/archive/2009/08/16/altering-a-partition-function.aspx
I even looked at all the documentations which could help but alas, there is not much which could be shared. So here all what I could find:
Overall to summarize, we commented on their approach. We mentioned that splitting the data partition is wrong and particularly with huge amount of data in the last partition, it is expected to give high IO usage and it would take longer time. We recommended them to go for Sliding Windows Approach with Partition Switch and follow archival strategy than keeping all the data in one table. Another alternate option what we suggested them to review is documented in Partitioning White Paper (Page 51).
Nice article! I liked the title. :)