SQL Server Compression Estimator

One of my favorite features in SQL Server 2008 is the ability to apply on-the-fly data compression to an active SQL Server database. Data compression offers a lot of benefits, such as a significant reduction in disk I/O consumption and lower memory utilization at a small cost in CPU to run the data compression algorithms. Because transactional workloads are most often constrained by I/O and memory but don’t usually put a heavy load on CPU, the trade-off from using data compression usually comes out strongly in your favor.

However, the native instrumentation to implement data compression is quite limited. There are a couple of T-SQL commands that you can use to enable and disable ROW and PAGE compression, as well as a system stored procedure, sp_estimate_data_compression_savings, which tells the administrator what kind of savings to expect for an individual database object. Because the native tools work on only one object at a time, determining whether data compression is a good option for a given database gets commensurately more difficult to analyze the more objects there are in the database. The SQL Server Compression Estimator (SSCE) is designed to make that scalability challenge easy.

Features

The creator, who prefers to go by boB Taylor, a Microsoft principal premier field engineer and Microsoft Certified Architect, built SSCE to make the implementation of data compression on large, multiobject databases less time consuming and labor intensive. SSCE has several useful features, including the following:

• SSCE makes it quick and easy to figure out the best compression for a database with many objects, estimating both ROW and PAGE compression on multiple objects in one pass. (This feature works as a batch process, so you might want to run it when you have plenty of time to process an extremely large database with thousands of objects.)
• SSCE reports on the index maintenance ratio (i.e., the ratio of updates to an index versus how often it’s used to satisfy queries) for indexes in the database so that you can better judge whether a frequently updated index might benefit from a less aggressive compression algorithm.
• SSCE creates the T-SQL statements needed to implement your chosen compression algorithm for each database object and/or partition.
• SSCE saves the analysis results to a .CSV file for later analysis.

SQL Server Compression Estimator

So by using SSCE in a database with a large number of objects, you can quickly and easily implement data compression. It’s quite simple to get a list of estimates from SSCE. To run SSCE on a local instance of SQL Server 2008 R2, do the following:

1. Point SSCE to the (local) SQL Server instance using Windows Authentication. You can specify a named instance and/or use mixed authentication. (Make sure the SQL Server Browser service is running if you want to access named instances.)
2. Define the database to process and the compression ratio you want to achieve or exceed. In my case, I specified the Singapore Shipping and Mercantile database and a 15 percent compression threshold, as Web Figure 1 (www.sqlmag.com, InstantDoc ID 139766) shows.
3. SSCE will then process a large number of tables and indexes, calculating the potential compression savings for each. It then returns a useful list of all the tables and indexes available for compression and the amount of compression they might offer.
4. You can either save the results to a comma-separated value (CSV) file or create a T-SQL script to apply compression to all the objects by selecting the check box in the Script This Option column.

System Requirements

SSCE works on SQL Server 2008 and later and requires both SQL Server Management Objects (SMO) and Microsoft .NET Framework on the client on which it runs. It’s also recommended that SQL Server Browser be installed and running so that SSCE can find named instances. You can download the SSCE toolkit and read the program details at ssce.codeplex.com, and you can see discussions about the product here.