Presenting Today at the Pragmatic Works Webinar

http://www.flickr.com/photos/chrisschoenbohm/5104367911/

Today I will be presenting a Free Training Webinar for Pragmatic Works at 11:00 am US EST.  My topic is Page & Row Compression How, Why, and When.   This will be the 8^th time this year that I have done this presentation! 

And yet the topic never get’s old to me.  Compression to me is like watching Sports Center, every time you use it you find something different, something new, something that you didn’t notice the first time around. 

“So Balls”, you say, “Why did you team up with Pragmatic Works and Why should I listen to you present on Compression?”

Great Questions Dear Reader let’s dive right in! 

WHY PRAGMATIC WORKS?

Since I have moved to Florida I kept hearing all these great things about a company named Pragmatic Works.  At my first SQL Saturday I met a guy named Jeremy Sirois (@JeremySirois ).  Jeremy is a Regional Account Manager for Pragmatic Works.  He’s from up North, I grew up in the Midwest.   We talked we joked around it was a fun evening.  That night I also met Brian Knight (@brianknight | Blog) for the first time.  We talked only briefly he was nice down to earth, and extremely tall.  I mean crazy tall, I’m picking him in a game of basketball tall.  So tall to dunk the ball he has to bend over tall.  I’m just saying….. the man is tall.

I soon met Adam Jorgensen (@Adam_jorgensen |Blog ), Devin Knight (@Knight_devin | Blog ) who is up to his eyeballs in beautiful baby twins right now (CONGRATULATIONS DEVIN!!), Jorge Segarra (@SQLChicken | Blog), Mike Davis (@MikeDavisSQL | Blog), and Gareth Swanapoel (@GarethSwan | Blog).  All of these gentlemen I’ve had the wonderful opportunity to talk with, to see in action as presenters, volunteers, and experts within our community.  And I enjoyed every minute of it.  I’m thrilled that I get to hang out with such wonderful people.

But that wasn’t all Dear Reader, not only were these top notch professionals I soon found out they are Top Notch human beings, and it starts from the Top Down.  I’ll point you over to Brian Knight’s Community Manifesto at Pragmatic Works for more. 

Pragmatic Works gives away seat’s in their Webinars, not the Free ones that I’m doing, but seats in the Paying Ones to unemployed people within our community.  They work with Veterans who have served our country Bravely to give them training opportunities to learn new technical skills after they come home and transition to civilian life.  They donate their time to the Community through SQL Saturdays, and free Webinars of their own.  They team with Microsoft and I’ve seen quite a few of those same names presenting over in the beautiful Tampa Offices for Microsoft for free community events.

They are authors, they are MVP’s, they are Patriots, and they are just all around good human beings.  When I got the opportunity to participate I jumped with both feet and anytime you get the opportunity to learn from them you should jump as well.

Speaking of which Mike & Jorge are just two of the four Pragmatic Works speakers who will be teaching a full day Pre-Con for SQL Saturday in Orlando on September 23^rd.  Click here to view that full day of training for only $99 (this price includes lunch, coffee, and tea)!

WHY SHOULD I ATTEND!

 

One of the most important things I’ve found is the steps you should take in determining what it is you should be compressing.  As a matter of fact I’ve even made a Monday Morning Checklist that you can download that will give you the steps that I would follow if I was there sitting with you trying to determine what to compress.

And all of the Scripts that you will see today are available for download on my Resources Page.  I’ve taken a lot of time to learn about this fascinating and very new technology so I can help you learn what you need to know as quickly as possible.  Personally I could spend days, and I have, just working on Compression. 

But you don’t have time for that!  You need to understand how it works so you can report on it to your Boss, but you want to make sure that you don’t learn it so quick that you end up forgetting it.  That is one of the MANY great things about Pragmatic Works is this Webinar will be online afterwards for you to download it and re-watch it.  My samples and my Deck will be Up on my website.  I will continue to blog on this topic, and you can find a lot of great ways to continue to educate yourself.  However this Webinar provides you a wonderful opportunity to ask me questions live and in person. 

