Rebuilding fragmented indexes on SQL Server

Question

We've got a SQL Server database powering our SAAS web app. It's a SQL Azure database, hosted on S3 Standard plan (100 DTU units - pretty suficient for what we need).

Our DB indexes fragment really quickly, to the point that after 3-4 days, a lot of our more used tables are usually over 40% fragmented. If left, after 2 weeks a lot would be ~90% fragemented. In our most used tables, we've got about 2 million rows.

So, to sort this, we've got a script that runs every 3 days, looking for indexes that are >20% fragmented, and we rebuild these (with online on).

The problem with that is while it's running, the app becomes very slow and unresponsive for about 2 hours while it does it's thing.

Is there a better strategy to deal with this fragmentation of indexes, or anything else I could look into to try and make the fragmentation less frequent?

Thanks.

Answer 1

If the indexes are fragmenting that quickly due to massive amounts of activity affecting arbitrary user-provided values then they may be little you can do.

If the indexes that are fragmenting badly are over UNIQUEIDENTIFIER columns then these will fragment quickly when they see a lot of activity because the data is effectively random. If this is the case then you can minimize this greatly by using NEWSEQUENTIALID() instead of NEWID(), or the equivalent in your application if you are generating UUIDs there instead of in the database.

If you are generating UUIDs outside SQL Server make sure that they are generated in a way that is sequential in SQL Server's sort order (different generators use different byte orderings) otherwise you lose some or all of the benefit. See this for reference to .Net's UuidCreateSequential not producing UUIDs with the same ordering properties to SQL Server's NEWSEQUENTIALID() and how to juggle the result to work.

Answer 2

anything else I could look into to try and make the fragmentation less frequent?

Maybe you can find an appropriate FillFactor for your tables.

I recommend you to read this article about it: SQL Q & A Database Consistency, Temporary Tables, and More by Paul S. Randal

Q: We're implementing a nightly database maintenance plan that includes improving index performance. I've heard that setting the "fill factor" option for indexes can completely remove the need for maintaining indexes. Is this true? It seems that some indexes in our database suffer from fragmentation and some don't. Should we set a default fill factor for the database that will apply to all indexes and if so, what value should we use?

A: The fill factor setting can indeed be used to partially mitigate the need for index maintenance, but rarely can it be used to completely remove the need. In a nutshell, the fill factor setting instructs the Storage Engine to leave a certain percentage of free space in pages of clustered and nonclustered indexes when they are created or rebuilt. ( Note that the fill factor setting is not maintained during regular insert/update/delete operations.) A fill factor of 90, for instance, leaves 10% free space. Fill factors of 0 or 100 both leave no free space (this has been the source of much confusion). The idea is that space is left in the pages, which allows records on the page to expand or new records to be inserted on the page without causing an expensive, fragmentation-causing operation called a page split. You specify a percentage of free space so the pages can become more steadily full until the next index maintenance operation occurs, which resets the fill factor again. The trick is to choose a percentage that minimizes page splits between index maintenance operations. For an OLTP (online transaction processing) database, there's no easy answer except to choose a fill factor for each index based on trial and error. For data warehouses, where the indexes don't change, the fill factor should be 100% (meaning no free space is left on the pages). It is pretty uncommon that the default fill factor for a database is changed from the default of 100%, as the best fill factors for various indexes are usually different. The SQL Server 2008 Books Online topic "Fill Factor" has a lot more information on this. One other option is to change the index so that page splits do not occur. This might involve changing the index key so that inserts are not random (for instance, by not using a random GUID primary key) or disallowing operations that change the size of variable-length columns.

Answer 3

Index rebuild (Online) is a resource intensive operation. During the rebuild there are locks being held which for a very short period of time can make a table unavailable (Very short time).Below link provides guidelines for online index operations :

https://learn.microsoft.com/en-us/sql/relational-databases/indexes/guidelines-for-online-index-operations

A paragraph from the above link :

Because both the source and target structures are maintained during the online index operation, the resource usage for insert, update, and delete transactions is increased, potentially up to double. This could cause a decrease in performance and greater resource usage, especially CPU time, during the index operation. Online index operations are fully logged.

