THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: Two Compression Questions and Parameter Metadata

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):

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.


Published Tuesday, March 10, 2009 11:46 AM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Roy Harvey said:

Personally I thought Microsoft missed an opportunity by not adding sp_estimate_data_compression_savings back into SQL Server 2005, but if the estimate is based on actually compressing some data that would be a problem.  As a marketing idea, however, it seems like an obvious choice to make it as widely available as possible.

March 10, 2009 5:11 PM

jerryhung said:

Cool good questions

I suppose for compression, eager customers can restore production DB on a test developer edition and estimate the compression

Same goes for Backup Compression, it'd be nice to estimate how much % saved for SQL backups. Red Gate SQL Backup has this estimation tool with 3 levels compression, which is neat

March 13, 2009 12:57 PM

Peso said:

March 26, 2009 5:44 PM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement