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 | |
| , partitionds.name 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 | |
| i.index_id | |
| 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; | |
| IF EXISTS ( SELECT * | |
| FROM #erroralert ) | |
| BEGIN | |
| SELECT DISTINCT TableName | |
| , AllocDataSpace | |
| , PartDataSpace | |
| , AllocPartNum | |
| , PartNum | |
| , AuFilegroup | |
| , PartFilegroup | |
| FROM #erroralert; | |
| PRINT 'ALERT DBA Here'; | |
| END; |
