Solved

SQL Server Compression Estimate

Posted on 2013-12-18
4
262 Views
Last Modified: 2013-12-23
What I would like to do is be able to estimate what the size off all tables within a database after a compression is performed.  My objective is to utilize some script to determine whether it is an optimal time to actually perform the database compression.
0
Comment
Question by:Lenny Gray
4 Comments
 
LVL 19

Assisted Solution

by:jss1199
jss1199 earned 150 total points
ID: 39728175
For SQL 2008, I know of know script to do this, but Bob Taylor, a Microsoft PFE, put together a tool called SQL Server Compression Estimator.

For SQL 2012, you can use the sp_esitmate_data_compression stored procedure - http://technet.microsoft.com/en-us/library/cc280574.aspx

There is a 2008 sp that you can create, detailed at this Microsoft blog, but I prefer SSCE
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 total points
ID: 39728202
Use proc:

sp_estimate_data_compression_savings
0
 
LVL 10

Accepted Solution

by:
PadawanDBA earned 250 total points
ID: 39728299
I actually wrote a blog on how to estimate compression savings as a function of a balanced scorecard that helps you compare cost vs. benefit on compressing that index.  It can take a while to run (2.5 hours on my production 1.25 TB DB) and it certainly impacts performance.  http://geekswithblogs.net/padawandba/archive/2013/09/04/table-reallyhellipindex-compression-statistics-for-all-tables-in-database.aspx
0
 

Author Closing Comment

by:Lenny Gray
ID: 39736604
very hepful thank you
0

Featured Post

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS SQL Server connect issues 4 39
Want an individual results display div 8 43
SQL Server code help needed 14 31
Find special characters using tSQL 6 18
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question