Most of us are aware of partitioning in SQL Server. It was introduced with SQL Server 2005 and then further enhanced in later releases. The value of partitioning a table can vary. Some use it as a means of archiving off older data, others use it for the sake of performance gains, either way, there are some important things to keep in mind when one partitions a table.
One of those items brings us to the point of the post, aligning your non-clustered indexes on the partition scheme. This is an important rule and more often than not it should be followed.
One of the reasons that it’s important is if you ever want to switch partitions, all of the indexes associated to that object must be aligned. ref: http://msdn.microsoft.com/en-us/library/ms187526.aspx
Another reason is around maintenance. If the indexes are partitioned then only the active partition (based on your partitioning strategy) needs to be maintained (re-org’d). For larger indexes, it can become prohibitive to re-build these when they become fragmented.
Probably the best reason to have your indexes aligned is that the amount of reads to satisfy your queries will significantly drop when that index is used.
I recently came across a scenario where I found that there were a couple of tables where the indexes weren’t aligned with the partition scheme. Instead of going through every partitioned table in the database, I wrote a quick query that would tell me which objects had indexes that weren’t created on the partition scheme for the table.
This query will also show you the usage of the indexes which can be interesting as well. Without further ado, here’s the query:
SELECT Db_name(database_id) AS dbname, o.name object_name, i.name index_name, i.type_desc, ds.name, ds.type_desc, 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 WHERE o.TYPE = 'u' AND i.Type in (1, 2) AND o.name IN (SELECT a.name FROM (SELECT ob.name, 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.name, ds.type_desc) a GROUP BY a.name HAVING COUNT(*) > 1) ORDER BY object_name DESC
That’s fun stuff… But then what happens when you actually want to FIX them? If there are a lot of them, it makes for a fairly tedious task. The code below will help. Be absolutely certain that you check the options chosen. For my database, I used MAXDOP = 32, ONLINE = ON, etc… Ensure that the the settings are appropriate for your environmnent… enjoy!
SET NOCOUNT ON SELECT db_name(database_id) as DBName,o.name Object_Name, i.name Index_name, i.Type_Desc as IDX_typeDesc ,DS.name , ds.type_desc ,sch.name + '.'+o.name as SchemaName_TableName, CAST(NULL as varchar(128)) as partitonOrdinal into #tmp1 FROM sys.objects AS o JOIN sys.indexes AS i ON o.object_id = i.object_id JOIN sys.schemas sch on sch.schema_id = o.schema_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 WHERE o.type = 'u' AND i.type IN (1, 2) -- Clustered and Non-Clustered indexes AND O.NAME in ( SELECT a.name from (SELECT OB.NAME, 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.name, ds.type_desc ) a group by a.name having COUNT (*) > 1 ) order by Object_Name desc; update a set partitonOrdinal = c.name FROM #tmp1 A JOIN sys.tables t ON A.Object_Name = t.name join sys.schemas s on s.schema_id = t.schema_id join sys.indexes i on(i.object_id = t.object_id and i.index_id < 2) join sys.index_columns ic on(ic.partition_ordinal > 0 and ic.index_id = i.index_id and ic.object_id = t.object_id) join sys.columns c on(c.object_id = ic.object_id and c.column_id = ic.column_id) WHERE I.name = a.Index_name and a.IDX_typeDesc = 'clustered' ; update a set name = b.name, partitonOrdinal = b.partitonOrdinal FROM #tmp1 a join #tmp1 b on a.object_name = b.object_name where b.idx_typedesc = 'clustered'; WITH index_cte (table_name, index_name, type_desc, index_columns, index_included_columns, filter_definition, is_unique, is_primary_key, is_unique_constraint,fill_factor, is_padded, allow_row_locks, allow_page_locks, ignore_dup_key, data_compression_desc, no_recompute, partitionname, partitonOrdinal) as( select sch.name+'.'+obj.name [table_name], i.name [index_name], i.type_desc, REPLACE( (select '['+col.name+'] '+ CASE WHEN k.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END as [data()] from sys.index_columns as k inner join sys.columns col on k.object_id=col.object_id and k.column_id=col.column_id where k.object_id = i.object_id and k.index_id = i.index_id and k.index_id<>0 and k.is_included_column<>1 order by key_ordinal, k.column_id for xml path('')) ,'C [','C, [') as [index_columns], isnull( REPLACE( (select '['+col.name+']' as [data()] from sys.index_columns as k inner join sys.columns col on k.object_id=col.object_id and k.column_id=col.column_id where k.object_id = i.object_id and k.index_id = i.index_id and k.index_id<>0 and k.is_included_column<>0 order by key_ordinal, k.column_id for xml path('')) ,'] [','], [') ,'') as [index_included_columns], ISNULL(i.filter_definition,'') as filter_definition, is_unique, is_primary_key, is_unique_constraint, fill_factor, is_padded, allow_row_locks, allow_page_locks, ignore_dup_key, data_compression_desc, no_recompute, a.name, a.partitonOrdinal from sys.indexes as i inner join sys.objects obj on i.object_id=obj.object_id inner join sys.schemas sch on obj.schema_id=sch.schema_id inner join sys.partitions part on i.object_id=part.object_id and i.index_id=part.index_id inner join sys.stats stats on i.object_id=stats.object_id and i.index_id=stats.stats_id JOIN #tmp1 a ON a.index_name = i.name AND a.object_name = obj.name and a.type_desc = 'rows_filegroup' where i.name is not null and obj.type<>'S' and sch.name<>'sys' and i.index_id>1 ) SELECT 'CREATE ' + CASE is_unique WHEN 1 THEN 'UNIQUE ' ELSE '' END + index_cte.type_desc +' INDEX ' + index_cte.index_name COLLATE DATABASE_DEFAULT +' ON ' +table_name +' ('+index_columns+')'+ CASE index_included_columns WHEN '' THEN '' ELSE ' INCLUDE (' + index_included_columns + ') ' END+ CASE filter_definition WHEN '' THEN '' ELSE ' WHERE ' + filter_definition END+ ' WITH ('+ 'FILLFACTOR = ' + CASE fill_factor WHEN 0 THEN '100' ELSE CAST(fill_factor as varchar) END+ ', PAD_INDEX = ' + CASE is_padded WHEN 1 THEN 'ON' ELSE 'OFF' END+ ', ALLOW_PAGE_LOCKS = ' + CASE allow_page_locks WHEN 1 THEN 'ON' ELSE 'OFF' END+ ', ALLOW_ROW_LOCKS = ' + CASE allow_row_locks WHEN 1 THEN 'ON' ELSE 'OFF' END+ ', IGNORE_DUP_KEY = ' + CASE ignore_dup_key WHEN 1 THEN 'ON' ELSE 'OFF' END+ ', STATISTICS_NORECOMPUTE = ' + CASE no_recompute WHEN 1 THEN 'ON' ELSE 'OFF' END+ ', DATA_COMPRESSION = ' + data_compression_desc + ', SORT_IN_TEMPDB = ON'+ ', ONLINE = ON'+ ', MAXDOP = 32'+ ', DROP_EXISTING = ON'+ ')' + ' ON ' + partitionname + ' (' + partitonOrdinal + ') GO' as 'USE Database statement goes here (TNW)' from index_cte order by table_name, index_cte.type_desc, index_cte.index_name drop table #tmp1