It would be recommended to have the script scheduled during minimal operations window . Identify the frequently high fragmented tables and treat them separately as per your environment convenience.

Also if possible always have a maintenance window defined for the database.

Answer 4

You might want to consider just bringing the statistics up to date for the indexes that have a high level of fragmentation.

Yes, the indexes are fragemented, but they are still indexes and they will still work, regardless of the level of fragmentation.

However, the statistics are possibly not up-to-date until you...

• update the statistics
• rebuild the index

Why? There is a built-in algorithm that determines when to update the statistics. Statistics are updated according to the following documentation:

The Query Optimizer determines when statistics might be out-of-date by counting the number of data modifications since the last statistics update and comparing the number of modifications to a threshold. The threshold is based on the number of rows in the table or indexed view.

References:
- Statistics (Microsoft Docs)
- SQL Server Statistics: Explained (Microsoft Developer Blogs)

The document then goes on to explain the algorithms for SQL Server 2014 and older:

Up to SQL Server 2014, SQL Server uses a threshold based on the percent of rows changed. This is regardless of the number of rows in the table. The threshold is:
- If the table cardinality was 500 or less at the time statistics were evaluated, update for every 500 modifications.
- If the table cardinality was above 500 at the time statistics were evaluated, update for every 500 + 20 percent of modifications.

If you would adapt that to your 2 Million row table, then the statistics would update roughly after 20% changes/rows * 2'000'000 rows = 200'000 changes on a SQL Server 2014 or older version.

It's slightly different for SQL 2016 and newer:

Starting with SQL Server 2016 and under the database compatibility level 130, SQL Server uses a decreasing, dynamic statistics update threshold that adjusts according to the number of rows in the table. This is calculated as the square root of 1,000 multiplied by the current table cardinality. With this change, statistics on large tables will be updated more often. However, if a database has a compatibility level below 130, then the SQL Server 2014 threshold applies.

References:
- Statistics (Microsoft Docs)
- Running SAP Applications on the Microsoft Platform

If you would adapt that to your 2 Million row table in your SQL Server Azure environment, then the statistics would update roughly after 2% of the data has changed. 2% changes/rows * 2'000'000 rows = 20'000 changes.

So you could be running into one of these situations:

Situation 1

1. not enough changes happening
2. statistics outdated
3. indexes fragmented
4. bad query plan

... bad performance, index fragmentation

Situation 2

1. lots of changes happening
2. statistics update regularly
3. indexes fragmented
4. good query plan

... periodically bad performance (statistics updating), index fragmentation resulting in slightly worse performance than when defragmented.

Situation 3

1. changes happening irrelevant
2. statistics updated or not
3. indexes fragmented
4. bad or good query plan

... varying performance, index fragmentation resulting in slightly worse performance than when defragmented.

Possible solutions

1. Update the statistics manually for the indexes on the very large tables to ensure you have up-to-date statistics and query plans that will select the correct index scan/seek options based on the cardinality of the data.

2. Create the indexes with a FILLFACTOR of 90, 80 or even lower depending on how often data gets inserted to reduce fragmentation of indexes caused by inserted/modified data.

3. Turn on the option AUTO_UPDATE_STATS_ASYNC = ON to decrease the probability of performance impacts due to statistics being updated during peak times of the day.

Considerations

As pointed out by others, index fragmentation "per se" shouldn't be an issue. However, depending on various factors it can be an indication of other issues.

When you rebuild the indexes you are refreshing the statistics and performance is back to normal.

References

• Statistics (Microsoft Docs)
• SQL Server Statistics: Explained (Microsoft Developer Blogs)
• Running SAP Applications on the Microsoft Platform
• Default auto statistics update threshold change for SQL Server 2016 (CSS SQL Server Engineers)
• Changes to auto update stats thresholds in SQL Server 2016 (Brenzozar.com)
• UPDATE STATISTICS (Transact-SQL) (Microsoft Docs)
• ALTER DATABASE (Azure SQL Database) (Microsoft Docs)
• STATS_DATE (Transact-SQL) (Microsoft Docs)