Search results matching tag 'Compression'

Why does SQL Server not compress data on LOB pages?

Hugo Kornelis — Thu, 31 Jan 2013 09:08:03 GMT

Enabling compression on your database can save you a lot of space – but when you have a lot of varchar(max) or nvarchar(max) data, you may find the savings to be limited. This is because only data stored on the data and index pages is compressed, and data for the (max) data types is generally stored on other, special-purpose pages – either text/image pages, or row overflow data pages. (See Understanding Pages and Extents in Books Online). This is from the SQL Server 2008R2 Books Online, but it is still valid in SQL Server 2012 – but apparently, this page has been removed from newer Books Online editions).

So why does SQL Server not compress the data that, perhaps, would benefit most from compression? Here’s the answer.

SQL Server currently supports two compression methods for data in the database (backup compression is out of scope for this post).

* Row compression: This is a simple algorithm to save storage space for individual rows. It has two elements. The first is a more efficient way to store the per-row metadata, saving a few bytes per row regardless of layout and content. The second element is storing almost all data types, even those that have a fixed length, as variable length. This mainly has benefits for the larger numerical types (e.g a bigint with a value of 1,000 is stored in two bytes instead of eight – only values that actually need all eight bytes do not gain from this, and will instead take up more space because the actual length has to be stored somewhere) and for fixed-length string types with lots of trailing spaces. For Unicode data, the SCSU algorithm is used, which saves 15% to 50% depending on the actual content of the column. (According to Wikipedia, the SCSU standard has gained very little adoption because it is not as effective as other compression schemes).

See Row Compression Implementation and Unicode Compression Implementation in Books Online.

* Page compression: When enabled, page compression is done *after* row compression. As the name implies, it's done on a per-page basis. It consists of two steps:

1. Prefix compression. Within each column, the longest common prefix is used to build the "anchor record". All columns than only indicate how many characters of the anchor value they use as prefix. So for example, if we have a first name column with the values Roger / Hugo / Hugh, the anchor value could be Hugh, and the data values would be stored as {0}Roger / {3}o / {4}. (Here, {3} is stored as a single byte, and {3}o means: first three characters of Hugh, followed by an o).

2. Dictionary compression. Accross the entire page, columns that are now stored with the same bit pattern are replaced with a single value that points to the dictionary entry. Let's assume that the same page I use above also has a Lastname column, with values Plowman / Kornelis / Ploo. Here, Plowman would be the anchor value, and the data after prefix compression would be {7} / {0}Kornelis / {3}o. The dictionary encoding would then see that there is a {3}o in the population of the Firstname columnm and a {3}o in the population of the Lastname column. It would place {3}o as the first entry in the dictionary and replace both {3}o values with the reference [1].

See Page Compression Implementation in Books Online.

All elements of page compression save space by eliminating repeated data between different column values, so they will only work when multiple values are stored on a page. For all LOB pages, the reverse is the case: a single value spans multiple pages. So by definition, page compression can never yield any benefits.

For row compression, the more efficient storage of per-row metadata naturally only affects pages that have per-row metadata stored – data and index pages, but not LOB pages. And the conversion of fixed length to variable length data types also doesn’t affect LOB pages, since these can only be used for varying length data.

Based on the above, it is obvious why SQL Server does not compress varchar and varbinary data stored on LOB pages – there would be zero benefit from any of the implemented compression methods. But how about Unicode compression for nvarchar(max) and overflowing nvarchar(nnn) data? Wouldn’t that save some space?

To answer that, I now have to go into speculation mode. And I see two possible theories:

1. Because the SCSU standard saves less spacing than other algorithms, the SQL Server team deliberately made this choice in order to encourage people to compress these large values in the client before sending them to the server, thereby reducing not only storage space (by more than SCSU would have yielded), but also network traffic. The down side of this is that cool features such as Full-Text Search and Semantic Search don’t understand data that was compressed at the client – at least not without a lot of extra effort.