And I will make you the promise that I make at every conference, if you ask me a question and I do not know the answer I will research it and I will Blog on it.  Some of the best learning I have done on the topic of Compression came after SQL Rally.  I got a lot of wonderful questions, and I scoured over all the fun things I could do with compression and I wrote on every single topic.  Don’t be afraid to ask, your question just might be the one that pushes me to keep on learning.

So come and join us today at 11 am, here is the link to Register!

Thanks,

Brad

SQL Saturday 79 Recap – (THANK YOU SQL SATURDAY 79)

It is hard to believe that SQL Saturday 79 has come and gone.  It was held in the Beautiful Carl DeSantis Building on Nova Southeastern University.  The day started out with a 3:30 am wake up.  I wanted to make sure that I had everything posted on the blog before I headed down so if anyone was looking for downloads or slides I could send them to the Resource Page for it.  After a quick shower and grabbing a cup of coffee for the ride, I hit the road about 4:30 am. 

The trip down is 187 miles from the new house in Winter Haven to Nova Southeastern, with registration starting at 7:30 and the keynote at 8 I wanted to make sure to be there in plenty of time.  It was a nice drive down, I always like watching the sun rise in the car.  I used to see it all time during the drive in to DC & on vacations driving from Virginia to Florida before the move down here.   So with the start of a beautiful day and a peaceful drive, I get to the parking garage and run into my good friend Kendal Van Dyke (@SQLDBA | Blog) on the way into the building. 

After finding registration, finding Coffee, and the restrooms it was off to the speaker room to get a Speaker Shirt.  I don’t normally spend a lot of time in the Speaker room.  I love to meet new people at SQL Saturday’s, but I didn’t make it down for the Speaker Dinner the night before so this was a nice chance to meet some of the other Speakers like Mike Hillwig (@mikehillwig | Blog), Herve Roggero (@hroggero | Blog)  , Bradley Schacht (@bradleyschacht | Blog) and it was a chance to say Hi to others that I’ve met before.  Andy Warren (@SQLAndy | Blog), Mike Davis (@MikeDavisSQL | Blog),  Mike Antonovich (@MPAntonovich| Blog ), Jose Chinchilla (@SQLJoe | Blog), Rodney Landrum (@SQLBeat | Blog), and Thomas LaRock (@SQLRockStar | Blog).

Tom LaRock made my day by remembering me.  I have been very lucky and fortunate to meet some really awesome people in the SQL Community.  There are a lot of people that I’ve gotten to hang out with on a regular basis (point’s to the list of names up above), that I just grin ear to ear thinking about.   It wasn’t that long ago that I set in one of Tom’s sessions for SQL Saturday 49, and later read Noel McKinney’s(@NoelMcKinney | Blog) Blog about it.  At the time thinking I want to get in on this, I want to present, I want to blog, I want to take the next step in my life as a DBA.   And now I say “Hi Tom I’m Brad Ba…”, and he say’s “Hi Brad I remember meeting you before.”  Awesome.

“So Balls,” you say “We get it your there, speakers are there, your gushing like a 14 year old at a Justin Bieber Concert, What about the SESSIONS?”

Well Dear Reader, I missed the first one because I was reviewing all my presentations, cutting some material and adding a little more to others.  But after that, I jumped right in!

RODNEY LANDRUM T-SQL ALPHABET SOUP

Rodney is a SQL MVP and has been working with SQL Server for over 12 years.  There are four letters that describe Rodney’s T-SQL Alphabet Soup, no none of the dirty ones, A.W.L.D.    A is for Awesome, W wait for it, L Ledgend ……. D DAIRY.   Ahhh…..Barney Stintson would be so proud.

