Hello all,
It’s been a year since we posted a blog. It’s been the busiest year of my life. However, we strongly believe that learning is a journey and it should be a continuing process. Coming to the point, recently I had to handle a customer problem with the overall application performance. Found some interesting problems and thought of sharing the same.
Clustered Index on UNIQUEIDENTIFIER column
Problem Statement:
Clustered Index on UNIQUEIDENTIFIER column takes you through high-level problems with the GUID. We have a crucial database environment where we were facing some performance issues. We identified a few issues fixed them. While analyzing the overall database architecture, we observed a few interesting facts which are highly impacting the database performance. One of the major observations is having a clustered index on UNIQUEIDENTIFIER column on Transactional tables where we are seeing a lot of writes on those tables. When we captured the statistics below are the observations:
1. Index Rebuild is happening on Sunday afternoon
2. Index fragmentation (Index on UNIQUEIDENTIFIER) is reaching 65% by Monday afternoon
3. We do not get any maintenance window during the week as the application is running in 24X7
4. We are using the same fragmented index during the week till Saturday
5. On that clustered index Bad Page Splits Per Second is high > 38% of Batch Requests Per Sec
6. These indexes are being highly used across the application for both WRITES and READS
An expert from the customer side asked us to brief our analysis and the generic information on why clustered index on UNIQUEIDENTIFIER is creating the problem and the possible solutions. Here is our analysis and the solution suggested.
