Rebuild Very Large Primary Key Index

Question

I have a SQL database that is hosted on Azure. The problem is that the size is getting out of control, I can see up to 99% fragmentation in the Primary Key clustered indexes.

I'm able to rebuild all other indexes with online=on option and it won't affect performance. The size of one of the PK Clustered indexes is greater than 200GB, and for this one a REBUILD...WITH (ONLINE=ON) causes locking.

We do have users from all timezones accessing the site so really, I'm unable to find a time where I can rebuild the index offline.

What is the best strategy to rebuild large indexes without having a downtime in the site?

I believe reorganize won't help since fragmentation is 99%. The problem is that the table gets locked even with online. The main problem is that the index is greater than 200GB. The primary key is an integer.

Answer 1

Even though it's a bit late, I'm going to field a response with hope that it helps or at least spurns some additional ideas/commentary on this issue because I think it's a good question.

First, and I don't know if you're doing this or not, but please don't assume that high fragmentation levels on the index are always going to cause poor performance. Stale statistics (e.g. sys.dm_db_stats_properties) and high amounts of white space per page (i.e. avg_page_space_used_in_percent column in sys.dm_db_index_physical_stats dmv) hold more relevance regarding performance issues than fragmentation alone. Yes, highly fragmented indexes will generate more read-aheads and you typically do see stale statistics and higher levels of white space per page coupled with fragmentation, but fragmentation isn't directly tied to query plan optimizations nor how much memory loading the index from disk will actually consume. Query plans are affected by statistics and your memory footprint bloats with more white space. For instance, an index that is 99% fragmented but has less than 5% avg. white space and up-to-date statistics is likely not causing you drastic performance issues as compared to either a bad execution plan as a result of stale statistics or constant paging of an index that's too big to fully fit in memory because there's a significant amount of white space present per page.

If fragmentation is truly an issue, you can reduce it, ONLINE, by issuing an ALTER INDEX ... REORGANIZE statement as identified by Dan Guzman in the comments. This won't create as streamlined an index as a REBUILD operation will, but it will reduce your fragmentation. The key here is to identify windows of lower usage on your database and run it then. This could be 15 minutes or multiple hours, obviously the longer the better, but the key here is this operation doesn't rollback and retains any progress made even if you kill it mid-execution.

If, in a perfect world where your fragmentation was eliminated, would it make more sense to utilize partitioning on this table? Azure SQL Database does allow for table partitioning and Microsoft has a great article outlining some Partitioning strategies for Azure SQL Database. If your data is non-volitile, partitioning it may help reduce maintenance needs, and if coupled with Table Compression, you may even be able to reduce your overall storage footprint as well. Alberto Murillo's earlier answer alludes to utilizing Horizontal Partitioning based on a data region, and this approach may help create some maintenance windows for you as your data would be more regionally specific instead of global.

Transitioning to a partitioned table won't be easy with your current absence of maintenance windows, but you may be able to utilize an approach outlined by Maria Zakourdaev which uses Partitioned Views over the top of your current table and a new partitioned table to start partitioning future data. As time goes on (and hopefully your old data is purged), you can eventually transition fully over to the partitioned table. Again, I don't know your data or application, but maybe this approach is something you can employ.

Answer 2

First, it's important to consider whether fragmentation matters.

If your query is only doing single-row seeks, you might not notice fragmentation at all. On modern SANs, the SAN-level caching may make phyiscal IOs fast enough that fragmentation doesn't matter. On SSD, the random IO pattern caused by scanned a fragmented index may actually result in better performance than non-fragmented data.

Often times, people notice that rebuiliding an index fixed a performance problem. Rebuilding an index also builds fresh statistics. It may be the case that the real fix is fresh statistics, not rebuilding the index. UPDATE STATISTICS...WITH FULLSCAN may be a cheaper, faster, less intrusive way to solve the same performance problem.

If you are not having problems caused by fragmentation, then you could be spending significant time & effort for no actual gain.

Second, there are two kinds of fragmentation:

1. Physical fragmentation. This is what most people think of when they think of fragmentation. Pages are out of order, and need to be re-ordered. When scanning an index this type of fragmentation can sometimes be a problem. I've generally noticed this has the largest impact on performance with physical reads. If you are looking at the results from sys.dm_db_index_physical_stats, this number is the avg_fragmentation_in_percent column.