2. Since all compression algorithms work on a per-page basis, they had a choice between either first breaking the LOB data into pages and then compressing (which makes no sense, as the rest of the page would remain empty and the amount of space actually used remains the same) or creating a separate algorithm for LOB data to first compress it and then split it over multiple pages. That would of course have cost a lot of extra engineering hours, and if my understanding of SCSU is correct, it would also have a big adverse side effect on operations that affect only a part of an nvarchar(max) value (like SUBSTRING or the .WRITE method of the UPDATE statement). That is because SCSU works by traversing the entire string from left to right and can’t handle operating on only a subset of the string.

Bottom line: When you have to store large values and you want to save on storage size, your best course of action is probably to compress and decompress the values on the client side. But do beware the consequences this has for Full Text Search and Semantic Search!

Final note: I didn’t spend as much time on this blog post as I normally do. That’s because this actually started as a reply to a question on an internet forum, but when I was busy I realized that the reply was long enough to be promoted to a blog post.

Did You Know? I gave two presentations last week

Kalen Delaney — Mon, 14 Jun 2010 15:05:00 GMT

Even though I didn't make it to TechEd this year, it didn't mean I was quiet last week. On Wednesday, I was in Colorado, giving a talk for the new Colorado PASS User Group, which is a joint venture between 3 different existing groups from Colorado Springs, Denver and Boulder. On Saturday, I spoke at SQL Saturday #43, in Redmond on the Microsoft campus. My presence there has already been mentioned on two other blogs here at SQLBlog: Merrill Aldrich and the infamous Buck Woody.

