Microsoft Dynamics Performance Issue and SQL Optimization Tips

Overcoming the Challenge with Large Data in Dynamics 365

Many organizations are understandably cautious when contemplating the implementation of Microsoft Dynamics at a large scale, particularly when faced with vast historical data. At my previous organization, we embarked on an ambitious journey by deploying Dynamics 365 on-premises to manage a large-scale service. This endeavor entailed the management of a staggering 20 terabytes of data and a colossal inventory of over 10 billion records, all neatly contained within a single entity.

The Primary Key Predicament

One of the foremost challenges we encountered with this immense dataset was rooted in the fundamental design of Dynamics 365. The backbone of the system lies in its consistent use of uniqueidentifiers as primary keys. While this uniqueness has its merits, it also serves as the epicenter of the main issue. It became apparent that dealing with such a database brings a unique set of constraints. Notably, you cannot harness partitioning techniques, as commonly employed in other databases, to optimize performance.

The Quest for Efficient Partitioning

In a typical scenario, when you're confronted with a substantial volume of records within an entity, the standard approach to improve performance in terms of reading and searching is to partition the table. Alas, this tried-and-true method does not align with the Dynamics 365 database structure. However, all is not lost. A solution emerges in the form of a powerful technique known as the Filtered-Index.

Unleashing the Power of Filtered-Index

Filtered-Index opens up the doors to effectively partitioning your data. It empowers you to segregate active records from deactivated ones, allowing for more efficient filtering within views. The brilliance of this approach lies in its ability to create an index that filters data based on specific criteria. Unlike traditional partitioning, Filtered-Index lets you manage your extensive dataset efficiently within the Dynamics 365 environment.

Moreover, you can expand the filtration capabilities by introducing additional criteria such as the year of record creation, providing even more fine-grained control over your data. However, it's essential to exercise caution when considering Filtered-Index on columns that experience frequent updates. For optimal performance, consider isolating the filegroup associated with this index from the main database and placing it on a separate high-speed disk.

Understanding the View Order Challenge

In Dynamics 365, all views of an entity come preordained with an order dictated by the primary key. The twist is that you cannot exclude this primary key from the equation. When you access a view of an entity, Dynamics arranges your records initially by the primary key and subsequently applies your specified order. Consequently, merely adding an index and ordering it by specific columns in isolation is not a feasible solution. The route to success requires the incorporation of the primary key into the index. Simultaneously, you must specify the columns and their order (ASC or DESC) to ensure swift loading of view records.

In Our Next Discussion: Dynamics 365 Publishing Challenges in a 24/7 Large-Scale Environment

In our upcoming discussion, we will shift our focus to another critical aspect of managing Dynamics 365 in a large-scale environment. Specifically, we will delve into the challenges and strategies related to Dynamics 365 publishing in a continuous 24/7 operational setting.

The efficient deployment and maintenance of a Dynamics 365 solution is a multifaceted endeavor, and ensuring the seamless publishing of updates, configurations, and customizations is a paramount concern. Operating in a large-scale environment with constant activity poses unique challenges and considerations. We will explore how to manage these intricacies effectively and keep your Dynamics 365 system running smoothly in a dynamic, around-the-clock setting.