Table Partitioning in SQL Server 2005 Part 2
Last time we looked at the building blocks for a partitioned table in SQL Server 2005. I decided to break out maintenance activities for the next post. Today we will be discussing the various functions and management views available for you to interact with your partitioned table.
Keeping the Invoice table from our last post I added some test data with the following code (edited for brevity).
INSERT dbo.Invoice (InvoiceID, CustomerID, InvoiceDate, Amount)
VALUES(1, 1, '2008-01-01 00:00:00', 100)
GO 10
INSERT dbo.Invoice (InvoiceID, CustomerID, InvoiceDate, Amount)
VALUES(2, 2, '2008-02-01 00:00:00', 200)
GO 20
...
INSERT dbo.Invoice (InvoiceID, CustomerID, InvoiceDate, Amount)
VALUES(11, 11, '2008-11-01 00:00:00', 1100)
GO 110
INSERT dbo.Invoice (InvoiceID, CustomerID, InvoiceDate, Amount)
VALUES(12, 12, '2008-12-01 00:00:00', 1200)
GO 120
The code above uses the GO syntax I blogged about earlier to insert disparate numbers of rows into each partition. This is done for easier allocation viewing later.
To view the allocation of rows across your partition you have multiple options. You can make use of the $partition system function, the sys.dm_db_partition_stats management view, or the sys.partitions catalog view. Each has their own flavor, with positives and negatives.
$partition system function
This system function returns the partition number into which a set of partitioning column values would be mapped for any specified partition function. The function returns an integer value between 1 and number of partitions of the partition function. The general syntax for use of this function is:
[ database_name. ] $PARTITION.partition_function_name(expression)
The following query returns the rows for our Invoice table.
SELECT
$partition.DateRangePFN(InvoiceDate) AS PartitionNumber
,COUNT(InvoiceID) AS NumberOfRows
FROM
dbo.Invoice
GROUP BY
$partition.DateRangePFN(InvoiceDate)
ORDER BY
PartitionNumber;
Running this code you would notice that any empty partitions are not returned. If you are interested in all partitions for your table you will want to make use of one of the following options.
sys.dm_db_partition_stats
This database related dynamic management view returns page and row counts for every partition. The following query returns the row count for our Invoice table.
SELECT
partition_number
,row_count
FROM
sys.dm_db_partition_stats
WHERE
[object_id] = OBJECT_ID('dbo.Invoice', 'U');
This output gives us a better view of the overall allocations for our table. However, what if you also wanted index granularity in your counts? For that we go to option three.
sys.partitions
This object catalog view contains a row for each partition of all the tables and indexes in a database. Note that all tables and indexes in SQL Server 2005 are considered to contain at least one partition, even if they are not explicitly partitioned. The following query returns the row count for our Invoice table.
SELECT
OBJECT_NAME(p.[object_id]) AS ObjectName
,i.[name] AS IndexName
,p.partition_number
,[rows]
FROM
sys.partitions AS p
INNER JOIN sys.indexes AS i ON p.[object_id] = i.[object_id]
AND p.[index_id] = i.[index_id]
WHERE
p.[object_id] = OBJECT_ID('dbo.Invoice', 'U');
This output gives us the most granular view of allocations across our table.
SQL Server 2005 also introduces four other catalog views for managing your partition implementation:
I have included links to BOL for these functions as their simplicity doesn't really warrant further discussion.
Now you have full view into your partitioning implementation and can feel comfortable that your planned allocations are working. Next time we will discuss maintenance activities to keep your partitions up and running at their best.
Enjoy!