Browse by Tags

• Index Fragmentation Series

• Changes to sp_estimate_data_compression_savings stored procedure in SQL2008R2

  When you compress an object (index or table or partition), there are two components of space savings that you get. First component is fragmentaton (i.e. the original object might have been fragmented). The object gets degragmented as part of doing compression Read More... Posted Wednesday, September 16, 2009 8:53 PM by Sunil Agarwal | 0 Comments Filed under: Index Fragmentation Series, Data Compression

• A Unicode Compression example

  Now that we have Unicode compression available in SQL Server 2008R2 as described in, let me take a simple example using AdventureWorksDW database to show you the additional compression that can be achieved on tables with one or more columns of type NCHAR Read More... Posted Monday, August 17, 2009 4:37 AM by Sunil Agarwal | 0 Comments Filed under: Data Compression

• Unicode Compression in SQL Server 2008R2

  In my previous blog , I had mentioned that unicode compression will be available as part of next SQL Server release named SQL 2008R2. You can down load the CTP2 version http://www.microsoft.com/sqlserver/2008/en/us/r2.aspx to play with it. This is a new Read More... Posted Monday, August 17, 2009 2:00 AM by Sunil Agarwal | 0 Comments Filed under: Data Compression

• Customer feedback on Data Compression

  As I described in my previous blog , the data compression feature has been very successful. We do appreciate all the feedback that we have received as this provides us a way to know how customers have been using the feature and the challenges they are Read More... Posted Monday, August 17, 2009 1:44 AM by Sunil Agarwal | 0 Comments Filed under: Data Compression

• Update on data compression performance/space-savings and links to published white papers

  It has been a while since I blogged about data compression so I thought it will be good to provide an update on data compression usage within SQL Server community. I am happy to say that the Data compression feature has been a tremendous success in SQL Read More... Posted Sunday, August 16, 2009 9:11 PM by Sunil Agarwal | 0 Comments Filed under: Data Compression

• DML operations on a HEAP and compression

  In my previous blog http://blogs.msdn.com/sqlserverstorageengine/archive/2008/12/20/enabling-compression-on-a-heap.aspx , I described how to enable compression on a HEAP. You can enable either ROW or PAGE compression on the heap. Let me now describe the Read More... Posted Sunday, December 21, 2008 9:16 PM by Sunil Agarwal | 0 Comments Filed under: Data Compression

• Enabling compression on a HEAP

  I often get a question how to do enable compression on a table that is a heap (i.e. it does not have a clustered index). Clearly, one could create the clustered index with compression option set to PAGE and then drop the clustered index. This is an expensive Read More... Posted Saturday, December 20, 2008 4:44 AM by Sunil Agarwal | 1 Comments Filed under: Data Compression

• Does data compression lead to more fragmentation?

  Lately I have been asked how data compression impacts fragmentation (i.e. does it cause more or less fragmentation?). I believe this question is best answered by looking at how does fragmentation occur in the first place and then analyze each of these Read More... Posted Wednesday, August 06, 2008 5:40 AM by Sunil Agarwal | 1 Comments Filed under: Data Compression

• Compression Strategies

  In the previous blogs, I had described the data compression in SQL Server 2008 and its implications on CPU and IO. One of the question that keeps coming up is ‘Should I compress my database? Or Why does the SQL Server not provide an option to compress Read More... Posted Sunday, January 27, 2008 4:20 AM by Sunil Agarwal | 2 Comments Filed under: Data Compression

• Details on page compression (page-dictionary)

  In the previous blog, I described column-prefix compression that is done as part of PAGE compression. The other component of PAGE compression is the page-dictionary. As the name indicates, this dictionary is specific to a page and the page can be data Read More... Posted Friday, January 18, 2008 9:02 PM by Sunil Agarwal | 1 Comments Filed under: Data Compression

• Details on PAGE compression (column-prefix)

  In the previous blog, I had mentioned that the PAGE compression is used to “minimize the data redundancy in columns in one or more rows on a given page”. You may wonder what exactly SQL Server does under the PAGE compression cover? With PAGE compression, Read More... Posted Friday, January 18, 2008 2:38 AM by Sunil Agarwal | 2 Comments Filed under: Data Compression

• Data Compression will be available in CTP-6

  Thanks to all who have contacted me for their interest in the data compression feature. Unfortunately, data compression feature is NOT available in the just released CTP-5 which is now available on the Microsoft Download Center as SQL Server 2008 Community Read More... Posted Tuesday, November 20, 2007 7:39 PM by Sunil Agarwal | 3 Comments Filed under: Data Compression

• Estimating the space savings with data compression

  Like I had indicated in my previous blog, it is recommended that you estimate compression savings on the object of interest before actually enabling compression as enabling compression is an expensive operation. To show how to estimate data compression, Read More... Posted Tuesday, November 13, 2007 12:54 AM by Sunil Agarwal | 4 Comments Filed under: Data Compression

• Types of data compression in SQL Server 2008

  SQL Server deploys two strategies to compress the data · First, it stores all fixed length data types in variable length format. If you recall, SQL Server 2005/SP2 provided a new variable length storage format decimal and numeric. Please refer to the Read More... Posted Monday, November 12, 2007 5:17 PM by Sunil Agarwal | 5 Comments Filed under: Data Compression

• Why not use compressed disk files or disk volumes?

  When we think of compressing the database, one of the first question that pops up is why not used the compressed volume? You may know that Windows OS has supported compression of individual files, folders and the entire NTFS volumes since Windows 2000. Read More... Posted Monday, November 12, 2007 4:19 AM by Sunil Agarwal | 4 Comments Filed under: Data Compression