We have had an index job that has been failing for a while. This is one of those things you really don’t want to clean up because no one is complaining, but you know you should. I had heard that I could rebuild one partition at a time, but where to start? Today, I worked my way through it, so here it is so that you can do it too.
First you need to find the biggest indexes, there is a good chance those are the ones that live on partitions. I am removing Primary Keys.
SELECT i.[name] AS IndexName ,SUM(ps.[used_page_count]) * 8 AS IndexSizeKB FROM sys.dm_db_partition_stats AS ps INNER JOIN sys.indexes AS i ON ps.[object_id] = i.[object_id] AND ps.[index_id] = i.[index_id] WHERE i.name NOT LIKE 'PK%' GROUP BY i.[name] ORDER BY IndexSizeKB DESC
The top ones are most likely the ones you want to focus on. Next, we need to track down the partition function.
SELECT * FROM sys.partition_functions
Next we are going to figure out which partition we want to rebuild.
SELECT sprv.value AS [Value], sprv.boundary_id AS [ID] FROM sys.partition_functions AS spf INNER JOIN sys.partition_range_values sprv ON sprv.function_id = spf.function_id WHERE (spf.name = N'NameOfFunctionFromPartitionFunctions') ORDER BY [ID] ASC
Pick the number of the partition you want to rebuild.
Next use your index name from earlier. You will also need the table name and the partition number.
ALTER INDEX [IndexName] ON [dbo].[TableName] REBUILD PARTITION = 3
This will rebuild just the partition that you requested.