Dynamics Community Forum Thread Details

Hello CU26121002-0,

You've identified a common and effective strategy for improving SQL Server performance: addressing disk I/O bottlenecks. Adding a new high-performance drive gives you a great opportunity to optimize. Let's break down the best approach and address your questions specifically in the context of Dynamics CRM On-Premise.

Recommended Best Practice: Move the Existing .mdf and .ldf Files to the New Drive

For a database primarily suffering from overall disk I/O limitations, the most straightforward and often most effective approach is to move the entire database (both the primary data file .mdf and the transaction log file .ldf) to the new, high-performance drive.

Here's why this is generally recommended:

Overall I/O Improvement: Moving both the data and the transaction log to a faster drive directly benefits all database operations, including reads and writes across all tables and indexes.

Simplicity: This approach is simpler to implement and manage compared to splitting data files.

Reduced Fragmentation: Moving the entire database can sometimes help in defragmenting the files on the new drive, leading to better sequential I/O.

Benefits Both Reads and Writes: Dynamics CRM workloads are a mix of reads and writes. A faster drive benefits both aspects. The transaction log, in particular, is write-intensive, and placing it on a high-performance drive can significantly improve write performance and reduce latency.

Addressing Your Questions:

What is the recommended best practice in this situation for SQL Server databases used by Dynamics CRM?
As mentioned above, the recommended best practice is to move both the primary data file (.mdf) and the transaction log file (.ldf) to the new, high-performance drive. This provides the most comprehensive improvement for overall database I/O.

Are there any risks or constraints (especially with CRM or future updates) if we move only certain tables to a secondary filegroup?
While technically possible to move specific tables to a secondary filegroup on the new drive, there are risks and constraints to consider, especially with Dynamics CRM:
- Complexity: Managing data across multiple filegroups adds complexity to database administration, backup/restore strategies, and maintenance tasks.
- Identifying "Heavy" Tables: Accurately identifying which tables are consistently the most I/O intensive can be challenging and might change over time with different usage patterns or CRM customizations. Moving the wrong tables might not yield the desired performance benefits.
- CRM Schema Awareness: Dynamics CRM's database schema is complex and tightly integrated. Moving tables to different filegroups could potentially impact the efficiency of certain CRM operations or queries if related data remains on the slower drive.
- Future Updates: While unlikely to break CRM functionality, future CRM updates and upgrades are designed and tested against the standard database schema. Introducing custom filegroup layouts might introduce unforeseen complexities during these processes. Microsoft's support might also have a more challenging time diagnosing issues in highly customized database layouts.
- Relationship Management: Maintaining the physical proximity of related tables on the same drive can sometimes improve join performance. Splitting related tables across drives could potentially negate some I/O benefits.
- Granular Control Challenges: Precisely controlling which data ends up on which filegroup for related entities can be difficult.
Therefore, while technically feasible, moving specific tables to secondary filegroups for a Dynamics CRM database is generally NOT the recommended best practice due to the added complexity, potential risks with CRM functionality and updates, and the difficulty in guaranteeing optimal placement of data.

Will splitting data across drives (using secondary data files) offer a measurable performance benefit compared to moving the whole database file?
Splitting data across drives can offer performance benefits in specific scenarios, primarily when:
- Specific Tables are Dominating I/O: If you have a few very large and heavily accessed tables that are clearly the bottleneck, placing them on a separate fast drive could improve performance for those specific operations. However, identifying and maintaining this accurately in a dynamic CRM environment is difficult.
- True Parallel I/O: SQL Server can perform parallel I/O operations across multiple data files on different physical drives. However, the degree of parallelism depends on the query execution plan and the underlying storage subsystem. Simply adding a secondary data file doesn't guarantee parallel I/O for all workloads.
Compared to moving the entire database (MDF and LDF) to a single high-performance drive, adding secondary data files for specific tables is less likely to provide a significantly better overall performance improvement for a typical Dynamics CRM workload and introduces more complexity and potential risks. The bottleneck you've identified is at the drive level. Addressing that directly by moving the entire database to the faster drive will likely yield the most immediate and widespread performance gains.

Steps to Move the Database Files:

Here's a general outline of the steps to move the database files. Perform these steps carefully and during a planned maintenance window. Always have a full backup of your CRM database before proceeding.

Take the CRM Organization Offline: Ensure all users are logged out and stop the Dynamics CRM services (Async Service, Sandbox Processing Service, IIS).

Detach the CRM Organization Database: Using SQL Server Management Studio (SSMS), detach the specific CRM organization database.

Move the Physical Files: Using Windows Explorer, move the .mdf and .ldf files for the detached database to the new high-performance drive.

Attach the CRM Organization Database: In SSMS, right-click on "Databases" and select "Attach." Browse to the new location of the .mdf file and attach the database. SQL Server should automatically find the .ldf file in the same location.

Verify File Locations: In SSMS, right-click on the attached database, go to "Properties," and then select the "Files" page. Verify that the "Physical Name" for both the data and log files now points to the new drive.

Bring the CRM Organization Online: Start the Dynamics CRM services and have users log back in to verify functionality.

Monitor Performance: After the move, closely monitor the disk I/O performance on the new drive and the overall responsiveness of your Dynamics CRM environment.

Important Considerations:

Transaction Log Location: It's often beneficial to place the transaction log (.ldf) on a physically separate high-performance drive from the data files (.mdf and .ndf if you were to use them) in very high-throughput OLTP systems. However, since you only have one new high-performance drive, placing both on it will still be a significant improvement over the previous slower drive.

Backup Strategy: Ensure your backup strategy is updated to reflect the new file locations.

Disk Space: Ensure the new drive has sufficient space for the current database size and future growth.

Permissions: Verify that the SQL Server service account has the necessary permissions to read and write to the new drive location.

Conclusion:

The most effective and least risky approach to improve performance in your situation is to move the entire Dynamics CRM database (both .mdf and .ldf files) to the new high-performance drive. Avoid the complexity and potential risks associated with splitting tables into secondary filegroups unless you have a very specific and well-understood bottleneck related to a small set of tables and a deep understanding of the CRM database schema. Focus on addressing the overall disk I/O bottleneck first by leveraging the speed of your new drive for the entire database.

If my answer was helpful, please click Like, and if it solved your problem, please mark it as verified to help other community members find more. If you have further questions, please feel free to contact me.

My response was crafted with AI assistance and tailored to provide detailed and actionable guidance for your Microsoft Dynamics 365 query.

Regards,

Daivat Vartak

Community site session details

Best Approach for Improving Performance by Using a New Drive in SQL Server (CRM On-Prem Database)

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Andrés Arias – Community Spotlight

Congratulations to the August Top 10 Community Leaders!

Leaderboard > Customer experience | Sales, Customer Insights, CRM

Featured topics

Product updates