Finishing up the posts on table partitioning here, we will be looking at maintenance activities today.  Two of the main areas of maintenance for your database have to do with indexes and backups.  Partitioning helps shorten potential maintenance windows for both of these activities.  We will start with indexes.

Rebuilding or reorganizing indexes on large tables can take quite a long time to process, negatively affecting the availability of your database.  In SQL Server 2005 we have the ability to rebuild a stated partition number of an index.  For an example we will go back to our Invoice table illustrated earlier.  We will add a clustered index on the datetime column, which also happens to be our partitioning key.  As the table is already partitioned, the syntax is no different than creating an index on a non-partitioned table.

--Create a clustered index on the InvoiceDate column
CREATE CLUSTERED INDEX IXC_Invoice_InvoiceDate ON dbo.Invoice (InvoiceDate);

If we had not already partitioned the table we could have created the clustered index adding the partitioning scheme and effectively implemented it that way.

We can verify the layout of the index by querying the sys.dm_db_partition_stats management view.

--Verify index
SELECT 
    i.[name] AS indexName
    ,i.type_desc AS indexType
    ,s.partition_number AS partitionNumber
    ,s.row_count AS numRows
FROM 
    sys.dm_db_partition_stats AS s
    INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]         
WHERE
    s.[object_id] = OBJECT_ID('dbo.Invoice', 'U')
    AND s.index_id = i.index_id;

The results of the query show the index and the row counts for the Invoice table.

To check fragmentation on our partitions we can run the following query against the management function sys.dm_db_index_physical_stats.

--Check index fragmentation
SELECT 
    OBJECT_NAME(i.[object_id]) AS objectName
    ,s.index_type_desc AS indexType
    ,s.partition_number AS partitionNumber
    ,ROUND(s.avg_fragmentation_in_percent, 2) AS indexFragmentationPercent
FROM 
    sys.dm_db_index_physical_stats 
        (
        DB_ID()
        ,OBJECT_ID('dbo.Invoice', 'U')
        ,NULL
        ,NULL
        ,NULL
        ) AS s
    INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
WHERE
    s.index_id = i.index_id;

Once we determine that maintenance is needed for our index we have two options.  We may choose either the REBUILD or REORGANIZE statements of ALTER INDEX.  These two options replace DBCC DBREINDEX and DBCC INDEXDEFRAG.  For full definition of both features see the ALTER INDEX entry in BOL.  Both options allow us to alter a stated partition number of an index.  The syntax for this is:

--REBUILD index on partition number 1
ALTER INDEX IXC_Invoice_InvoiceDate ON dbo.Invoice REBUILD PARTITION = 1;

--REORGANIZE index on partition number 1
ALTER INDEX IXC_Invoice_InvoiceDate ON dbo.Invoice REORGANIZE PARTITION = 1;

This syntax allows us to stagger maintenance of large tables to better serve our application's needs.  We also have the benefit of only doing maintenance on the partitions that are experiencing fragmentation.  As data ages in your application you should see less and less fragmentation in your partitions.  Eventually you should be able to remove those partitions completely from maintenance activities.

This takes us nicely into our other maintenance topic, back up.  If you follow best practice recommendations mentioned earlier in this blog topic and create separate filegroups for each of your partitions you can implement filegroup back ups of your database.  The syntax for a filegroup back up is:

--Backup indvidual filegroup
BACKUP DATABASE PartitionDB 
FILEGROUP = 'FG200801' 
TO DISK = 'C:\SQLData\FG200801.BAK';

To utilize filegroup back ups you will need to be in FULL recovery mode and take log back ups.  For full discussion of back up activities see the entry in BOL.

Again, we are able to take advantage of our data aging and move stable data to read-only filegroups.  Once the filegroup has been marked read-only we have effectively removed it from our maintenance cycle.

So now we have fully implemented a partition table in our database.  Hopefully this discussion will help you out in your own schemas.  If you are interested in a white paper version of this blog series go to the email section and drop me a line.

Enjoy!