Sql-server – Rebuilding fragmented indexes on SQL Server

azure-sql-databaseindex-tuningsql server

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.

Best Answer

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.

Related Question