SQL Server Compression Estimator is a free tool available through CodePlex (http://ssce.codeplex.com/) which helps you in estimating the best compression for your SQL server objects. It estimates both Row and Page compression and also provides option to generate script for altering your SQL Server objects to implement compression.
My great thanks to MajikbyboB for providing this tool to the community for free.
Let us see how to install and use this tool
Download and extract the setup from the above site and extract the zip file and run setup.exe
Click “Next” to proceed
Select the installation folder and then click “Next” to proceed
Click “Next” to confirm the installation and proceed
Click “Close” to finish the installation
Now we have successfully installed the tool, Let us see how to run and estimate compression on databases
Run “Compression Estimator” and specify your Server name and select authentication method and provide required credentials and click “Connect”
Click on Database combo and select your database, and then specify the savings threshold.
Savings threshold identify what objects to be reports based on the saving in size due to compression
If you want to include index maintenance ratio, select the “Include index maintenance ratio” check box.
Click “Process” to proceed
Once the processing is complete, you can see the list of objects which can be compressed with the following details
1. Type of Compression (Page or Row)
2. Current Size
3. Compressed Size
4. $ of Savings
You can save the estimation results as CSV file by using “Save Results” option for your reference.
You can select the required changes to be made and then click on “Create Script” to generate the SQL Script to make compression changes
Sample Script generated
- — This compression script was created by Compression Estimator
- — By applying PAGE compression setting, you should obtain
- — approximately 53.33% compression
- ALTER TABLE [Production].[Product]
- REBUILD
- WITH (DATA_COMPRESSION = PAGE);
- — By applying PAGE compression setting, you should obtain
- — approximately 53.70% compression
- ALTER INDEX IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode ON [Person].[Address]
- REBUILD WITH (DATA_COMPRESSION = PAGE);
- — By applying PAGE compression setting, you should obtain
- — approximately 63.64% compression
- ALTER TABLE [Production].[BillOfMaterials]
- REBUILD
- WITH (DATA_COMPRESSION = PAGE);
- — By applying PAGE compression setting, you should obtain
- — approximately 59.09% compression
- ALTER TABLE [Purchasing].[PurchaseOrderHeader]
- REBUILD
- WITH (DATA_COMPRESSION = PAGE);
- — By applying PAGE compression setting, you should obtain
- — approximately 56.25% compression
- ALTER INDEX AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode ON [Sales].[CurrencyRate]
- REBUILD WITH (DATA_COMPRESSION = PAGE);
- — By applying PAGE compression setting, you should obtain
- — approximately 60.00% compression
- ALTER TABLE [Production].[ProductListPriceHistory]
- REBUILD
- WITH (DATA_COMPRESSION = PAGE);
- — By applying PAGE compression setting, you should obtain
- — approximately 60.00% compression
- ALTER INDEX IX_ProductReview_ProductID_Name ON [Production].[ProductReview]
- REBUILD WITH (DATA_COMPRESSION = PAGE);
- — By applying PAGE compression setting, you should obtain
- — approximately 60.00% compression
- ALTER TABLE [Production].[ProductCostHistory]
- REBUILD
- WITH (DATA_COMPRESSION = PAGE);
- — By applying PAGE compression setting, you should obtain
- — approximately 60.00% compression
- ALTER INDEX AK_Employee_LoginID ON [HumanResources].[Employee]
- REBUILD WITH (DATA_COMPRESSION = PAGE);
- — By applying ROW compression setting, you should obtain
- — approximately 60.00% compression
- ALTER INDEX IX_ProductReview_ProductID_Name ON [Production].[ProductReview]
- REBUILD WITH (DATA_COMPRESSION = ROW);
- — By applying PAGE compression setting, you should obtain
- — approximately 69.62% compression
- ALTER TABLE [Production].[WorkOrder]
- REBUILD
- WITH (DATA_COMPRESSION = PAGE);
- — By applying PAGE compression setting, you should obtain
- — approximately 67.25% compression
- ALTER TABLE [Production].[TransactionHistoryArchive]
- REBUILD
- WITH (DATA_COMPRESSION = PAGE);
- — By applying PAGE compression setting, you should obtain
- — approximately 57.58% compression
- ALTER TABLE [Purchasing].[PurchaseOrderDetail]
- REBUILD
- WITH (DATA_COMPRESSION = PAGE);
- — By applying PAGE compression setting, you should obtain
- — approximately 73.25% compression
- ALTER TABLE [Production].[WorkOrderRouting]
- REBUILD
- WITH (DATA_COMPRESSION = PAGE);
- — By applying PAGE compression setting, you should obtain
- — approximately 64.86% compression
- ALTER TABLE [Production].[TransactionHistory]
- REBUILD
- WITH (DATA_COMPRESSION = PAGE);
- — By applying PAGE compression setting, you should obtain
- — approximately 65.31% compression
- ALTER TABLE [Sales].[CurrencyRate]
- REBUILD
- WITH (DATA_COMPRESSION = PAGE);
I hope you all find this information useful. Please post your comments !!!
SQLXpertise 