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

by Jan 16, 2016Event0 comments

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:-


  • Belgian Brand Kambukka Dials Up Productivity with Microsoft Dynamics 365 Business Central
  • Microsoft study: Small and medium-sized businesses growing fast by embracing digital technologies
  • Realizing-Result
  • Boost Sales and Improve Project Costing
  • Unlock Productivity and business insight
  • Move to the cloud faster, with confidence
  • Work Together, Work Faster
  • Break Into New Markets With Business Central for SMB
  • Dynamics 365 Business Central For SMB
  • Drive Transformative Business Result
  • Turbocharge your CRM with AI-Driven Dynamics 365 Copilot
  • How to Choose the Right CRM System for Your Business
  • Empower Everyone To Turn Data Into Insights Into Action
  • 2022
  • Microsoft Executive Circle Building Resilient Digital Workforce
  • Build High Productivity Apps With Microsoft Power Apps
  • Anyone Can Be a Developer With Microsoft Power Platform
  • Being as Close as Possible with Your Customers by Using Omnichannel
  • Win Your Customers with Dynamics 365 Marketing Capabilities
  • Gain More Deals Faster by Accelerate Your Sales with Dynamics 365 Sales
  • Technosoft Solutions Days 2022
  • 2021
  • Technosoft Customer Gathering 2021
  • Marketing Automation for Banking & Financial Industries
  • Delivering Personalized Experience with Dynamics 365 Customer Insights
  • Increase New Students Registration with Digital Platform
  • Meningkatkan Performa Digital Marketing Melalui Pengelolaan Data Pelanggan
  • Empowering your customer experience with an Omnichannel Strategy
  • Customer Data Platform For Marketing Strategies
  • 2020
  • Mobil88 Digital Journey With Technosoft Yana Dealer Management System (Yana DMS)
  • Technosoft in Microsoft Cloud Innovation Summit 2020
  • Automotive Retail Business Solution Day
  • Next Generation Customer Centric Automotive Retail Solutions
  • Webinar Dynamic 365 Business Central
  • Education System for Student Lifecycle
  • Automate Financial Management with Business Central
  • Stay Connected with Microsoft Teams
  • Webinar May 2020 : Customer Insights
  • Technosoft Webinar Microsoft Teams & Power Platform
  • Power BI Drives Your Business Better with Report Analysis
  • Stay Productive with Dynamics 365
  • Customer Insights Allow Businesses to Serve Customers Better
  • Business Central That Flexible To Adapt Your Business Needs
  • 2019
  • Keeping Track Data to Outpace the Competition in industries
  • Microsoft Power Platform – Create Your Own Application
  • Microsoft Power Apps (App in A Day)
  • Digital Marketing Asia
  • Dynamics 365 Business Central release wave 2 preview
  • Azure Updating For A New Season
  • Industry 4.0 Revolution in the Industrial world
  • Higher Education System The Answer For Digital Transformation
  • 2018
  • Properti Manajemen System Revolusi Digital Dalam Bisnis Properti.
  • Sistem CRM Sangat Penting untuk Dealer Anda
  • DMS Untuk Pengelolaan Pelanggan Yang Effisien
  • ERP Akselerasi Bisnis Dengan Solusi Menyeluruh
  • Transformasi Digital dalam dunia Otomotif
  • Customer Relationship Management Solusi Terbaik
  • Technosoft Consulting Becomes a Microsoft Authorized Education Partner
  • Technosoft Insider | June 2018
  • Technosoft Insider | April 2018
  • Technosoft Insider | March 2018
  • 2017
  • Technosoft Insider | November 2017
  • Technosoft Insider | October 2017
  • Technosoft Insider | September 2017
  • Technosoft Insider | August 2017
  • Technosoft Insider | July 2017
  • Technosoft Insider | June 2017
  • Technosoft Insider | May 2017
  • Technosoft Insider | April 2017
  • Technosoft Insider | March 2017
  • Technosoft Insider | February 2017
  • Technosoft Insider | January 2017
  • 2016
  • Technosoft Insider | December 2016
  • Technosoft Insider | November 2016
  • Technosoft Insider | October 2016
  • Technosoft Insider | September 2016
  • Technosoft Insider | August 2016
  • Technosoft Insider | July 2016
  • Technosoft Insider | June 2016
  • Technosoft Insider | May 2016
  • Technosoft Insider | April 2016
  • Technosoft Insider | March 2016
  • Technosoft Insider | February 2016
  • Shaping CRM future of Automotive, transforming customer experience
  • Performance improvement for large table (Not to choose GUID as Cluster Index)
  • What Is CRM? Breaking Five Auto Industry Myths
  • Microsoft Dynamics Asia Partner Summit 2014
  • Dealer Management System Redefine your Sales Productivity
  • Technosoft Insider | January 2016
  • 2015
  • Technosoft Insider | December 2015
  • Share This