Are Your Partitions on the Correct Filegroup?

Last summer we discovered that some records that had been migrated to a read-only filegroup via partitioning were still getting updated (here’s the sordid details on stack overflow). Working with the SQLCat team at PASS in October we discovered that while the partitioning scheme indicated the records should be on the read-only partition according to the allocation unit mapping showed the records will still resident on the active filegroup. For unknown reasons during partition maintenance the records hadn’t moved properly. Working with MS Support we weren’t able to replicate the issue or determine how it happened. Even more insidious is that there is no good way to detect if the issue is occurring unless you go specifically looking for it. It only came to our attention because we were setting some records to read only. If you are doing partition maintenance which moves records physically on the disk you might be interested in the following query which will detect when there is a disconnect between where your partition scheme thinks records will reside and where sys.allocation_units tells you those same records reside:

/* returns all partitioned tables: */
SELECT OBJECT_NAME(p.[object_id]) AS 'TableName'
, i.[name] AS 'IndexName'
, i.index_id
, p.[object_id]
, au.data_space_id
, p.partition_number
, p.hobt_id
, AS partition_filegroup
INTO #partDist
FROM sys.allocation_units au
JOIN sys.partitions p ON au.container_id = p.[partition_id]
LEFT JOIN sys.destination_data_spaces dds ON p.partition_number = dds.destination_id
LEFT JOIN sys.data_spaces partitionds ON dds.data_space_id = partitionds.data_space_id
LEFT JOIN sys.indexes i ON p.[object_id] = i.[object_id]
JOIN sys.partition_schemes ps ON dds.partition_scheme_id = ps.data_space_id
AND i.data_space_id = dds.partition_scheme_id
LEFT JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
WHERE i.index_id = ( SELECT TOP 1
FROM sys.indexes inner_i
WHERE inner_i.[object_id] = i.[object_id]);
/* Check for records where partition numbers match but partition filegroup names do not: */
SELECT pd.TableName
, pd.IndexName
, au.data_space_id 'AllocDataSpace'
, pd.data_space_id 'PartDataSpace'
, au.partition_number 'AllocPartNum'
, pd.partition_number 'PartNum'
, au.partition_filegroup 'AuFilegroup'
, pd.partition_filegroup 'PartFilegroup'
INTO #erroralert
FROM #allocUnits au
JOIN #partDist pd ON au.object_id = pd.object_id
AND au.partition_number = pd.partition_number
AND pd.index_id = au.index_id
WHERE au.partition_filegroup <> pd.partition_filegroup
ORDER BY pd.TableName
, au.index_id
, au.partition_number;
FROM #erroralert )
, AllocDataSpace
, PartDataSpace
, AllocPartNum
, PartNum
, AuFilegroup
, PartFilegroup
FROM #erroralert;
view raw getPartitionedTables hosted with ❤ by GitHub

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.