2. Low-density fragmentation. This fragmentation is caused by pages that are only partially filled with data. You have low density of data because your data is spread across more pages than necessary. As a result, reading the data requires more IOs because the data is spread across more pages than necessary. This can affect both logical and physical reads. If you are looking at the results from sys.dm_db_index_physical_stats, this number is the avg_page_space_used_in_percent column. This column is only populated when using SAMPLED or DETAILED mode.

So what do you do about it:

Physical Fragmentation: If you are simply chasing high numbers for avg_fragmentation_in_percent, really consider if you're wasting your time. Make sure that you have an actual query that is performing poorly, and use a test environment to confirm that you're fixing a problem by eliminating fragmentation.

You can address physical fragmentation by doing ALTER INDEX...REORGANIZE. The REORGANIZE operation is online, moving pages one at a time to reorganize them back into physical order. If you kill a REORGANIZE statement part way through, any work that was already performed is maintained--only the one page currently being moved will be rolled back. Doing a REORGANIZE on a large table that is highly fragmented can require more total transaction log space, and in full recovery mode may generate a significant amount of transaction log backups. It may also take longer to REORGANIZE a highly fragmented index than to REBUILD it.

You will often see advice to perform a REBUILD on highly-fragmented indexes, rather than a REORGANIZE -- This is because rebuilding from scratch can be more efficient. However, reorganizing can be a "more online" operation and is sometimes preferred, even for highly fragmented indexes.

Low-density fragmentation cannot be fixed by REORGANIZE. It can only be fixed by doing an ALTER INDEX...REBUILD. By doing the index with ONLINE=ON, you should be able to minimize blocking. However, the REBUILD still needs to take a lock for a moment to swap the old index for the new index. On a very busy system, attaining this exclusive lock can sometimes be a problem. You should be able to confirm if you are having this issue by using something like sp_whoisactive to examine blocking during your rebuild, and looking at the details of the locks & waits. Using the WAIT_AT_LOW_PRIORITY option may be useful if you know that there is an upcoming period of low utilization, and your rebuild can "sneak in" for this swap when activity drops low enough to attain that lock. Note that a long-running REBUILD operation is also going to be a long-running open transaction. Long-running open transactions can have their own problems, related to transaction log use/reuse. If you are using mirroring or Availability Groups, there are also considerations for transaction log redo on the secondary replica.

Answer 3

Notice

After this comment:

You will lose rows that are inserted during the copy. If you want to prevent this by locking the table then you end up with the same problem as the OP stated in his question. Also 200Gb will not come for free :-) – Marco Sep 5 '17 at 11:18

...I see how this approach will not work.

I'll leave this answer up as an example of what not to do.

---

If you have a spare 200+ GB free on your Azure DB, you can get sneaky with the "rebuild", by copying your data to a totally new table and ordering it there.

Try:

• scripting your LiveTable into an empty NewTable
• copying the LiveTable into the NewTable
• renaming LiveTable to OldTable
• renaming NewTable to LiveTable

Obviously, use your table's name instead of LiveTable.

Answer 4

Ideally, if an index is well designed, we shouldn't need to fiddle with the locking mechanism.

It sounds to me like you'll need to accept the locking to defrag the clustered index. If there's a good chance of this happening again, then look at redesigning the clustered index (it should be narrow, unique, static and ever-increasing).

I'm not sure what version of SQL Server you are using, but you could try the following in 2012:

• SET DEADLOCK_PRIORITY LOW - This tells the engine that the index rebuild should be the deadlock victim when/if one occurs.

• MaxDOP = 1 - The MaxDOP value limits the total number of logical CPUs used in parallel to create the index (2005 upwards - Enterprise edition only).

You can also change the page/row locks configuration, but I wouldn't do that without testing. You could just make the locking worse, especially if it's a badly designed index.

In 2014 onwards, there is the following option that basically tells the engine to allow other sessions to proceed and the online index operation to wait:

(WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF))

Answer 5

I have used the same approach as Oreo described above with great success! The only thing that is missing is that you need to run an update script after you have copied the data and made the last renaming.

The update will look like this:

Insert from OldTable into LiveTable
  Where not exists (select From OldTable Where LiveTable.Key = OldTable.Key)

If Key is an Identity column, you need to use a slightly different approach.

Answer 6

Try to use sharding to distribute data of your database geographically. You then will be able to identify different maintenance windows for each geographic location, and the time to make maintenance will be shorter. This will also improve performance. You can learn more on this article. Do not wait for the database to get bigger.

With big databases and users connected 24 x 7, you need to use index reorganize and update only statistics that need to be updated (sp_updatestats) to minimize the time needed for maintenance and the impact to users.

Hope this helps.