I would normally blog details, but Rodney’s Presentation is very interactive and a lot of fun.  He goes one letter at a time and asks the audience to guess what command he will use.  This is a great mental exercise for those of us that use SQL, and it is a lot of fun to participate in.   And it will be one of the presentations given at SQL Saturday 85 J , CLICK HERE to look at the full line up for that great event.

BRADLEY BALL PAGE & ROW COMPRESSION HOW, WHY, AND WHEN

My Early Birds! We had another 15 minutes to fill the room after this picture!

Compression is a great topic.  Pictured above is my room as people started coming in 15 minutes before the session started.  I had one of the best crowds I’d ever had.  We cover a lot when I do this presentation, and I had a great group of people. By the time the presentation got started we had almost filled the room.  People asked questions, we had a really good back and forth.

The main thing that I tell people is that when you apply Compression you do not want to do it blindly.  I have a Monday Morning Checklist on my Resources Page and it lists all the steps you can take to determine the tables in your database that are candidates for compression.   I like to think of Compression the way that you would Indexes.  You wouldn’t just toss an index on a table and never check to see if it effected your query plans/query response times, over head for different operations (updates/inserts), or if the data was actually worth indexing.  And Compression needs the same consideration to make sure you do it right.

Compression is also very Dynamic, with the way that Allocation Units work within tables, Indexes, and Partitions you can apply Compression in a lot of different ways and we cover the full gambit.  This was a great start on the presentations and I want to say a BIG Thank You to everyone who attended this was a lot of fun to present and you were a great crowd!

LUNCH

Lunch was served by Azteca Real Mexican Restaurants and it was delicious.  The line was vast, the picture you see above is after waiting for 30 minutes and then going to the back of the line, the sever table was on the opposite end from me.  There were a lot of people to feed and the SQL Saturday team did a great job of getting everyone fed, with hefty portions. 

Quick thinking on their part led them to open a line on either side of the serving table to get the crowd through as quickly as possible.  I chatted with folks in line, sat with Serge & Javier (two new acquaintances) and talked about the emergence of Business Intelligence, the free training offered by the Great Folks over at Pragmatic Works on a weekly basis, and a whole host of other topics.

With my stomach full it was off to see a SQL Rockstar!

THOMAS LaROCK MONITORING DATABASES IN A VIRTUAL ENVIRONMENT

I can’t speak for you Dear Reader, but I’m seeing a lot of Virtualization going on.  There are some databases that will not translate well, that must have physical hardware.  But these databases are normally the exception and not the rule.  We all know that Virtualization is a great way to reduce your footprint, go green, yadda yadda yadda. 

So you’ve gone Virtual, now what.  Now you are still going to have users coming up to you saying “The Application is running slow, can you fix the database?”  And you will still need to ask the same questions.  Slow compared to what?  Do you have baselines, do you know how your server is behaving, Do you know how your Host & Virtualized instance are working in tandem? 

Ah I got you with the last one, and Tom got me as well.  Tom does a great job of stepping through the different pain points that you have, CPU, Memory, Disk I/O, and Network Latency.  These are all very important things to consider, after all they were important when it was just Physical Hardware.  Now you have virtualized Hardware don’t you want to make sure that you know what is going on?

The presentation is shaped around VM Ware.  The terminology is specific to it and if you are running VM Ware and have Server Engineers talking about crazy sounding stuff like ESX Hosts, then you need to know crazy things like VM Ready Time, Reservations, Limits, and Shares.  You need to know that Swapping and Ballooning can be bad, and if you understand how SQL Server uses memory it translates pretty quick.

Tom talks about his (Patten Pending) BMFF Best Metric Friend Forever.  And when it comes to troubleshooting your VM’s these are things you need to know.  If your working with Hyper-V this is still a great presentation to attend because it will get you thinking about the questions you should be asking, and the terminology that you should be learning to better support your environment.

BRADLEY BALL TRANSPARENT DATA ENCRYPTION INSIDE AND OUT

