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!

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

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;

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.

Return to sp_Blitz or Ask Us Questions