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

Geek City: Clearing a Single Plan From Cache in SQL Server 2008

Kalen Delaney — Fri, 31 Oct 2008 23:43:00 GMT

I just today found out about something that was in plain sight in the Books Online... but the documentation is so vast, I haven't discovered everything yet.

I blogged about clearing a single plan from cache in SQL Server 2005 a while back, and mentioned a rumor that there would be a new way to do this in SQL Server 2008. And then just today, someone posted a comment on that blog post, asking about the new 2008 method.

One of my contacts on the SQL Server team at Microsoft just sent me an email telling me about new features related to plan caching, as I am starting to work on updating that chapter for my 2008 book. He mentioned that DBCC now takes parameters, and one of the parameters is a plan handle!

DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ]

There is full example in the Books Online, so I won't repeat it here. If you don't have a local copy of BOL handy, you can read about the new option to DBCC FREEPROCCACHE online here:

http://msdn.microsoft.com/en-us/library/ms174283.aspx

Have fun!

~Kalen

Kalen Delaney : metadata, SQL Server 2008

Geek City: Two Compression Questions and Parameter Metadata

Geek City: Clearing a Single Plan From Cache in SQL Server 2008