I’ve worked a lot with Transparent Data Encryption.  It is a great technology and was a great addition to SQL Server 2008.  It is a very simple technology to turn on and off.  And like my friend Colossus to the left it affects the Physical Structure only.

During this presentation one of the cool thing we do is taking an unencrypted backup tossing it into a Hex editor and looking at the data you can start to pull out.  The fact is there is a lot of meta data and header information, but there is also all of your other data stored in plain text.

Then take that same database, apply Transparent Data Encryption, back it up, and place that backup in a Hex Editor.  Aside from some header information that cannot be encrypted so that SQL can still read it, the difference is like night and day.

We do that in this session and then I cover some tips on how to automate the backup and on disk management of your Certificates as well as the impact this will have on Advanced Features such as Mirroring and Log shipping, as well as the additional considerations you should account for when planning your DR strategy.

Once again a very interactive crowd, and a very BIG Thank You to everyone who attended!   Some of the most gratifying moments of the day came in the conversations after this session and my next one.

BRADLEY BALL SQL INTERNALS, RECOVERY MODELS, & BACKUPS! OH MY!

3rd Presentation of the Day we are now in the Home Stretch

This was the first time I had done this presentation.  My good friend Kendal Van Dyke sat in and gave me some great pointers for the next time I do this, and I’m really excited for next time.

I think this is the year where a lot of things clicked for me and I grew the most as a DBA.  I remember reading Paul Randal’s (@PaulRandal | Blog ) about the internal contents of a Page a couple years ago.  And it took me several reads to process it to the point I could make flash cards on it.  This year the internal make up of a Page has become familiar like the view out of a window.   And as I’ve continued on with learning I’ve noticed that there are a lot of things that come up time and time again.

Sometimes I stop and go, “How have I lived my WHOLE DBA life and not known this!”  So this presentation is not about becoming an expert at internals.  It is about getting to the point where you can start that journey.  In it we covered ACID, SQL’s Internal Components, the Data Hierarchy, Transaction Log Internals, Recovery Models, how Recovery Models relate to SLA’s & DR scenarios, the different types of Backups, and what a Piecemeal Restore really does. 

I had a LOT of great questions.  And even though I was able to answer all of them I think a lot of them deserve follow up blogs.  I pointed people repeatedly to the great free resources that are out there, How Do You Learn & Top 5 Reasons You Should Be On Twitter, and encouraged them to do free and paid training where they can.  The point was to better themselves, which is always a good thing.

Another BIG Thank You to everyone who attended this presentation.  I had a great time, and really appreciated all the questions.  After this presentation I had someone come up to me and say "I've been learning on topic X and the way you explained it everything just clicked and I got it."  That is one of the best thing's I've ever been told after a presentation!

WRAP UP

And with that Dear Reader we called it a day.  I sat and talked with Kendal, Andy, and Mike and watched as the raffles began.  It was around 5 pm, a little under 12 hours since I had awoke, and I decided to call it a day, with a 3 hour car ride and another 187 miles to home.  I got some Tweets about the after party, and I wish I had made it.  Maybe next year we will make a family vacation out of this, or even better just me and Mrs. Balls, so we can stay a little longer.

This was a great event and a great day despite all the difficulties the team hit during the day (10 speakers no showed the event)!  We had a lot of great people that filled slots, the team worked tirelessly to get it all pulled together, and by the end of the day I saw a lot of smiling faces.

I Can’t wait till next year.  Now off to SQL Saturday 85 in Orlando September 24^th J!

As Always Thanks for Reading!

Thanks,

Brad

Presenting Tonight at OPASS!

Hello Dear Reader just wanted to write a quick post.  I will be at OPASS Tonight presenting Page & Row Compression How, Why, and When.  I’ve presented on this topic many times, and it is always a great time.  Tomorrow I’ll have my deck and demo’s posted on my Resource Page.

