Archive

Posts Tagged ‘SQL Server Compression Estimator’

SQL Server Compression Estimator

June 10, 2011 Leave a comment

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

image

Click “Next” to proceed

image

Select the installation folder and then click “Next” to proceed

image

Click “Next” to confirm the installation and proceed

image

Click “Close” to finish the installation

image

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”

image

image

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

image

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

image

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

  1. — This compression script was created by Compression Estimator
  2.  
  3. — By applying PAGE compression setting, you should obtain
  4. — approximately 53.33% compression
  5. ALTER TABLE [Production].[Product]
  6. REBUILD
  7. WITH (DATA_COMPRESSION = PAGE);
  8.  
  9. — By applying PAGE compression setting, you should obtain
  10. — approximately 53.70% compression
  11. ALTER INDEX IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode ON [Person].[Address]
  12. REBUILD WITH (DATA_COMPRESSION = PAGE);
  13.  
  14. — By applying PAGE compression setting, you should obtain
  15. — approximately 63.64% compression
  16. ALTER TABLE [Production].[BillOfMaterials]
  17. REBUILD
  18. WITH (DATA_COMPRESSION = PAGE);
  19.  
  20. — By applying PAGE compression setting, you should obtain
  21. — approximately 59.09% compression
  22. ALTER TABLE [Purchasing].[PurchaseOrderHeader]
  23. REBUILD
  24. WITH (DATA_COMPRESSION = PAGE);
  25.  
  26. — By applying PAGE compression setting, you should obtain
  27. — approximately 56.25% compression
  28. ALTER INDEX AK_CurrencyRate_CurrencyRateDate_FromCurrencyCode_ToCurrencyCode ON [Sales].[CurrencyRate]
  29. REBUILD WITH (DATA_COMPRESSION = PAGE);
  30.  
  31. — By applying PAGE compression setting, you should obtain
  32. — approximately 60.00% compression
  33. ALTER TABLE [Production].[ProductListPriceHistory]
  34. REBUILD
  35. WITH (DATA_COMPRESSION = PAGE);
  36.  
  37. — By applying PAGE compression setting, you should obtain
  38. — approximately 60.00% compression
  39. ALTER INDEX IX_ProductReview_ProductID_Name ON [Production].[ProductReview]
  40. REBUILD WITH (DATA_COMPRESSION = PAGE);
  41.  
  42. — By applying PAGE compression setting, you should obtain
  43. — approximately 60.00% compression
  44. ALTER TABLE [Production].[ProductCostHistory]
  45. REBUILD
  46. WITH (DATA_COMPRESSION = PAGE);
  47.  
  48. — By applying PAGE compression setting, you should obtain
  49. — approximately 60.00% compression
  50. ALTER INDEX AK_Employee_LoginID ON [HumanResources].[Employee]
  51. REBUILD WITH (DATA_COMPRESSION = PAGE);
  52.  
  53. — By applying ROW compression setting, you should obtain
  54. — approximately 60.00% compression
  55. ALTER INDEX IX_ProductReview_ProductID_Name ON [Production].[ProductReview]
  56. REBUILD WITH (DATA_COMPRESSION = ROW);
  57.  
  58. — By applying PAGE compression setting, you should obtain
  59. — approximately 69.62% compression
  60. ALTER TABLE [Production].[WorkOrder]
  61. REBUILD
  62. WITH (DATA_COMPRESSION = PAGE);
  63.  
  64. — By applying PAGE compression setting, you should obtain
  65. — approximately 67.25% compression
  66. ALTER TABLE [Production].[TransactionHistoryArchive]
  67. REBUILD
  68. WITH (DATA_COMPRESSION = PAGE);
  69.  
  70. — By applying PAGE compression setting, you should obtain
  71. — approximately 57.58% compression
  72. ALTER TABLE [Purchasing].[PurchaseOrderDetail]
  73. REBUILD
  74. WITH (DATA_COMPRESSION = PAGE);
  75.  
  76. — By applying PAGE compression setting, you should obtain
  77. — approximately 73.25% compression
  78. ALTER TABLE [Production].[WorkOrderRouting]
  79. REBUILD
  80. WITH (DATA_COMPRESSION = PAGE);
  81.  
  82. — By applying PAGE compression setting, you should obtain
  83. — approximately 64.86% compression
  84. ALTER TABLE [Production].[TransactionHistory]
  85. REBUILD
  86. WITH (DATA_COMPRESSION = PAGE);
  87.  
  88. — By applying PAGE compression setting, you should obtain
  89. — approximately 65.31% compression
  90. ALTER TABLE [Sales].[CurrencyRate]
  91. REBUILD
  92. WITH (DATA_COMPRESSION = PAGE);

I hope you all find this information useful. Please post your comments !!!