Jaa

Now Available! A smaller, leaner database

  • Artikkeli

As promised, I have released my SQL Server Compression Estimator tool to https://ssce.codeplex.com/

One of the most exciting additions to SQL Server 2008 was the capability to apply data compression. The benefits are many including reduced disk I/O and memory utilization, with a small cost of CPU overhead.

In a database that may contain thousands of objects, determining which objects and / or partitions of those objects may benefit from data compression can be time consuming and labor intensive.

Not anymore! SQL Server Compression Estimator (SSCE) will allow you to estimate the best compression for your objects. It will estimate both ROW and PAGE compression for all objects, and will optionally report the Index Maintenance Ratio (a ratio that indicates how often an index is updated versus how often it is used to satisfy queries). You can then use that information to perhaps choose a less aggressive compression algorithm for heavily updated indexes.

For more information on the benefits of data compression please visit the SQL Customer Advisory Team site and view some in depth information regarding data compression

Data Compression: Strategy, Capacity Planning and Best Practices - https://sqlcat.com/top10lists/archive/2009/01/30/top-10-sql-server-2008-features-for-the-database-administrator-dba.aspx

Top 10 SQL Server 2008 Features for the Database Administrator (DBA) - https://sqlcat.com/top10lists/archive/2009/01/30/top-10-sql-server-2008-features-for-the-database-administrator-dba.aspx

PLEASE NOTE: This tool leverages the SQL Managment Objects to build the list of available SQL Servers. If you have named instances, ensure that a SQL Server Browser service is running.

If you have the SQL Server Browser service running, or you are executing this tool on a default instance you should see the following Authentication request

Once you have provided authentication information you will be presented with the main screen where you should

  1. Select the database to process
  2. Decide the percentage of compression you would like, in order for an option to be displayed in the resuits (i.e. if you select 75, only compression that would result in a 75% savings will be displayed)
  3. Decide if you want Index Maintenance Ratios to be calculated

 

When the processing is complete (and this could take awhile in a database with a large number of objects) you will be presented with the results

You then have two options for saving the results of the analysis

  1. Save Results will save the entire contents of the results window to a comma separated value (CSV) file
  2. Create Script will create a Transact-SQL script that contains the appropriate syntax to implment those items selected

Well that's it for now...

Until next time this is

boB 'The Tool Man' Taylor

Comments

  • Anonymous
    May 09, 2011
    Hi Bob,I am trying to download it from codeplex site but do not find the applications.
  • Anonymous
    May 09, 2011
    Hi BobI couldn't find any downloads in CodePlex site you have specifiedCan you please reupload it ? Thanks
  • Anonymous
    May 09, 2011
    I missed a checkbox on CodePlex which prevented the download from being visible. It is there now.
  • Anonymous
    May 09, 2011
    Thanks Bob, I am able to download now
  • Anonymous
    May 15, 2011
    Bob, This is great tool, I used it and it is extremely convenient.
  • Anonymous
    November 24, 2011
    Thanks a lot u save my lot of time