Compression is a topic that has a lot of depth to it and this is a good introduction into the subject.  If you get a chance to come out we are meeting at the Hampton Inn at Lake Mary in North Orlando.  Here are the full details on the Event.  Hope to see you there!

Event Date/Time:

Tuesday August 9 @ 6:00 PM

Location:

Hampton Inn Lake Mary
850 Village Oak Lane
Lake Mary, FL 32746

Map/Directions:

Bing Map to Event Location

 

Bradley Ball

Speaker Bio:

Bradley Ball is a MCITP SQL 2005 & MCTS 2008 DBA with over 10 years of IT experience.  Bradley spent 8 years working as a Defense contractor for clients such as the U.S. Army and The Executive Office Of the President of the United States.  He is currently a Sr. SQL DBA Staff Specialist for Publix Supermarkets.  He has presented at SQL Saturdays 62 & 74, for the MAGICPASS & OPASS SSUG’s, SQL Rally, and at the PASS Summit in 2011. Bradley can be found blogging on http://www.sqlballs.com

Title:

Page and Row Compression How, When, and Why

Abstract:

Page and Row Compression are powerful new tools. Vardecimal shipped with SQL 2005 SP2, Page & Row with SQL 2008 RTM, and Page & Row with Unicode Compression with SQL 2008 R2. Get an overview into how each version of compression works internally. Learn how your Allocation Units will determine if your data is a candidate for compression. Understand how your tables Update and Scan pattern’s affect the compression types you should consider. And what you should you take into consideration for additional overhead.

SQL Shot Speaker: Rodney Landrum

Speaker Bio:

Rodney Landrum has been architecting solutions for SQL Server for over 12 years. He has worked with and written about many SQL Server technologies, including DTS, Integration Services, Analysis Services, and Reporting Services. He has authored three books on Reporting Services. He is been a regular contributor to SQL Server magazine, sqlservercentral.com and Simple-talk.com. Rodney is also SQL Server MVP.

SQL Shot Title:

Availability Groups and High Availability/Disaster Recovery (HADR) for SQL Denali

Abstract:

Rodney will be giving the group a high level overview of your HADR options and how you can leverage this technology for reporting applications.

 

To RSVP Click HERE to go to the OPASS Website.

Thanks,

Brad

SQL SATURDAY 79

http://www.flickr.com/photos/14529149@N07/1492197386/

This weekend Saturday August the 13^th, I will be trekking down to beautiful Ft. Lauderdale to speak at SQL Saturday 79.  I applied to speak back in March, and at the time I submitted 5 different sessions. I was brain storming at the time.  I’ve done a lot of presenting on Compression, and I love to present on it, but I had a couple other topics that I wanted to present on as well.  And that is one of the great things about SQL Saturday, it is all about giving Attendees the chance to learn, and giving Speakers the chance to present.  And will I ever be presenting!  I will be speaking not once, not twice, but THREE times this Saturday!

“So Balls”, you say, “What will you be presenting on?”

So Glad you asked Dear Reader J, and away we go!

PAGE & ROW COMPRESSION HOW, WHY, AND WHEN

http://www.flickr.com/photos/meegs108/4792232575/

I’ve presented on this topic at SQL Saturday 62 & 74, SQL Rally, MagicPASS, and Tuesday August 9^th at OPASS, the SQL Server User Group for North Orlando.  Compression is a great topic, and is a technology that I believe will only increase in usage.

So often people confusing Compression, with squeezing more into one place.  It is much more like efficiently packing what you have in the best logical order.  Understanding how compression works, and what you should be compressing, and why compression could help you is key to benefiting from the cost of an Enterprise Edition License for SQL 2008 and up.   

I’m presenting a Deep Dive on this topic at the PASS Summit in October, and this presentation is the perfect preparation for that Deep Dive.  If you get a chance to stop by I’d love to have you, here is the abstract for my session.

