THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Merrill Aldrich

Technical content about Microsoft data technologies. All opinions expressed are purely my own and do not reflect positions of my employer or associates.

Are You Sure Those Rows are in That Partition?

Lately I have been working with a database that has a comprehensive table partitioning requirement. In order to do troubleshooting, and to test partition switching and data archiving code, we needed a quick but accurate way to view what rows are in which partition for each table. Importantly, we needed to align the filegroup/disk where the rows are all the way back to the value in the partition function that causes the rows to land where they land. In short, “Where are the rows on disk for December 2012 for this table? How many are there?”

There are some examples of how to join the DMVs out on the web, but there seemed to be one detail missing from a lot of them: the number of partitions is always one more than the number of boundary values in a partition function. The arrangement is really like this:

   ROWS     |     ROWS     |     ROWS     |     ROWS     |     ROWS     |     ROWS     |     ROWS

PARTITION      PARTITION      PARTITION      PARTITION      PARTITION      PARTITION      PARTITION    
         BOUNDARY       BOUNDARY       BOUNDARY       BOUNDARY       BOUNDARY       BOUNDARY   

The number of boundaries in a partition function is (number of partitions) - 1. That means that if you query the various DMVs for partition function, partition scheme, boundary values, partitions and filegroups all joined together, it’s deceptively easy to be off by one, and mistake having rows for, say, 2012-01 in partition 6 instead of where they really are in partition 7. If there are 10 million rows there, that be a major mistake.

For me the key to this is incorporating the RANGE RIGHT or RANGE LEFT value for the boundary, which determines if that boundary value belongs to the partition on its left or the partition on its right. That T-SQL option when you create the partition function is represented in the DMVs as the column sys.partition_functions.boundary_value_on_right.


Here’s an example that I think makes the partitions and the partition boundary values line up correctly, by incorporating the value of boundary_value_on_right to bump the partition_range_values boundary_id by one relative to sys.partitions partition_number.

SELECT schema_name( o.schema_id ) schemaname,
    o.name objectname, 
    i.name indexname,
    pf.name pfname, 
    ps.name psname, 
    ds.name dsname, 
    fg.name fgname,
    pv.boundary_id, 
    pv.value, 
    p.partition_number, 
    p.[rows]
    --, * 
FROM sys.objects o
    JOIN sys.indexes i on o.object_id = i.object_id
    JOIN sys.partitions p on i.object_id = p.object_id and i.index_id = p.index_id
    JOIN sys.data_spaces ds on i.data_space_id = ds.data_space_id
    JOIN sys.partition_schemes ps on ds.data_space_id = ps.data_space_id
    JOIN sys.partition_functions pf on pf.function_id = ps.function_id
    JOIN sys.destination_data_spaces dds on dds.partition_scheme_id = ps.data_space_id 
        AND p.partition_number = dds.destination_id
    JOIN sys.filegroups fg on dds.data_space_id = fg.data_space_id
    LEFT OUTER JOIN sys.partition_range_values pv on pv.function_id = pf.function_id
        AND p.partition_number - pf.boundary_value_on_right = pv.boundary_id 
WHERE i.index_id in ( 0, 1 )
    AND SCHEMA_NAME( o.schema_id ) = 'dbo'
    AND o.name = 'someTable'
    -- AND p.rows > 0
ORDER BY o.name,  SCHEMA_NAME( o.schema_id ), p.partition_number
OPTION (FORCE ORDER)

Happy partitioning!

Published Thursday, December 4, 2014 2:06 PM by merrillaldrich

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Privacy Statement