Partitioned Tables with Non-Aligned Indexes
sp_Blitz Result: Partitioned Tables with Non-Aligned Indexes
Table partitioning is a complex way to break out your large tables into smaller, more manageable chunks, but it comes with a lot of management heartache. One of the challenges is making sure that your indexes are partitioned and aligned the same way as your clustered index. We’ve written a lot about the pros and cons of table partitioning.
This part of our SQL Server sp_Blitz script checks the DMVs sys.objects, sys.indexes, and sys.data_spaces to see if they line up and have the same number of partitions. It only checks at the database level – to see the individual objects having the problem, run this diagnostic query – and thanks to Alin Selicean for improving this query!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
SELECT ISNULL(db_name(s.database_id),db_name()) AS DBName ,OBJECT_SCHEMA_NAME(i.object_id,DB_ID()) AS SchemaName ,o.name AS [Object_Name] ,i.name AS Index_name ,i.Type_Desc AS Type_Desc ,ds.name AS DataSpaceName ,ds.type_desc AS DataSpaceTypeDesc ,s.user_seeks ,s.user_scans ,s.user_lookups ,s.user_updates ,s.last_user_seek ,s.last_user_update FROM sys.objects AS o JOIN sys.indexes AS i ON o.object_id = i.object_id JOIN sys.data_spaces ds ON ds.data_space_id = i.data_space_id LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s ON i.object_id = s.object_id AND i.index_id = s.index_id AND s.database_id = DB_ID() WHERE o.type = 'u' AND i.type IN (1, 2) AND o.object_id in ( SELECT a.object_id from (SELECT ob.object_id, ds.type_desc from sys.objects ob JOIN sys.indexes ind on ind.object_id = ob.object_id JOIN sys.data_spaces ds on ds.data_space_id = ind.data_space_id GROUP BY ob.object_id, ds.type_desc ) a GROUP BY a.object_id HAVING COUNT (*) > 1 ) ORDER BY [Object_Name] DESC; GO |
To Fix the Problem
Depending on the size of the data involved, this could be a pretty big problem that involves taking a maintenance window. Time to hit the books – start reading our partitioning resources.