“Page and Row Compression are powerful new tools. Vardecimal shipped with SQL 2005 SP2, Page & Row with SQL 2008 RTM, and Page & Row with Unicode Compression with SQL 2008 R2. Get an overview into how each version of compression works internally. Learn how your Allocation Units will determine if your data is a candidate for compression. Understand how your tables Update and Scan pattern’s affect the compression types you should consider. And what you should you take into consideration for additional overhead.”

Next up a little Transparent Data Encryption

TRANSPARENT DATA ENCRYPTION INSIDE & OUT

In the wake of all the hacking scandals that we’ve seen recently, Security is at the forefront of many people’s minds.  If you haven’t had your CIO, Bosses Boss, or your Boss ask you about what you can do to “better secure” your databases, you will at some point.

 If you are paying for an Enterprise Edition License for SQL Server 2008 and above you have Transparent Data Encryption available to you.  It is really easy to enable, but you need to understand how it works, what it does and what it doesn’t do, additional backup considerations, what the impact will be to advanced features, and how this will add to disaster recovery scenarios.   We will cover all of that.  I’ve set up TDE with Mirroring, Log Shipping, on Vendor Databases, and on Custom Databases. 

When we talk about TDE it is physical hardening, like Colossus up above, and if you work with sensitive data this could be a real benefit to you.  I’ve got this chocked full of info.  I love questions so if you’ve got them get them ready because I’d love to help with an answer.  And if I don’t have an answer I’ll research it and blog about it!

Here’s the abstract for this presentation.

“Security is a very important part of your job and in how data is utilized. We have many tools to make data more secure, and starting in SQL 2008 we were able to add Transparent Data Encryption to that list. Find out What it does, What it doesn’t do, how it effects Read-Only Filegroups, Performance, Compression (Backup and Row/Page), and other Advance Features as well as some tips on how to manage it.”

And now a little fun with Internals!

SQL INTERNALS, RECOVERY MODELS, & BACKUPS! OH MY!

http://www.flickr.com/photos/keolson81/413390058/

Listing to Paul Randal's (@PaulRandal | Blog) MCM videos, he talks about spelunking in the Database engine. 

As someone who has rock climbed, caved, and generally spent a lot of time hiking around the mountains I really like the mental image of a couple guys lowering themselves deep into the SQL Engine with Hard Hat’s and Light’s on their heads. 

“Hey Paul you ever been here before?”  “Yeah, loads of time check out the granite stalagmites by the Access Methods!”  And if you are looking for spelunking that is what I’m doing, but instead of going caving we are spelunking around the kiddie pool.

Why should you come to this session?  Especially after you’ve just sat through my two other sessions J?  Because Dear Reader I want you to learn.  There is so much to learn when it comes to internals that people can spend weeks, and do, learning about one particular section and still have more to learn.  But as you continue your learning there is a lot of vocabulary you will need to know.

You need to know what the internal Data Hierarchy looks like.  You should know what the difference between a record, a page, an extent, an allocation bitmap, and Allocation Units/IAM Chain’s are.  You should know how your Transaction Log effects your Recovery Model.  You should know the different Recovery Model’s and how they relate to the different type of backups, and how backups relate to Service Level Agreements, SAL’s, and Disaster Recovery, DR.

But unlike any other presentation that I have ever done, this is just a talk, a conversation.  You could do demo’s with this, but that is a lot to squeeze into an hour & ½ .  I want to make sure that when you leave the room you know enough to feel informed.  But that you also know enough, to realize how much you do not know.  And you’re not alone. 

I don’t know it all.  Not even close.  But there are a lot of amazing experts out there that I’m still learning from, and probably will continue to learn from until they retire or I do.  But this will set you up with knowledge you might not have already known, and will make sure you are poised for future learning.  Here’s the abstract.

