Rebuilding Indexes By Partition

Andrea Allred shows how to rebuild indexes one partition at a time:

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.

This can be a real time-saver if a majority of your partitions either are read-only or at least rarely update.

Related Posts

Max And Min Partition Values

Ken Kaufman explains a major performance problem when trying to get maximum (or minimum) values from a partitioned table: Now that I rambled a bit you want to know why when using a partitioned table does grabbing the min and max of the primary key take sooooo long, and how do you fix it.  Theoretically […]

Read More

Resumable Online Index Rebuild

Arun Sirpal shows off a SQL Server 2017 and Azure SQL Database feature: Clearly I am in a paused state with 5.48% percent of the operation complete. In the original query window where I started the index rebuild, I receive: Msg 1219, Level 16, State 1, Line 4 Your session has been disconnected because of […]

Read More

Categories

February 2016
MTWTFSS
« Jan Mar »
1234567
891011121314
15161718192021
22232425262728
29