A client called us with the following issue:
He had a table of a few dozen million rows. The table had a few GUID columns that had to be indexed.
He had an ETL process that inserted a few thousand rows every few minutes. When the table had only a clustered index, the insert took about 100 milliseconds. As he started adding indexes the duration gradually went up at about 50 millisecond per index.
At some point, there was a big spike, and from about 300 milliseconds, the duration jumped to about 3 seconds.
What was that tipping point?
At first, we thought it might be because of page splits that are generated because GUIDs are random. However, rebuilding the index with a lower fill factor only got things worse. So what was it?
GUIDs are good for uniqueness and for communicating with applications that work with those GUIDs for business/technical reasons.
However, they have two characteristics that affect performance:
GUIDS are wide
Since GUIDs are wide, they take up more space in memory, and there’s less space to store them and other data and index pages in memory. We need 4 times more pages to store GUID data as oppose to INT data
GUIDs are (mostly) random order
Every time we insert a row into a table, SQL Server has to update the relevant index page with that row. For that, he has to traverse the index and find that page. When we work with an identity column, for example, that page will always be at the end of the index.
When we work with a random GUID, however, that page can be anywhere in the index. And when we insert 3000 rows, we have to read many pages from many parts of the index.
Because of those factors, SQL Server had to read many pages, and at some point, not all pages could fit in memory and we had to go and read pages from disk. That was the tipping point, because, as always, when starting to read things from disk, things get slower.
Possible solutions
Each one can fit or not depending on your app and environments. Here are a few of them:
- Stop working with GUIDs and work with integer-based keys
- Work with GUIDs, but make them ever-increasing in the application or using SQL Server’s NewSequantialID function
- Throw money at the problem – more memory, faster disks, SQL Server Enterprise Edition
- Store the GUIDs in a key-value store like Azure Table, Redis or Riak, and store only integer keys as references in the database.
The post Indexes on GUIDs – Not Only Fragmentation appeared first on Madeira Data Solutions.