“The more you know about SQL Server the more you understand how it works. SQL Server is a product we use every day, and most of us know the big concepts. At the 10,000 foot view we know what Databases, Tables, and Columns are. But what makes up those Databases, Tables, and Columns. What are Records, Pages, Extents, and Allocation Units? What are Full, Simple, and Bulk-Logged Recovery? What are the differences between Full, Transaction Log, Differential, or Filegroup backups? This is an introduction to these concepts. In this session you will learn about the internal Structure, Recovery Modes, and Backups and be better prepared to for Future Learning and Managing SQL!”

So what are you waiting for Dear Reader, Click HERE to go register, come up and say “Hi!” and I hope to see you there!

Thanks,

Brad

SQL Rally Q & A Row Compression with Variable Legnth Unicode

This is another follow up on the Q & A’s that came about from SQL Rally, this question was asked by Jim Murphy (@SQLMurph | Blog).   This wasn’t so much of a question as it was a discussion during the presentation. 






I have Slide, see below, that lists the data types that can use Row Compression.  

Row Compression specifically takes all the empty space out of Fixed Length Data Types and stores them as efficiently as possible.  So in essence we take a fixed length field and treat it as if it has a variable size.

Jim very astutely pointed out that I had nvarchar listed as a Data Type that compresses even though it was not a Fixed Length Data Type and Jim wanted to know how Compression worked on an nvarchar Data Type.  To be perfectly honest I botched the answer.  But it led to a wonderful learning opportunity for me.

WHAT IN THE UNIVERSE IS UNI-CODE

The short answer is it is a Universal Character Set that allows for many non-English Characters and is governed by the International Standards ISO/IEC committee.  The Data Types in SQL Server that use Uni-Code are NCHAR & NVARCHAR and they use the UCS-2 character set.  UCS-2 allows exactly two bytes to represent each character.

So what in the wide wide world of sports does this have to do with Compression.  This means that when I declare an NCHAR with a 250 length that it is actually a 500 byte value.  When I type ‘Bradley Ball’ into an NVARCHAR data type instead of taking up 11 bytes of space it takes up 22.  So this encoding can utilize a lot of space that it will need when moving the data to the user, but not necessarily in Storage.

SQL 2008 R2 introduced Unicode Compression to SQL Server.  So to get back to the original question:

“Row Compression works by taking fixed length strings and Compressing out the unused white space from the record, NVARCH is a variable length string how does Row Compression work with a Variable Length Data Type?”

The short answer it strips out the unused extra encoding if there is any to strip out.  The long answer, TO THE DEMO MOBILE!

DUHNA DUHNA DUHNA DUHNA DEMO

Coolness is not Compressed for Batman

So Let’s start off by making our database.

USE master;

GO

IF EXISTS(SELECT name FROM sys.databases WHERE Name=N'demoCOMPRESSIONInternals')

     BEGIN

          DROP Database demoCOMPRESSIONInternals

     END

    

CREATE DATABASE demoCOMPRESSIONInternals

GO

USE demoCOMPRESSIONInternals

GO

Then we’ll create our table and populate it with a couple rows.

IF EXISTS(SELECT NAME FROM sys.tables WHERE name=N'myTable1')

BEGIN

     DROP TABLE dbo.myTable1

END

CREATE TABLE myTable1(

     myID INT IDENTITY(1,1)

     ,productName NCHAR(500) DEFAULT 'some product'

     ,productDescription NVARCHAR(1000) DEFAULT 'Product Description'

     ,PRIMARY KEY CLUSTERED(myID)     

) ; 

DECLARE @i INT

SET @i=0

WHILE (@i<5)

     BEGIN

          INSERT INTO dbo.myTable1(productName, productDescription)

          VALUES(

                   ('some product' + CAST((@i +1) AS VARCHAR(5)))

                   ,('Here is a Generic Product Description' + CAST((@i+2) AS VARCHAR(5)))

                   )

             

          SET @i = @i +1

     END

We’ll set on Trace flag 3604, and we’ll do a DBCC IND to get our page number, *Page Numbers will vary when you do this on your own.

