Browse by Tags

Tagged Content List
  • Blog Post: Data Compression and Fill Factor

    Recently, I was asked this question "If I have a compressed index and I rebuild it with fill factor 70%, do I actually get any fill factor? Or does compression take away the empty space?". The answer to this question is 'yes'. In fact all index options listed below available with data compression (both...
  • Blog Post: 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 so you get some space savings. Second component...
  • Blog Post: 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 or NVARCHAR. use [AdventureWorksDW2008] ...
  • Blog Post: 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 exciting addition to the data compression offering...
  • Blog Post: 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 facing. Based on the feedback, we plan to improve...
  • Blog Post: 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 Server 2008 with customers. Many customers have...
  • Blog Post: 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 implications of DML operations on a heap. ROW...
  • Blog Post: 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 operation because · Creating a clustered...
  • Blog Post: 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 cases for compressed and uncompressed data. Let...
  • Blog Post: 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 the whole database’. I am not suggesting that you...
  • Blog Post: 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 page or a leaf-index page. The non-leaf pages of...
  • Blog Post: 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, the SQL Server eliminates two types of data redundancy...
  • Blog Post: 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 Technology Preview (CTP) November 2007. ...
  • Blog Post: 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, I have created a very simplistic and definitely...
  • Blog Post: 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 series of blogs vardecimal-storage-format for details...
  • Blog Post: 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. Given this, you may wonder why don’t we just create...
  • Blog Post: Data compression techniques and trade offs

    Ok, now that we have sort of agreed ( http://blogs.msdn.com/sqlserverstorageengine/archive/2007/09/30/data-compression-why-do-we-need-it.aspx ) that data compression is a good thing, you may wonder how SQL Server compresses the data, what does this compression mean to my data and to my workload? If...
  • Blog Post: Data Compression: Why Do we need it?

    As announced in Tech-Ed 2007, data compression is a new and exciting feature targeted to be available in SQL Server 2008. This is a huge topic to be covered in one BLOG post, I have decided to break it into a series of posts, each building on the previous ones. If you are interested discussions on any...
Page 1 of 1 (18 items)