Monday July 28th I will be sharing the podium with my colleague and friend Sergey Barskiy to give a talk about SQL Server 2008 functionality for our .NET developer brethren. It should be a great time, details of the event can be found on the ADNUG site.
I got the email tonight from my next speaking engagement and got quite a laugh...one missing letter has left me as "Whiney". Maybe Doug is trying to tell me something?
If you like reading whitepapers have I got the find for you. The Data Platform Insider has seven articles across the 2008 product for your reading pleasure. You can feel the buzz cranking up as we get closer to RTM. These are always such fun times...
Mary-Jo Foley is reporting an announcement of August as the RTM date for the hotly awaited release. Let the online chatter begin...
We're back talking SQL Server 2008, hopefully with announcements of RTM around the corner. Today I want to cover one of the great new features in the upcoming release of SQL Server - data compression.
Data compression allows you to store your data more effectively and reduce the storage resources needed. The biggest winners of this feature will be instances that have large I/O bound workloads, such as data warehouses.
To give some perspective, a 4-byte integer in SQL Server 2005 would take up 1 byte + 4 bits per value with data compression enabled in SQL Server 2008. Apply this across millions of rows and you have some significant savings.
Compression can be applied at either row or page level for the following object types:
- Tables
- Nonclustered indexes
- Indexed views
Partitioned and non-partitioned tables may be compressed, with partitioned tables allowing further specification of row, page, or none at the individual partition level.
Indexes of compressed tables do not inherit the settings of their parent object. You must set each index individually.
Setting up compression can be done in a couple of ways. Like so many things SQL Server related, there is a wizard. For an existing item, right click the object in Object Explorer and choose "Storage" --> "Manage Compression..." as seen below.
Upon selecting "Manage" the wizard pops up to take you through the process.
The first step in the wizard displays the partitions associated to our object and allows us to set the compression type for each. Had this example table been partitioned we would have seen details for partition function boundary and a row count. We also have a handy "Calculate" button to show the compressed space necessary.
For this simple example I want to apply compression to all partitions (all one of them) so I select the smartly named "Use same compression type for all partitions" checkbox and choose from the drop-down list to the right.
The next step of the wizard presents us with options to either script the compression changes, run them immediately, or schedule them (presumably for an off-peak hour when you won't run your instance into a ditch). I really appreciate the choice given to me here as my needs may change drastically depending on the objects or environment where I happen to be working.
At this point we choose "Next" or "Finish" and receive the output of all our clicking:
USE [ExampleCode]
ALTER TABLE [dbo].[Invoice] REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = PAGE
)
Executing the query applies compression to the object, with no outward signs of compression to anyone viewing via Object Explorer or Intellisense.
To find the compression style of an object you can run the following query against the sys.partitions compatibility view:
SELECT *
FROM sys.partitions
WHERE object_id = OBJECT_ID('dbo.Invoice', 'U');
The data_compression_desc column will list the compression style. To get statistics on specific partitions you can query the Dynamic Management Object function sys.dm_index_operational_stats:
SELECT page_compression_attempt_count, page_compression_success_count
FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('dbo.Invoice', 'U'), NULL, NULL);
Like all things SQL Server, there are considerations before going out and running wild with compression:
- Compression is available only in Enterprise and Developer editions.
- Compression does not change the maximum row size of a table or index.
- A table cannot be enabled for compression when the maximum row size plus the compression overhead exceeds the maximum row size of 8060 bytes. This check is done at initial compression and again as rows are inserted or modified.
- You can enable or disable ROW or PAGE compression online or offline. Enabling compression on a heap is single threaded for an online operation.
- The disk space requirements for enabling or disabling row or page compression are the same as for creating or rebuilding an index. For partitioned data, you can reduce the space that is required by enabling or disabling compression for one partition at a time.
Next time...encryption and announcement of another place you can come hear me speak, if you can bear it. ;)
Say you're a DBA responsible for several instances of SQL Server running in your company and you have a long deserved vacation coming up. You want to know about data changes to certain mission critical tables while you're away but don't want your spouse noticing the company laptop tucked away in the carry on luggage. Well, thanks to the SQL Server Samples blog you can put your fears to rest with the open source SQLRSS project. The project writes changes to an RSS feed that can be subscribed to, alerting you of data changes as they take place. With an RSS reader you can stay on top of changes and not be the dork with a laptop open down on the beach (for full disclosure I've been that dork).
This project is SQL Server 2008 specific as it makes use of Change Data Capture (one of my favorite new features). I'm going to see how difficult it is to re-engineer this for SQL 2005 making use of similar auditing tables. Another post to follow...
So check out this open source project on that VPC/VM you've got running with SQL Server 2008 RC0. Your spouse/significant other will appreciate it. ;)
The inaugural MTC Summit has kicked off here in Chicago. Jay from the VB team kicked off the keynote with another session from Rocky.
It should be a great day meeting clients and hearing presentations from fellow Magenicons. I will be presenting my DMV talk at 1:15.
Our marketing folks did a great job with this event. We found out last night at dinner that we had to close registration so that we guaranteed seats for all signing up. Not bad for a first year event.
Here's a picture from the keynote. A little blurry but not bad from my Dash.
I will blog a bit more later today about the event as a whole and include my slides and code samples.
Some of you may have been asking yourself this question recently if you've been checking this site. I've even gotten some good natured ribbing from James and Bill (prompting this post I suppose).
I have a backlog of thoughts rolling around in my head, they've just been put on hold for the moment while I get ready for this week's MTC Technology Summit. I'm speaking on Dynamic Management Views and have decided to get a little deeper into the Resource database for this crowd.
Once I get back I've got plenty of SQL Server 2008 things to get to, including some changes in RC0 from CTP6.
So yeah, this blog is still in business...
It’s now released to the general public, not just MSDN or TechNet subscribers. I was really hoping to see the arrival of the tool for removing CTP instances. I had installed the CTP on my laptop for this very test, I guess it will have to wait.
Get your download on here.
I’m a little late getting this out on the blog, but I wanted to make mention of this event as it will be a great time. It’s especially exciting for me as I will be presenting (on SQL 2005 DMVs). Here is the official release from Magenic:
Magenic is holding a full-day, two-track mini-conference on June 20. We have put together a great lineup of speakers and topics, including 2 keynotes and 8 sessions. This will be a full day of hard-core technical content and fun!
The event is being held in Downers Grove near Chicago, IL. It starts at 8:30 AM and runs through to a reception at the end of the day at around 5 PM.
The event is by invitation only - specifically invitation by one of Magenic's sales people. If you are already a Magenic customer and you'd like an invitation, please contact your Magenic AE and let them know. If you are not a Magenic customer please email info@magenic.com and let us know you'd like an invitation.
The event is free, and includes both lunch and a reception at the end of the day. You are responsible for any travel expenses involved in getting to the event. Magenic is arranging a block of rooms at a nearby hotel with special pricing and ground transportation between the conference and hotel.
The following link has more information about the event
http://magenic.com/Default.aspx?tabid=1225
If you have questions that aren’t answered on our web site feel free to drop me a line and I will do my best to get the information for you.
I ran across a crazy behavior in the SQL Server 2008 CTP 6 tools around changing a table via the "Design" option in Object Browser. I typically script all changes to objects as I am not a fan of GUI development in general (and this may have cemented the opinion forever). Maybe I was just in a hurry today, but I connected to a database to add a column to an existing table and was presented with the following ugly message box:
I knew this wasn't permissions related as I had been happily altering schema in this instance for weeks (even a couple stored procedures that day). So what's a geek to do when his path is blocked? Dig into the options! So away I went down the Tools--> Options--> Designers path. And there it was, staring me in the face...
A rather odd little checkbox with the text "Prevent saving changes that require table re-creation". I was positive I hadn't seen this before, so positive I fired up SQL 2005 SSMS to compare, and found that it truly was new functionality. For the life of me I'm not sure why this was added as a default behavior. Especially one that might override the security privileges of a user.
The only documentation I found online for this is from the MSDN SQL Server Developer site. There was one Connect item with a Microsoft comment referencing the fact that it is on by default, I'm assuming the Change Data Capture feature is the answer. I've got some emails out to see what the true intention was here.
So if you see the nasty pop up above while working in your CTP you will now know that you've been attacked by a checkbox. ;)
UPDATE: I got an email back from Buck Woody of the SQL team right after posting last night with the purpose for this checkbox, you can check it out here. Thanks Buck!
We're back on the 2008 topics here at the W, talking about column sets. Column sets extend sparse columns allowing you another mechanism to query your desperate data.
A column set is implemented as an XML column on your table. For this example I will use the same table in my earlier sparse column post. Here is the syntax:
IF OBJECT_ID('dbo.ProductSparse', 'U') IS NOT NULL
DROP TABLE dbo.ProductSparse;
CREATE TABLE dbo.ProductSparse
(
ProductID INT IDENTITY(1,1)
,[Description] VARCHAR(100) NOT NULL
,Col02 INT SPARSE NULL
,Col03 INT SPARSE NULL
,Col04 INT SPARSE NULL
,Col05 INT SPARSE NULL
,ProductSparseSet XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
);
Now we will insert some data. The script below will create 4000 rows in our table spread across the various sparse columns.
--Insert some data
INSERT dbo.ProductSparse ([Description], Col02) VALUES ('aaa', 1);
INSERT dbo.ProductSparse ([Description], Col03) VALUES ('bbb', 2);
INSERT dbo.ProductSparse ([Description], Col04) VALUES ('ccc', 3);
INSERT dbo.ProductSparse ([Description]) VALUES ('ddd');
GO 1000
If we query the table with "SELECT *" we will get back the column set only, all our SPARSE columns will be obscured. Here's a snippet of our table:
SELECT *
FROM dbo.ProductSparse;
Another nice touch of the column set is that we can use our normal means of dealing with XML data for this column. We can insert or update data as easily with the column set XML as we can the actual column(s). One caveat -- if you do use the XML for updating sparse columns any column not referenced in the update will be set to NULL.
While this feature was implemented mainly to support future SharePoint functionality there are many business cases in the OLTP world that can benefit. Product catalogs, census style information or anything else where desperate data exists can be more successfully modeled with sparse columns.
You can find the slides and code samples from my talk last night at the Atlanta.MDF on my SkyDrive. Thanks to all that came out last night. We had some great questions and a lot of good conversation afterwards.
I will be speaking at the Atlanta.mdf user group this coming Monday, on the topic of Dynamic Management Views in SQL Server 2005. If you are in the perimeter area stop by, the meeting starts at 6:30PM with food and drinks provided.
The first meeting of the Gwinnett Georgia Microsoft User Group will be held tomorrow night at Gwinnett Technical College. I am always happy to see new user groups pop up, especially ones that are close to home. ;)
Magenic will be sponsoring the food tomorrow night. If you will be in the area swing by and check it out.