We have upgraded 100 servers to SQL server 2008 R2 last quarter for our existing client because we evaluated that SQL 2008 R2 features can significantly improve the application performance of both SQL Server 2008 R2 and Microsoft Dynamics CRM 4.0 while resulting in space savings.
In addition, deploying these features does not require invasive infrastructure changes, and the features are easily enabled and disabled.
Microsoft SQL Server 2008 R2 contains a variety of features that, when implemented properly, can improve the performance of a Microsoft Dynamics CRM 4.0 implementation .
These Microsoft SQL Server 2008 R2 features include:
- Sparse Columns
- Backup Compression
- ROW COMPRESSION
ROW compression maps a fixed length data type to variable length physical storage to save space used to store the data; basically, it compresses columns in the row. For example, a CHAR(100) column stored in a variable length storage format only uses up the amount of storage defined by the data. With ROW compression enabled, storing “SQL Server 2008″ in the column requires storing only 15 (not the full 100) characters, representing a savings of 85%. Also, with ROW compression enabled, storing zero or null values requires no storage space. Microsoft SQL Server 2008 R2 supports ROW and PAGE compression for tables and indexes.
A superset of ROW compression, PAGE compression takes into account the redundant data in one or more rows on a page to save space used to
store the data. PAGE compression uses column prefixes and a page level dictionary technique for this task. In other words, with both page
compression techniques, the storage engine reduces the amount of data that is repeated in the page.
_Sparse columns _are ordinary columns that have an optimized storage for NULL values. Sparse columns reduce the space requirements for NULL values at the cost of greater CPU overhead to retrieve not NULL values. Sparse columns enable applications, such as Windows SharePoint Services, to efficiently store and access a large number of user-defined properties by using SQL Server 2008 R2.
INSERT, UPDATE, and DELETE statements can reference the sparse columns by name. Additionally, you can also view and work with all the sparse columns of a table that are combined into a single XML column. This column is called a column set.
Because a compressed backup is smaller than an uncompressed backup of the same data, compressing a backup typically reduces device I/O and therefore usually increases backup speed significantly.
IMPORTANT: Creating compressed backups is supported only IN SQL SERVER 2008 ENTERPRISE EDITION and later versions, but every edition of SQL Server 2008 and later versions can restore a compressed backup. Also, backup compression is not recommended for use with TDE. Based on your evaluation we come up along with some recommendation
IMPROVING PERFORMANCE BY USING SQL SERVER 2008 R2 COMPRESSION
Columns in Microsoft Dynamics CRM tables are typically sparsely populated, which our analysis of the customer database used in the testing efforts confirmed. Except for certain columns such as Account ID, Name, Phone Number and Email Address, the Account table was sparsely populated. Both row and page compression are very effective on Microsoft Dynamics CRM entity tables, which also contain a lot of binary columns and columns that specify pick list values.
When considering the use of SQL Server 2008 R2 Compression to improve the performance of a Microsoft Dynamics CRM 4.0 implementation, keep in mind the following recommendations:
v Identify the largest tables in the Microsoft Dynamics CRM database and consult SQL Server best practices to pick the best candidates for compression.
v Note: Microsoft Dynamics CRM tables (such as metadata tables) that are frequently accessed or continuously updated may not be good candidates for compression. An example is the PrincipleObjectAccess table which manages the privileges for the Microsoft Dynamics CRM system. In these cases, the performance impact of managing these compressed tables may outweigh the space savings from compression.
v Estimate savings for each table by using the following stored procedure
v Enable Page Compression on tables with mostly static data.
v Enable Row Compression on entity tables.
v Verify performance impact.
IMPORTANT: Compressing every table in the database may put a lot of load on the computer running SQL Server and compression should be limited to large tables as per SQL Server best practices.
IMPROVING PERFORMANCE BY USING SQL SERVER 2008 R2 SPARSE COLUMNS as mentioned previously, columns in Microsoft Dynamics CRM tables typically are sparsely populated. The sparse columns feature in SQL 2008 is perfectly suited for Microsoft Dynamics CRM tables because it reduces the space required to store data in user-specified columns. In addition, by setting the columns as sparse, Microsoft Dynamics CRM administrators can optimize access to frequently accessed tables in which certain columns are rarely accessed and include all or mostly NULL values.
Designating a column as sparse is only useful when the column contains mostly NULL values; consider using sparse columns when the space saved is at least 20 percent to 40 percent to strike a balance between space savings and additional CPU overhead. As you work to improve Microsoft Dynamics CRM 4.0 performance by using SQL Server 2008 R2 Sparse Columns, keep in mind the following
v Identify large tables in the Microsoft Dynamics CRM database.
v Consult SQL Server best practices documented in the article mentioned previously to select the columns to be marked as sparse; typically, use sparse storage for columns in which most values are NULL.
v Note: Marking non-sparse columns as sparse will significantly increase the amount of space needed to store the data.
v Mark the columns as sparse and rebuild the indexes on the table.
v Verify performance impact.
PERFORMING AND MAINTAINING BACKUPS MORE EFFICIENTLY BY USING SQL SERVER 2008 R2 BACKUP COMPRESSION
As expected, results confirmed that enabling Backup Compression yields significant savings in the time required to perform backups and in the space required for storing those backups.
BACKUP TIME (MIN)
BACKUP SIZE (KB)
NOTE: The size of the database used in this test was 15.5 gigabytes.
Enable backup compression to increase the efficiency of performing and maintaining backups. However, keep in mind that compression can significantly increase CPU usage and that the additional overhead might adversely impact concurrent operations. As a result, when using backup compression, be sure to verify that the overall system performs at desired levels. Additionally, consider creating low-priority compressed backups in a session whose CPU usage is limited by Resource Governor.