DBCC IND(demoCOMPRESSIONInternals, 'mytable1', 1)

go

DBCC TRACEON(3604)

Go

And here are our pages.

Now a DBCC Page on page 153.

DBCC PAGE('demoCOMPRESSIONInternals', 1, 153, 3)

GO

Now let’s grab an edited look at our records, we mainly want to see the Length to each record in its pre-compressed state.

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

myID = 1                            

Slot 0 Column 2 Offset 0x8 Length 1000 Length (physical) 1000

productName = some product1                                                                                     

                                                                                                                 

                                                                                                                

                                                                                                                 

                                                                         

Slot 0 Column 3 Offset 0x3f7 Length 76 Length (physical) 76

productDescription = Here is a Generic Product Description2               

Slot 0 Offset 0x0 Length 0 Length (physical) 0

So we can see that even though Slot 0 Column 3 only contains 38 characters it’s length is 76.  You can see the doubling of the fixed length field as well looking at Slot 0 Column 2, even though it is an NCHAR(500) it has a length of 1000 bytes.  Now let’s apply Row Compression.

ALTER TABLE dbo.myTable1

REBUILD WITH (DATA_COMPRESSION=ROW)

go

We need to do another DBCC IND to get our newly rebuilt pages.

DBCC IND(demoCOMPRESSIONInternals, 'mytable1', 1)

go

Here are the new pages.

Now let’s do a DBCC Page on page 155 and let’s take a look at our data page and get the lengths.

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 1

myID = 1                             

Slot 0 Column 2 Offset 0xc Length 1000 Length (physical) 13

productName = some product1                                                                                     

                                                                                                                

                                                                                                                

                                                                                                                 

                                                                         

Slot 0 Column 3 Offset 0x19 Length 76 Length (physical) 39

productDescription = Here is a Generic Product Description2              

Slot 0 Offset 0x0 Length 0 Length (physical) 0

We see that the length is reduced greatly.   Slot 0 column 1 going from 4 bytes down to 1.  We see that Slot 2 went from 1000 Characters down to 13.

But the answer to the question lies in column 3 which went from 76 bytes down to 39.  When your nvarchar strings get to be even longer that get’s to be more space savings.  We did a nvarchar 1000.  If we maxed out that value it would be 2000 characters.

So let’s do that, we’ll update our values to max out our strings.

DECLARE @i INT

SET @i=0

WHILE (@i<5)

BEGIN

     UPDATE dbo.myTable1

     SET productname = (REPLICATE('a', 499) + CAST(@i AS VARCHAR(1)))

     WHERE myID=@i

     UPDATE dbo.myTable1

     SET productDescription = (REPLICATE('b', 999) + CAST(@i AS VARCHAR(1)))

     WHERE myID=@i

     SET @i=@i+1

END

And now we’ll take a look at page 155 again.

DBCC PAGE('demoCOMPRESSIONInternals', 1, 155, 3)

go

And what are the lengths of our slots?

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 1

myID = 1                            

Slot 0 Column 2 Offset 0xc Length 1000 Length (physical) 501

productName = aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1           

Slot 0 Column 3 Offset 0x201 Length 2000 Length (physical) 1001

productDescription = bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

bbb1                                

Slot 0 Offset 0x0 Length 0 Length (physical) 0

WOW, so even when we max out our fields, Row Compression still provides us with savings.  Slot 0 Column 2 is 1000 bytes in length, but it’s physical storage is only 501 bytes, and Slot 0 Column 3 our variable length field is physically 2000 bytes but is stored in 1001 bytes.

I’m sure Dear Reader you have noticed that instead of an even 1000 it is 1001, and I would love to tell you that I know why, but I don’t.  I suspect that the 1 byte is some sort of internal byte that contains the information needed for a Unicode Character to be translated by the access methods after compression.  But your guess would be as good as mine.

Thanks,

Brad