As Merrill mentioned, my session was on compression ("The Compression Session", isn't that catchy?). Right before lunch, I ran into an old friend, who asked what exactly my session would cover. I explained I would talk about the compression algorithms, and how exactly the data in your data files was compressed. He said… "Oh, you're going to talk about the Internals of Compression"… like he was surprised. I guess I'll have to send him a copy of my latest book… SQL Server 2008 Internals. I actually didn't cover the deepest internals that I could have; I did have some extra slides showing the details of the new row formats used if you choose to compress your data, but we didn't have any extra time. The details are in my book, if you're interested.

As mentioned, I discussed primarily the compression algorithms. I think my friend was asking what my specific topic was because he wanted to know if I was going to discuss performance and best practices at all. He then shared a tidbit that he had just heard from inside Microsoft (he used to work for Microsoft, for many long years) and that is that the consulting teams have pretty much discovered that the benefit of compression almost always outweighs the cost, and for most large systems they are recommending turning on page compression right out of the box. Remember that compression is a Enterprise Edition feature (and it's mainly the larger systems that are using Enterprise Edition). I'm just passing on this comment, I haven't done enough testing of my own to make a personal recommendation.

The demos have been posted for both the Denver PASS and the SQL Saturday sessions at:
www.sqlserverinternals.com/conferences

~Kalen

Did You Know: My Next Public Event at SQL Saturday in REDMOND

Kalen Delaney — Mon, 21 Sep 2009 02:59:00 GMT

My Boston class is SOLD OUT, but those of you in the Beautiful Pacific Northwest have an opportunity to come hear me at SQL Saturday in Redmond WA on Oct 3. I'll be speaking about compression in SQL Server 2008.

Check out the schedule of 3 concurrent tracks of great sessions, and it's all free!

http://www.sqlsaturday.com/schedule.aspx

I hope to see many of you there!

~Kalen

Should I Compress My Indexes? New Compression Whitepaper!

Lara Rubbelke — Sun, 31 May 2009 15:27:00 GMT

A very well written study of the compression is now available: Data Compression - Strategy, Capacity Planning and Best Practices. This study provides guidance on best practices for designing, implementing and managing compression in your environment. I found this whitepaper to be full of useful guidance and scripts to monitor and manage compressed tables.

The paper starts with tips and tools to help decide what to compress. It then guides the reader through considerations for implementing compression and estimating necessary resources to complete compression. The final sections of the paper provide guidance and evidence on management topics – space management, performance management, partition management.

I am certain that those who are considering compression will find these concepts, scripts and tools to support the full lifecycle of compression. I know that I will keep this on my short list of favorite whitepapers!

A Quick Look At Compression In SQL 2008

Denis Gobo — Tue, 07 Apr 2009 19:32:00 GMT

To use compression you have to understand what your data looks like, if your data is mostly unique then compression might not really help a lot. If your data is repeated on a page then it could be beneficial. In my own test compression would improve performance with some tables and not so much with other tables. I won't explain how compression works, this is all explained in books on line. the goal of this post is for you to have some code that you can run and then see how page and row level compression differs compared to the original data.

Here is some code that you can run to see how compression works. the data I have used is similar to what we use at my job(but not the same, i used fake tickers so don't try to invest in those ;-0). I have used just the 10 industry groups from the Industry Classification Benchmark, I left out supersectors,sectors and subsectors otherwise we would have a lot more rows per table. I also did not create fake ISIN Code,SEDOL and CUSIP columns since that would have bloated the code in this post. The code will generate 2780650 rows per table, this is a nice size to test with.
Compression is really nice when you get charged $30 per GB for the SAN, so besides performance there is also a cost benefit in storage

Here is the code
This block of code will create the database, setup the lookup tables and create the tables for our tests

use master

create database CompressionTest2

use CompressionTest2

--our Industry Classification Benchmark table

Create table IndustryGroup(GroupCode char(4) not null primary key,

GroupDescription varchar(40))

--just industry groups

insert IndustryGroup values('0001','Oil & Gas' )

insert IndustryGroup values('1000','Basic Materials' )

insert IndustryGroup values('2000','Industrials')

insert IndustryGroup values('3000','Consumer Goods')

insert IndustryGroup values('4000','Healthcare')

insert IndustryGroup values('5000','Consumer Services')

insert IndustryGroup values('6000','Telecommunications')

insert IndustryGroup values('7000','Utilities')

insert IndustryGroup values('8000','Financials')

insert IndustryGroup values('9000','Technology')

--currency table

Create table Currency (CurrencyCode char(3) not null primary key, CurrencyDescription varchar(30))

--just handful of currencies

insert currency values('USD','U.S. Dollar')

insert currency values('AUD','Australian Dollar')

insert currency values('CAD','Canadian Dollar')

insert currency values('JPY','Japanese Yen')

insert currency values('MXN','Mexican Peso')

insert currency values('GBP','U.K. Sterling')

insert currency values('EUR','European Euro')

insert currency values('ISK','Iceland Krona')

insert currency values('BGN','Bulgarian Lev')

insert currency values('RON','Romanian Leu')

insert currency values('INR','Indian Rupee')

insert currency values('RUB','Russia Rubles')

insert currency values('BHD','Bahrain Dinar')

insert currency values('EGP','Egypt Pounds')

insert currency values('JOD','Jodan Dinars')

insert currency values('KWD','Kuwait Dinars')

insert currency values('MAD','Morocco Dirham')

insert currency values('OMR','Omam Rial')

insert currency values('QAR','Qatari Rial')

--market cap table

create table MarketCap (MarketCap varchar(20) not null primary key,MarketCapDescription varchar(100) )

--left out a bunch of market caps

insert MarketCap values('ALL','broad')

insert MarketCap values('MID','mid cap')

insert MarketCap values('MCR','micro cap')

insert MarketCap values('SML','small cap')

insert MarketCap values('LRG','large cap')

--calendar table

create table Calendar (CalendarDate date not null primary key)

insert Calendar

select dateadd(d,number,'19920101') from master..spt_values

where type = 'p'

and datepart(dw,dateadd(d,number,'20080101')) not in (1,7)

union

select dateadd(d,number,'19970810') from master..spt_values

where type = 'p'

and datepart(dw,dateadd(d,number,'19970810')) not in (1,7)

--the table that we will test against

create table IndexCloseValues (CalendarDate date not null,

IndexSymbol varchar(30) not null,

GroupCode char(4) not null,

CurrencyCode char(3) not null,

MarketCap varchar(20) not null,

CloseValue decimal(30,10),

TotalReturnClose decimal(30,10))

--2780650 rows

insert IndexCloseValues

select CalendarDate,

CurrencyCode + left(GroupCode,1) + MarketCap as Symbol, --fake tickers

GroupCode,CurrencyCode,MarketCap,

ROW_NUMBER() OVER( ORDER BY CalendarDate,GroupCode,CurrencyCode,MarketCap ) *0.00100,

ROW_NUMBER() OVER( ORDER BY CalendarDate,GroupCode,CurrencyCode,MarketCap ) *0.247001

from IndustryGroup i cross join currency c

cross join MarketCap

cross join calendar

--create a copy for page compression

select * into IndexCloseValuesPage

from IndexCloseValues

--create a copy for row compression

select * into IndexCloseValuesRow

from IndexCloseValues

--add unique constraint

ALTER TABLE IndexCloseValues WITH NOCHECK

ADD CONSTRAINT UQ_IndexCloseValues UNIQUE Clustered (CalendarDate,IndexSymbol)

--add unique constraint

ALTER TABLE IndexCloseValuesPage WITH NOCHECK

ADD CONSTRAINT UQ_IndexCloseValuesPage UNIQUE Clustered (CalendarDate,IndexSymbol)

--page compression

ALTER TABLE IndexCloseValuesPage

REBUILD WITH (DATA_COMPRESSION = PAGE);

--add unique constraint

ALTER TABLE IndexCloseValuesRow WITH NOCHECK

ADD CONSTRAINT UQ_IndexCloseValuesRow UNIQUE Clustered (CalendarDate,IndexSymbol)

--row compression

ALTER TABLE IndexCloseValuesRow

REBUILD WITH (DATA_COMPRESSION = ROW);

Now that everything is setup we can look how big the tables are. Run the code below (old school I know)

dbcc showcontig('IndexCloseValues')

dbcc showcontig('IndexCloseValuesPage')

dbcc showcontig('IndexCloseValuesRow')

Table: 'IndexCloseValues' (213575799); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned................................: 23767
- Extents Scanned..............................: 2972
- Extent Switches..............................: 2971
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.97% [2971:2972]
- Logical Scan Fragmentation ..................: 0.01%
- Extent Scan Fragmentation ...................: 0.17%
- Avg. Bytes Free per Page.....................: 23.3
- Avg. Page Density (full).....................: 99.71%

Table: 'IndexCloseValuesPage' (245575913); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned................................: 9307
- Extents Scanned..............................: 1165
- Extent Switches..............................: 1164
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.91% [1164:1165]
- Logical Scan Fragmentation ..................: 0.04%
- Extent Scan Fragmentation ...................: 0.77%
- Avg. Bytes Free per Page.....................: 10.7
- Avg. Page Density (full).....................: 99.87%

Table: 'IndexCloseValuesRow' (261575970); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned................................: 13255
- Extents Scanned..............................: 1657
- Extent Switches..............................: 1656
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1657:1657]
- Logical Scan Fragmentation ..................: 0.01%
- Extent Scan Fragmentation ...................: 0.36%
- Avg. Bytes Free per Page.....................: 11.4
- Avg. Page Density (full).....................: 99.86%

As you can see compression really reduced the pages for the table, page level compression looks really good here.

First let's look at the execution plans, running the following block of code....

select * from IndexCloseValues

where CalendarDate = '19920101'

select * from IndexCloseValuesPage

where CalendarDate = '19920101'

select * from IndexCloseValuesRow

where CalendarDate = '19920101'

...will result in this plan

Increasing the query to return a month of data instead of a day

select * from IndexCloseValues

where CalendarDate between '19920101' and '19920121'

select * from IndexCloseValuesPage

where CalendarDate between '19920101' and '19920121'

select * from IndexCloseValuesRow

where CalendarDate between '19920101' and '19920121'

will result in this plan.

You can draw your own conclusions from those images

Let's look at some reads, first turn statistics io on

SET STATISTICS IO ON

select * from IndexCloseValues

where CalendarDate = '19920101'

select * from IndexCloseValuesPage

where CalendarDate = '19920101'

select * from IndexCloseValuesRow

where CalendarDate = '19920101'

950 row(s) affected)

Table 'IndexCloseValues'. Scan count 1, logical reads 12, physical reads 0......

(950 row(s) affected)

Table 'IndexCloseValuesPage'. Scan count 1, logical reads 7, physical reads 0......

(950 row(s) affected)

Table 'IndexCloseValuesRow'. Scan count 1, logical reads 8, physical reads 0......

Those are some nice numbers for the reads, now we will increase the date range to one month

select * from IndexCloseValues

where CalendarDate between '19920101' and '19920121'

select * from IndexCloseValuesPage

where CalendarDate between '19920101' and '19920121'

select * from IndexCloseValuesRow

where CalendarDate between '19920101' and '19920121'

(14250 row(s) affected)

Table 'IndexCloseValues'. Scan count 1, logical reads 125, physical reads 0......

(14250 row(s) affected)

Table 'IndexCloseValuesPage'. Scan count 1, logical reads 52, physical reads 0......

(14250 row(s) affected)

Table 'IndexCloseValuesRow'. Scan count 1, logical reads 69, physical reads 0......

When selecting more data the numbers look even better.

Turn statistics io off again

SET STATISTICS IO OFF

So as you can see compression reduces the reads by over half when using page compression.

Geek City: Two Compression Questions and Parameter Metadata

Kalen Delaney — Tue, 10 Mar 2009 17:46:00 GMT

I am teaching the SQL 2008 version of my course for the first time this week. My life is still is disarray and spare time is practically non-existent. During the evenings after I class I am trying to get caught up on several projects that I fell way behind on over the last month when most of my time was spent in the hospital. Blogging has been put on the back burner, but I thought I could share some of the questions that came up in class on the first day. I usually try to track down answers to open questions, and then share them with the students the next day.

1. Why can't the stored procedure sp_estimate_data_compression_savings be available on SQL Server 2008 Standard Edition, instead of just Enterprise, Developer and Evaluation? We realize that compression is an Enterprise only feature, but if non-Enterprise users could see how much space they would save, it might help them determine whether Enterprise Edition is worth while?

(OK, here's a really geeky part... speaking of Enterprise, my #1 son sent me this link yesterday. )

Now I'm not saying that Microsoft should be obnoxious about this and say "See what you could save if you upgraded to Enterprise", and do that whether the user wants to know or not. It would be on request, when the user ran this procedure.

I also understand that the procedure actually applies compression to a sample of pages to compute the savings potential. And it was confirmed last night when I started researching this question that Enterprise features are just turned on and off through a switch so it actually would be easy enough to just not do the check for Enterprise Edition within this stored procedure. Microsoft is looking at possibly making this change, but it looks like it might not happen before the next major release.

2. Since page compression is applied on a page by page basis, what's the point of compressing a read-only table? Each page would be compressed, perhaps into a small amount of space, but if no new rows were added, the empty space on each page wouldn't get used and we would still have the same number of pages in the table.

Although it is true that compression is applied on each page individually, you need to rebuild the table or index in order to compress it, with ALTER TABLE or ALTER INDEX. And rebuilding a table or index moves the data to all new pages. So as the table is being rebuilt, when a page gets full, it is compressed, but then more rows can be added, and when are sufficient number are added, it gets recompressed, and keeps getting recompressed as more rows are added, until there is no chance of any more more rows being added. So during the rebuild process the table really isn't read-only, as the original data is being inserted into the a new copy of the table. You could end up with far few pages.

This is pretty easy to test out, by just compressing a relatively full table of your own into a test table (so no one will update it) and then compressing it. It should take less space.

There are lots of compression scripts, plus a lot more detail about how compression works, in my new book, which is scheduled for release TOMORROW!!

3. Where is the metadata that contains the default values of SQL Server stored procedure parameters?

Uh, the answer appears to be 'None of the above'. There is no metadata, either system views or system tables (viewable using the DAC) that contains this information.

You can read this Connect request for more details (and even vote for it if you like):

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=234143

It does seem strange that this information is not available and it's hard to believe SQL Server parses the procedure definition every time it wants to know the default. It turns out that even if all you want to know is whether or not a default exists is not easy to find. (You might want to know that to be able to determine if a parameter is optional.)

So now you know.

~Kalen

Enabling Data Compression On All Tables By Default in a SQL Server 2008 Database

jmkehayias — Tue, 24 Feb 2009 16:35:24 GMT

One of the great things about answering questions online is that every now and then you get one of those Aha! moments and you create a solution to a problem that once written seems really obvious and makes you wonder how that ever escaped you previously. This happened recently on the MSDN Forums, but in this case it wasn't my Aha! moment, it was Aaron Alton's in a follow up response he posted behind me. The question asked was:

"Is there a way to set the default compression level for all tables created in a database?"

I misread the question initially and thought that it was a two part question, first asking if it could be set by default and then if not, could it be changed afterwards, so I responded with how to alter the table and indexes afterwards since there isn't a default option to set it for all tables. Later Aaron replied how to use a DDL Trigger to automagically add Database Compression to all tables. He followed up today with a blog post titled:

Enabling Data Compression By Default in a SQL Server 2008 Database

Its an interesting solution to the problem posed by the original poster, and when I read it in the forums, my first thought was Duh! Why in the world didn't that cross my mind? I'll never know but it was definitely a Aha! type of thing. There are some caveats to this solution, which you can read about in his blog post, or on the original forums thread:

Is there a way to set the default compression level for all tables created in a database?

Backup Compression with SQL Server 2008

ejohnson2010 — Thu, 02 Oct 2008 21:01:33 GMT

SQL Server 2008 provides a feature, which, in my opinion, has been far overdue -- backup compression. For too long, if you wanted the benefit of compressed backups, you had to look at a third-party tool. Now, backup compression is built right into SQL Server 2008, and what's even better is that it's easy to use. All you have to do is append the WITH COMPRESSION option to your backup statements and you are off to the races. This bit of code will back up the AdventureWorks2008 database using compression:

BACKUP DATABASE AdventureWorks2008
TO AdWorksBackup
WITH COMPRESSION

Using compression with SSMS is just as easy. Simply set the compression option on the Options page of the Back up Database dialog.

You may be asking yourself: How effective is this compression? The answer can be tricky because it depends on the structure of your database and the type of data being stored. When I backed up my copy of the AdventureWorks2008 database, which is using about 700 MB of disk space, I got a 147 MB compressed backup file. Compare that to the 636 MB file I got when not using compression.

Here's what else is cool: You can change the default compression behavior of your entire server. On the Database Settings tab of the Server Properties dialog, you can select the option Compress Backup. Alternatively, you can run the following t-SQL code:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'backup compression default', 1
RECONFIGURE

The first command enables advanced options and the second will make compression the default for all backups. If you go this route, you won't have to change a thing about your backup scripts in order to take advantage of compression. Now, just a simple BACKUP DATABASE statement will use compression. To run a backup without compression when it's the server default, simply use the WITH NO COMPRESSION option.

SQL Server 2008 finally offers backup compression natively and I hope you find it to be a useful feature. It's great for saving disk space and you no longer have to zip backup files before moving them over the network just to improve the copy time