CODEWITHSUNDEEP
sql-server
luiscla27
luiscla27

Reputation: 6409

Creating a new index can cause a "page-split" or "high-fragmentation" in SQL Server?

I had an issue where a bunch of my tables where having fragmentation of about 70% to 90%, i've already analized the following ways to avoid table fragmentation:

  1. Choose a cluster key that complements the table’s insert pattern
  2. Do not insert records with random key values
  3. Do not update records to make them longer
  4. Do not update index key columns
  5. Be aware of features that can cause page splits
  6. Implement index fill factors

I found those "ways to avoid table fragmentation" here, here and here.

However by reading this, my guess is that SQL SERVER has to do "something" to make indexes "compatible" between each other when a new one is created.

The weird thing is that, the 70% - 90% fragmentation was already there before slowdown the issue!

So, the difference was that I added like 5 more new INDEXES, that's when the slowdown occurred. In order to fix it I just excuted a ALTER INDEX ALL ON TABLENAME REBUILD; for every table and voila! querys were super fast again (those indexes were already tested into a QA database, so my guess is that the problem was originated due to the second database statistics/fragmentation)

Upvotes: 0

Views: 281

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88971

ALTER INDEX ALL ON TABLENAME REBUILD

Not only reduces fragmentation on the indexes, it also updates the statistics for the indexes. It's much more likely that the updated statistics resolved the performance issue than the reduction in fragmentation did.

And if your database is small, or you have you have high Page Life Expectancy, or is on solid-state storage or an SAN with a large number of disks it's vanishingly unlikely that fragmentation created a significant performance problem.

If you have a single spinning disk dedicated to your database files, you can get sequential read rates of about 120MB/s, but random IO reads of less than 10MB/s. That disparity is the historical basis for most of the guidance about reducing fragmentation, and it's largely obsolete. For modern storage solutions (large SANs and Solid State storage devices) all IO is random IO. And on modern servers, larger memory sizes have massively reduced the amount physical read IO on the database files for most workloads.

Upvotes: 3

Related Questions