Performance improvement for large table (Not to choose GUID as Cluster Index )

By default, GUID are set as primary key and typically set as Cluster Index in SQL for Microsoft Dynamic CRM (due to GUID as the primary key by default in MSCRM). This may not be a concern for small data tables.

However for large enterprise project like DMS (Dealer Management System), it will easily have large volume records for Vehicle Service History and other high volume master/ transaction related to Vehicle like  Vehicle Recall data and Warranty information etc.

Thus, it’s very important to apply the best practice on SQL for large volume data which can easily contain up to millions rows of record or more.

During enterprise project implementation, the usual bottleneck on performance will be on database layer for large data insert/retrieval / update once the Application / UI code layer is optimized at its best.

From our experience working with large volume data, you would see tremendous improvement when optimization is applied on database layer (optimizing the paging/fill factor, rebuilding / reorganizing index, index fragmentation health check etc).

For a start, I would like to share the benefit of on not using GUID as Cluster Index for large volume data. This will likely lower the index fragmentation and the fill factor, improving the performance in SQL database for read/ write. It would also save the storage space when paging is excessive due to ineffective cluster index with GUID.

The alternative for cluster index here would be date/time and Integer instead of GUID. Integer would be enough to handle records up to 2 billions.

GUID Have a good read on these links below by SQL Expert (Kimberly Tripp and others) on this topic:-

• http://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-the-clustering-key/
• http://www.sqlskills.com/blogs/paul/can-guid-cluster-keys-cause-non-clustered-index-fragmentation/