SQL Server Sizing for Microsoft Dynamics CRM 2011
SQL Server sizing is critical for good CRM Performance. Maybe you are standing up a new environment and wanting to ensure that your SQL environment will handle your estimated load of data and users, or maybe you have an existing CRM deployment and you want to enhance it to improve performance.
The following are rules of thumb to follow when sizing your CRM database server. These are general recommendations—you will want to take into account your expected user count and transaction levels, but the following principles should be helpful guidelines to follow when estimating your sizing requirements.
1. More RAM is usually better, but not always. Get as much as you can justify—if possible, go to 80% of what the SQL server will support. Some users have reported performance degradation if max ram is used.
2. Disk I/O is more important than RAM. In today’s speedy servers, the most common bottleneck is the hard drive speed, rather than memory. This is especially relevant if virtual servers are used for database—don’t use virtual partitions on a shared physical disk. Go with the fastest possible disks you can justify in your budget. 17,000 RPM spindles or Solid State Drives (SSD) will make significant performance improvements.
3. Buy many lower capacity disks instead of one high capacity disk and split out data, transaction logs, and temp db onto their own physical partitions. You may even want to consider moving busy tables to their own disk.
4. For an existing server, optimize your SQL performance first, then add RAM. If you have tables that are not optimized, have missing indexes, or have indexes that are unnecessary, your CRM performance will be slow. In this case, optimizing and tuning your database will give you better performance improvements than adding additional RAM.
5. Remember that some optimizations, such as indexes, will require additional RAM, so if you are doing heavy optimization, see # 1.
6. Make sure you have a good maintenance plan and are regularly defragmenting your indexes.
7. For most implementations of CRM 2011, 12 GB should be the minimum RAM on the SQL Server. More is better.
8. Be sure to set the MaxServerMemory setting in SQL to reserve 20% of system memory for the OS and other applications. SQL Server likes to use as much memory as it can, and it sometimes doesn’t like to release that memory. That’s why setting the maximum server memory available to SQL to a number lower than the total system memory is a good idea—it leaves enough memory available for the operating system.
9. Your data partition should be approximately 2.5 times as big as the estimated size of your CRM database. This will give you room to grow, and also give you leeway should you ever want to set up a second copy of your environment for test purposes or to use as a replicated reporting environment.
10. Monitor performance regularly—don’t set it and forget it. Data volumes and usage patterns with change over time. If you have tools like SCOM, you can automate monitoring of standard OS and SQL performance metrics, and about every 1-3 months consider running standard sql queries and reports to check ongoing performance. The Standard reports available in SQL Management Studio are a good starting point, such as the memory consumption at the server level and Index Usage Statistics at the database level
11. Follow the recommendations in the Optimizing and Maintaining Performance of a Microsoft Dynamics CRM 2011 Server Architecture White Paper.
It has several good recommendations for SQL Server performance optimization, such as setting MaxDegreesofParallelism to 1.
The following are rules of thumb to follow when sizing your CRM database server. These are general recommendations—you will want to take into account your expected user count and transaction levels, but the following principles should be helpful guidelines to follow when estimating your sizing requirements.
1. More RAM is usually better, but not always. Get as much as you can justify—if possible, go to 80% of what the SQL server will support. Some users have reported performance degradation if max ram is used.
2. Disk I/O is more important than RAM. In today’s speedy servers, the most common bottleneck is the hard drive speed, rather than memory. This is especially relevant if virtual servers are used for database—don’t use virtual partitions on a shared physical disk. Go with the fastest possible disks you can justify in your budget. 17,000 RPM spindles or Solid State Drives (SSD) will make significant performance improvements.
3. Buy many lower capacity disks instead of one high capacity disk and split out data, transaction logs, and temp db onto their own physical partitions. You may even want to consider moving busy tables to their own disk.
4. For an existing server, optimize your SQL performance first, then add RAM. If you have tables that are not optimized, have missing indexes, or have indexes that are unnecessary, your CRM performance will be slow. In this case, optimizing and tuning your database will give you better performance improvements than adding additional RAM.
5. Remember that some optimizations, such as indexes, will require additional RAM, so if you are doing heavy optimization, see # 1.
6. Make sure you have a good maintenance plan and are regularly defragmenting your indexes.
7. For most implementations of CRM 2011, 12 GB should be the minimum RAM on the SQL Server. More is better.
8. Be sure to set the MaxServerMemory setting in SQL to reserve 20% of system memory for the OS and other applications. SQL Server likes to use as much memory as it can, and it sometimes doesn’t like to release that memory. That’s why setting the maximum server memory available to SQL to a number lower than the total system memory is a good idea—it leaves enough memory available for the operating system.
9. Your data partition should be approximately 2.5 times as big as the estimated size of your CRM database. This will give you room to grow, and also give you leeway should you ever want to set up a second copy of your environment for test purposes or to use as a replicated reporting environment.
10. Monitor performance regularly—don’t set it and forget it. Data volumes and usage patterns with change over time. If you have tools like SCOM, you can automate monitoring of standard OS and SQL performance metrics, and about every 1-3 months consider running standard sql queries and reports to check ongoing performance. The Standard reports available in SQL Management Studio are a good starting point, such as the memory consumption at the server level and Index Usage Statistics at the database level
11. Follow the recommendations in the Optimizing and Maintaining Performance of a Microsoft Dynamics CRM 2011 Server Architecture White Paper.
It has several good recommendations for SQL Server performance optimization, such as setting MaxDegreesofParallelism to 1.
No comments:
Post a Comment