We are podcast crazy this week on the Premier Field Engineering for Dynamics team. The PFE team will have a podcast a day going this week to get everyone jazzed up for Convergence next week. Our Dynamics AX focused podcast centers around using the compression capability available in Microsoft SQL Server 2008 Enterprise Edition. With compression you can both improve performance and reduce the size of your AX database. Michael DeVoe joined the podcast to talk about the different types of compression--when to and not to use compression, and some of the results he's found from working with our enterprise and mid-sized customers on the right compression strategy for their business.
Check out the archived podcast here: http://www.blogtalkradio.com/pfedynamics/2011/04/05/leveraging-sql-compression-with-microsoft-dynamics-products
For Dynamics AX customers, there is a new type of compression that's included in SQL 2008 R2 which does compression for Unicode data. SQL Server stores Unicode data as 2 bytes, and for most western languages you can save up to 50% of storage space. The chart below from the MSDN article on SQL Unicode Compression shows an overview of the potential savings.
When an index is created or rebuilt, or when a value is changed in a table that was compressed with row or page compression, the affected index or value is stored compressed only if its compressed size is less than its current size. This prevents rows in a table or index from increasing in size because of Unicode compression.
The storage space that compression saves depends on the characteristics of the data that is being compressed and the locale of the data. The following table lists the space savings that can be achieved for several locales:
For more information on the information presented in the podcast, you can check out these related links:
the topic was great, but does raise one question. You guys spoke about a compression Analyser Tool. Is this publicly available? If so where can I find it?