OK, you know how to set up partitioning for a table and an index, and now you think you’ve solved your deadlocking, and your I/O issues forever, right?

Hardly.

Once you’ve been in SQL long enough you learn that no answer is forever.  Inevitably you’ll have to revisit your old queries and see if their performance has degraded.  Partitioning is no different.  There are several issues you may need to solve with partitioning in the long run.  The first scenario is really easy to understand.

Let’s say you have the partitions set up like we did back in my Partitioned Tables and Indexes article.  Your items table has grown and grown over the past year, and you notice that your data is starting to get heavy in one particular filegroup.

SPLIT

DECLARE @totalCount DECIMAL(9,2)
SELECT @totalCount = COUNT(*) FROM items

SELECT
 CASE WHEN manufacturerID <= 10 THEN 'fg1' else
   CASE WHEN manufacturerID between 11 and 20 THEN 'fg2' else
     CASE WHEN manufacturerID between 21 and 30 THEN 'fg3' else
       CASE WHEN manufacturerID between 31 and 40 THEN 'fg4' else
         CASE WHEN manufacturerID > 50 THEN 'fg5' END
       END
     END
   END
 END as filegroup
 , CONVERT(DECIMAL(9,2), count(*))/ @totalCount
FROM items
GROUP BY
  CASE WHEN manufacturerID <= 10 THEN 'fg1' else
    CASE WHEN manufacturerID between 11 and 20 THEN 'fg2' else
      CASE WHEN manufacturerID between 21 and 30 THEN 'fg3' else
        CASE WHEN manufacturerID between 31 and 40 THEN 'fg4' else
          CASE WHEN manufacturerID > 50 THEN 'fg5' END
        END
      END
    END
  END

When you run this, you notice even with 5 partitions you have much more than half of your records are in the last group.  This tells you you need to dig into that last group and figure out where you need to add a partition to your table.  You could then run another query to look at the distribution in that last group by groups of 10

SELECT 
 (manufacturerID/10) + 1 AS filegroup
 , CONVERT(DECIMAL(9,2), count(*))/ @totalCount
FROM items
GROUP BY
 (manufacturerID/10) + 1

After looking into this you notice that over time you’ve added more manufaturers in this range, and you can effectively split the load for this file group if you set a boundary at 75.  That would put half of fg5 into a new partition, and the load would be split evenly again.  But how could you add a partition?

Well, in order to add a partition, you’re going to have to assign it’s data to a filegroup.  I’m going to assume all the filegroups you have defined for your current database are already in use.  We’re going to need a new filegroup.

ALTER DATABASE KOTOR
ADD FILEGROUP fg6
   (NAME = fg6_data, FILENAME = 'd:\mssql\data\kotor_fg6.ndf', SIZE = 10MB)

Before you can use that filegroup in your partition, you’ve got to alter the PARTITION SCHEME.  You need to identify it as the next filegroup to use when you alter your PARTITION FUNCTION.  To do that, you need to ALTER the PARTITION SCHEME and identify the next filegroup to use.

ALTER PARTITION SCHEME myPartitionScheme
NEXT USED fg6

Now that you have identified the new filegroup as the NEXT USED filegroup, you can define the new boundary to your PARTITION FUNCTION.  The SPLIT operator allows you to define a new boundary point to your PARTITION FUNCTION.

ALTER PARTITION FUNCTION myPartitionFunction(int)
SPLIT RANGE (75)

This now adds 75 as a new boundary to the PARTITION FUNCTION we originally created in this article.

MERGE

IF you wanted to remove a boundary instead.  Let’s say you have a boundary in use that is completely empty, and you want to stop using that partition, to free it up for some reason, then you want to use the MERGE operator. The syntax is just like SPLIT, except the boundary point you pass will be removed from your PARTITION FUNCTION.

SWITCH

Now before we go, I’d like to really blow you away.  How do you archive data?  SELECT it from one table into an ARCHIVE table, then delete it from the original table?  What if your database is highly available and you can’t get a lock on the table to do that?

Well, if you partitioned your table, you’ve got a very powerful little tool available to you.  You can actually move a partition to a new table then you’ve effectively detached that part of the table from the original table.

You read that right.  You move a partition from one table to another, and the rest of your data remains untouched.  If you deal with sales data, you’ve probably already partitioned your table on a DATE or DATETIME field, right?  Once you move beyond a certain period and you’re ready to archive that data, you can use this trick.

Script out a create statement for your table, give the table a new name, tableNameArchive.  Then, SWITCH the partition to that new table.

ALTER TABLE tableName
SWITCH PARTITION x TO tableNameArchive

Where x is the number of the partition containing the data you want to archive (this would usually be the first or oldest partition).  Then, MERGE that partition’s boundary in the first table, since you no longer need it.

ALTER PARTITION FUNCTION partFunction()
MERGE RANGE (value)

Check out the data in your tableNameArchive table… it’s your archived data!  Pretty slick, right?  This is a pretty complex use of partitions.  I think I should put together a demo of it in use, so you can work through it.  What do you think?  Do you want a practice problem?  If so, let me know!

If you have questions on this, or any other SQL topic, send them in.  I’m here to help!