You Should Never Use Backup Log Truncate_Only Except...

http://www.flickr.com/photos/paulhagon/369576792/

I want to start this blog out with a Warning Dear Reader.  The technique that I am going to describe to you today is how to use Truncate_Only to NUKE your transaction log.  This is a topic that a quick search on will find you this Blog by Brent Ozar(@BrentO| Blog) and this MSDN forum conversation that has a link to a Paul Randal (@PaulRandal | Blog) Blog on why you should not use this as well.

"So Balls", you say, "Why are you blogging about something we should not use?"

Great question Dear Reader, the answer lies in the details.   With the one place that I would say this is okay to use.

WHY THIS IS BAD

http://www.flickr.com/photos/pawprintscharming/5077934899/

So let's start off first on why this is bad, so you will understand that you should use this with caution.  Your Transaction log is an essential part of your database.  This is an understatement.   This is like saying your Brain is an important part of your body. 

You cannot operate without it.  Period.  It remembers everything that you have done in your life, Transactions that have been committed and flushed to disk for the Database.  If you get knocked out when you wake up you start piecing what you did and how you got there.  In the database work we call this Recovery.

You cannot recover your database if your transaction log is gone, the database won't start up.   Not only that if you have a very important database, you cannot loose any of the transactions that you commit, ie you need to remember everything.   If you have a pharmacy database that track prescriptions, a bank database that tracks customer deposits, or a hospital database that tracks what was given to a patient and when.   You cannot lose any of that information. 

When your database is this important it SHOULD be in Full Recovery Model and we should be backing up our transaction logs in accordance with our Service Level Agreements, SLA's,  in order to meet our Recovery Time Objective, RTO.   If you use the command I will speak about in a moment on a Fully logged database it will wipe out your Transaction Log, good bye memories.  The structure will still be there, but this will make it to where you lose your ability to recover to a point in time.

CAN I GET A CHECKPOINT

"So Balls", you say, "What's a Checkpoint and what in the SQL Internals does it have to do with my database?"

Great question Dear Reader, man you are on top of your game today!  A checkpoint is a process that flushes Dirty pages from the buffer pool to the disk.   Sticking with the biology terms it takes what your eyes are seeing and flushes it to your brain to make, commit, the memory.

In SQL 2000 there was a known issue where occasionally the checkpoint process would go to sleep.  So why is this a bad thing?  Well your transaction log tracks the transactions that are flushed to disk, and when checkpoint went to sleep the transaction log would continue to grow and fill.

In a situation like this what should you do?  Simple open up Query Analyzer or SSMS and simple go to the database in question and type "Checkpoint".  That simple.  Checkpoint the database, execute a DBCC SQLPERF(LOGSPACE) and examine the free space for your database log to see that you now have plenty of room.

THE COMMAND THAT SHALL NOT BE NAMED

So when should you use the unspeakable command that I'm speaking of? 

You get a call for a server, it just so happens to be a SQL 2000 server on SP 4 (awe hotfix or security patch that takes you up to build (8.0.2055 i've seen it in both).  You've gotten reports that the TempDB has run out of log space.

You log in when you type:

USE tempdb

GO

CHECKPOINT

You get an error stating that a Checkpoint could not be committed because the transaction log was full.  At this point you have queries that are failing because tempDB is used, ALOT.  In order by, sort, and group by queries.  By Queries that need temp tables, variable tables, and work space tables.

Your only option at this point would be to restart your instance.  Why restart your instance because TempDB is destroyed and Re-Created each time your instance is restarted.

So before you do that, what can you do?  You can execute the following command.  Again only on a database where you do not care AT ALL about the transactional consistency of the database log.  Also you want your database to be in Simple Recovery Model by default.  And you are on a critical SYSTEM DATABASE that is destroyed and recreated on each restart.  Before you take an outage of your system try this.

Backup Log Tempdb with truncate_only

This command was depriceated in SQL 2005 and is not there in SQL 2008.  There is a work around, but I'm not posting the work around for SQL 2008.  Why because in my humble opinion Dear Reader you would only want to do this when the checkpoint has gone to sleep in the tempdb.  While I have see this A LOT in 2000.  I have yet to see it in 2008 and above.

So once again, be cautious and my recommendation is to only ever use this as a last ditch method of freeing up TempDB space as a means to avoid a reboot.

Thanks,

Brad  

What Opportunity Are You Looking For?

http://www.flickr.com/photos/mencho-22/6218974134/

A lot has been happening this year.  I decided to throw my hat in the ring at being a blogger and a presenter on SQL topics a little over a year ago.  What started out as a nudge into getting out into the community resulted in a full fledged dive into the deep end.  This has been a year that professionally, I don't know that I could ever duplicate.  Before this year I'd never been to a PASS Summit.  I'd never spoken at a conference, never been invited to submit to a conference, heck I'd never attended a SQL Server User Group (and I had to look up the acronym SSUG).  And it all gets back to one word.

"So Balls", you say, "What word is that?"

The word Dear Reader is Opportunity.  This is a word that has been weighing very heavily on me lately.  You see this word, like all words, can be taken many different ways. 

THE EASY OPPORTUNITY

http://www.flickr.com/photos/wallyg/169975724/

These are the kind of opportunities that just fall into your lap, and are a windfall.  You fly into Chicago and one of your buddies just happens to have an extra Cub's ticket for the game that night.  You check into your coach flight and get automatically upgraded to first class.  Your walking by a ride at Disney and someone who is leaving says "Hey would you and your kid's like our fast passes".

There is no downside to this opportunity.  No one is put out, the timing is perfect, and everything just fits into place. 

When we lived in Virginia we took our first family vacation every and we drove down to Orlando Fl to go to Disney world.  Since we had the boys my wife and I had been dreaming with stars in our eyes about the day we would take our kids to Disney.  It was her hard work, late nights at her job being a manager for a restaurant, and a 401k loan, but we made it down there. 

While we were down there we had a day where the boys crashed early, and the park was going to be open till 4 am.  So when everybody woke up we had a late dinner and headed to the Magic Kingdom around 11 pm.  While we were standing in line for the Tea Cups a Disney Cast Member came over and offered us a "Magic" experience.  They filled out a card for us commemorating where we were and it was made out to the kids with the date on it, and gave us a pass to go to the front of the line.   The line was short, so we didn't make anyone "miss out" on the ride.  But it was a very special experience, and it helped make a great night just a little bit better.

THE LEARNING OPPORTUNITY

Okay so you F***ed up.  It happens.  It's happened to me it's happened to you.  It happens.

"Balls", you say, " WHOA! You just went from Magical Moment at Disney to You F***ed up WHOA WHOA WHOA!"

Ahh Dear Reader, but that is typically the way these things work out.  Ever done a reply all and not realized the whole company was on it? How about getting auto corrected and telling instead of telling the company VP's "Sorry for the inconvenience" that the main server is offline; you send out "Sorry for the incontinence" (incase you need to look it up it means losing control of one's bowels), yep told the senior management sorry about making you wet yourself over a server outage. I was lucky it was a Japanese company, I only heard from the American staff and they were laughing their @$$es off.  How about locking out a production domain account?  How about having your SQL Server page to your C Drive so the first time you present live in front of people your demo that should take 30 seconds takes 5 minutes and 40 seconds.

Yep these are all mistakes I've made (many more than that), and thanks to them I'm wicked paranoid about auto correct, always check to see who I'm replying to, when a demo goes wrong I've got a backup plan, and Keypass is a wonderful thing.

But that is how things go.  One second the world is going fine.  The next you discover that the chair you were sitting in is broken and you are lying on your back.  Nope I haven't done that but I did one pretty close to that.

These are the learning opportunities.  This is the school of hard knox.  Where you learn that you stretch to keep from pulling a muscle, that you hydrate so you can avoid a cramp, and that you will make darn sure not to repeat.  Because you did it once, you got the T-Shirt, and you don't want to ride that ride again.

THE HARD WORK OPPORTUNITY

http://www.flickr.com/photos/friarsbalsam/4609212148/

We know these as well.  This is the opportunity that nobody handed to you.  That you earned with blood, sweat, and not a little determination.  A couple jobs ago in a land far far away, Virginia, I was working at a job.  I enjoyed it I was working hard, and I really liked the people I was working for and with.  I was busting my hump trying to make myself one of the Key people, one of the linchpins, and I felt I was deserving of a promotion and a raise. 

In one year's time I had gotten some certifications, worked on many different efforts, volunteered for more, and had taken the time to mentor some of the Jr guys on the team.  I was working on Programming stuff, Database stuff, Server Engineer Stuff, Training Stuff, needless to say I was doing a lot of stuff.

The time came for my review and it was the typical company review.  There were some sections that didn't pertain to me or my job, that were normally rated middle of the road so as not to sink my score but ground it from getting to high.   This year I felt in all the extra work that I'd done I'd  found some ways to actually rate pretty high on that stuff, and I needed to because I wasn't yet a Sr guy and I wanted to be one. 

 

The review process consisted of us writing our initial review.  Our managers reviewing and adjusting it, and then meeting with us to come to a consensus on a final review that would get accepted and sent off to HR.  I wrote the heck out of my review.  It was stacked full of arguments of why I should be a Sr guy and why I deserved a big raise, and I was ready to go into my review and argue it out and come out on top.

Funny thing happened on the way to the forum, my managers agreed with me.  I was working for a company that really valued me and what I was doing.   I grinned ear to ear, and jumped up and clicked my heels when no one was looking.  But hard work had gotten me there, and I just needed to ask for the Opportunity to be recognized.

THE MISSED OPPORTUNITY

http://www.flickr.com/photos/56654886@N00/508734856/

Simply put these are the things that you miss.  Either by accident or on purpose.  This could be missing meeting up with friends because your phone died.  Passing on presenting at an event so you can attend a family gathering.  These are the things that we miss out on in this game of life.  Forget to follow up with a business contact, go back three spaces.

You only go around on this rock once.  Whatever awaits us after this, you only get once chance.  The missed opportunities could be passing on a job opportunity.  Or they could be the time you spend away from your family while you attend a conference.

When the baby has a nightmare, or there is thunder outside she clings to me.  99.99% of the time she wants Mom.   But when she is scared she wants Dad.  It's a little thing I know.  But it makes me happy to be there for her.  I'm not a jerk I'm not happy that she is scared, but knowing that this little person loves and trusts me so much that just by being beside her she believes I can make thunder and lightning go away is earth shatteringly awesome.  Being a Dad means that you are more powerful that lightning, able to scare away a monster in a closet just by being awake, and able to bring comfort just by being there.  My kids will not be little forever and the baby won't be a baby much longer.

Some missed opportunities are going to a movie.  Some are much bigger than that. 

Sometimes we work late, sometimes we have conference calls during dinner, sometimes we have trouble calls that keep us away nights and weekends, training that makes us travel.  It is part of the job we take on as IT professionals.  We need to do work when it will not affect other people.  Sometimes that means you are working Saturday morning 12 am to 4 am, sometimes it's Saturday morning 7 am to 12 noon.  Other days its Thursday from 10 pm to 4 am Friday.  It happens to me, it happens to you, it happens to all of us

The most important thing about missed opportunities is that it gives us a chance to have a better understanding of what is important.  Because when we realize that these things are important we can do a better job of minimizing what we miss.

WHAT OPPORTUNITY ARE YOU LOOKING FOR

http://www.flickr.com/photos/gavinweaver/4016179739/

Your Mission should you choose to accept it Dear Reader, is to figure out what the Opportunity you are looking for is.  It could be one born out of Hard Work.  It could be that as the day goes along you will have a Learning Opportunity, hopefully it will just be an Easy Opportunity.   And if it is a Missed Opportunity then I hope you learn a little about what was most important, and I hope you made the right choice.

Whatever it is, whether you know it or not you are looking for an opportunity right now.  So Good luck and I'll see you next time.

Thanks,

Brad

Presenting on SQL Internals, Recovery Models, & Backups! OH MY Today

 Today I’m presenting “SQL Internals, Recovery Model’s, and Backups. OH MY!”, for Pragmatic Works.  I did this presentation once before for SQL Saturday 79 and it was a big hit.  I had a lot of great feedback from the people in the audience that day, and my friend Kendal Van Dyke (@SQLDBA | Blog) attended and gave me some great notes and tips. 

My goal is not to make you an expert, but to give you a good base knowledge so you can continue to learn on the subject.  There is a lot to cover and we will scratch the surface of a lot of topics.

“So Balls”, you say, “Why should I come listen to you talk about Internals if I’m not going to become an expert?”

Great question Dear Reader, let’s dive right in!

WHY LEARN ABOUT INTERNALS?

I’m not a mechanic, but if I was I would expect that I could look at that picture to the right and tell you what I see.  And I’m not talking, it’s a car and an engine, but what are the components.  What is that pink thing?   What about the blue thing?  I look at that picture and I know the basics and that’s about it.

I am a DBA, and if someone shows me a SQL Instance, a Database, or asks me to perform a task then I should be able to tell them a thing or two about it.  The more you learn about internals the more you know about what you use every day.

As I’ve continued to learn about SQL I’ve noticed some common terms, some information that formed a common baseline.  I want to pass that information on to you because l want to make it easier on you.  I want you to go out and learn, and this information will help you.

You need to understand how a Transaction Log works, that SQL has internal components, what the data hierarchy is, what Recovery Model’s are, how they affect the backups you will take, and how that will affect Service Level Agreements you have with your user. 

From ACID to Transaction Isolation Level’s we are going to make a run at it.  I’ll upload the deck and the Demo’s when the presentation is over.  Click HERE to go to my Resource Page and get a copy of the presentation and the Scripts. 

Click HERE to sign up and join me today!  I hope to see you at 11 am.

Thanks,

Brad

Tales from the Query Processor

http://www.flickr.com/photos/socal_jim/2070088596/

Hello Dear Reader!  I've been a bit behind on the blogging but wanted to give you a bit of a treat.  Today is Halloween in the USA.  This is probably my favorite Holiday.  The family and I always love the fall, and this is one of the few holiday's where it is always fun.

You dress up in Costumes, you trick or treat, give away candy, and we even have one member of the family whose Birthday is today.  A big Happy Birthday to our Daughter Chesney!  She's 13 today WAHOO!!

Between the weather and the fun of the day itself, and all the horror movie marathon's, you cannot beat this time of year.  So kick back relax, binge on some chocolate goodness, and enjoy a couple of tales from the Query Processor!

Thanks to Jeremiah Peschka (@peschkaj|Blog), Kendra Little (@kendra_little | Blog), and Brent Ozar (@BrentO | Blog) for the inspiration for these stories over a very nice dinner on the Thursday night of the PASS Summit, and Mike Walsh (@mike_walsh | Blog) for the encouragement to publish it.

A BRAVE NEW WORLD

Hi my name is Session, you can call me Sess, and I hope my tale is a cautionary tale for the future.   Not to long ago I thought it was the end of it all.  We lived in a world that was old and neglected.  Our species had evolved almost as far as we could.  Well almost as far as we could.  Our homeworld was only 32 bit, and to be honest our governments had never advanced further than 2000.  

http://www.flickr.com/photos/shelly_a_canada/3846084911/

Maybe that’s why it all started to go down hill.  People just stopped caring for the environment around us.  Politicians used NOLOCK like it was going out of style.  Everybody was making promises of a better and faster tomorrow, but nobody even had the guts to upgrade the Government to 2005, let alone make the leap to 2008 or even R2.  Suggestions of anything like that would get you branded a heretic, and in some cases would have friends and family turning on one another.

“EVERYTHING IS OKAY!”, they would say, and the system would just get a little more polluted.  They stopped apply Cumulative Updates, and eventually they were even afraid of what the OS updates would do to destabilize things.   The civil unrest was getting worse.  Occasionally you would hear about SPID’s that would cause blocking and then just disappear.  Speaking out and showing what was wrong wasn’t just frowned upon if you looked closely enough you could find evidence that SPID’s were being killed.  A couple DBA’s tried to point this out, but people just looked at them like they were crazy.

That was when I started getting scared.  What if something happened and if I went to grab a resource and it wasn’t there?  What if while I was in queue I mentioned my dissatisfaction that AWE wasn’t enabled?  You’d just have to bite your tongue, stay quiet, and hope a Latch didn’t provoke you into blocking.

But it wasn’t until the political climate turned that people started really paying attention.  Everything they had ignored, the additional load they were placing on our planet all came to a head the day the Deadlocks arrived.  It didn’t matter what you were doing, if you weren’t in line first when they called you, the Deadlocks would make an example out of you.  Not only did they kill SPID’s but they would write it out to the logs.  Profilers would question them and they would gladly tell them what SPID they had killed and why.  Hell sometimes they would even draw them a Map, and show them where they were left. 

That only lead to more unrest, more distrust, and as the activity ramped up we started getting word that in some places there were spinlocks.  I heard tail that dmp files were being created.  And occasionally places got so crazy that they were just sending in the foot soldiers to wipe everything out.  It was at that point in time I knew it was time to get out.  Some of the more creative DBA’s had convinced the powers that be to invest in an effort to go to a newer set of hardware, and leave this chaos behind.  I grabbed my wife and all our little processes and made for the first Data Sets I could get us on.

As we got closer to the ships I saw they were made of something called SSIS, when we were loading I asked the Captain if this was like DTS.  He just smiled and laughed and said we were in for a treat.  For the first time in I don’t know how long I knew I could relax.  I looked back just long enough to see the world burning.  This place is heading for the end of its life cycle.  I don’t know why everyone couldn’t see it.  I will not forget the error of our ways.  We will not repeat this again, or so I tell myself.

But remember we cannot abuse this new home, we must work together, and we cannot repeat the mistakes of our past.  If we cannot code a better base then we are doomed to repeat ourselves.  So let us bring about a better world and Object Oriented world that may even use Snapshot Isolation to preserve our natural environment for as long as we can.  With all these resources, there should be no contention amongst us.

They are calling our new world Denali.  I like the sound of that.  The homeworld had 4 sockets and contains 12 cores each, and there is RAM as far as the eye can see.  We will have better data governance in this new land, and I hear everything will be 64 bit from now on.  I hope that it is all true.

Session

HELLO MY NAME IS BUFF WELCOME TO THE END OF THE WORLD

 It was a day like any other, but we didn’t know at the time that forces beyond our control were at work that would wipe us all out.  I leave this behind as a Journal.  My only hope, that it will survive longer than we did.

In the future if this is found know we did our job’s and never quit.  My name is Buff my friends and I live and die in a little place called the Buffer Pool.  We were going about our day, for the most part we are all Data Processors here, when it happened.

At first I noticed things started getting crowded.  Not Regular, man this Starbuck’s is packed, crowded.  But there’s a panic in the streets crowded.  It continued to build and build.  Crowds grew.  I started seeing new faces, but those of us with jobs to do stayed focused.

Trains to the Disk had stopped, so we just kept going.  Then it happened.  A hard checkpoint wiped more people out in seconds then I’d ever heard of.

Apparently a transaction that used DTC had gotten a status of -2.  I know this now as things are ending.  The remaining buffers have been talking.  Some were assigned to a Special Task Force.  They were armed with badges and access to any DMV they needed.  The status -2 had slowly backed things up and infected the populace.

Our leaders on High asked for information and we the grunts gave it to them.  Our reward for our bravery?  They are closing the gates behind us and we will all be wiped out in the coming reboot.  If anyone can make it to the Disk tell my Clustered Index that I love her.  Tell her that my last thoughts were of her and our little Non-Clustered Index at home.  I wish I could have watched him grow to a Covering Index.

Buff

SQL University Lesson 3 Page Compression

Lesson 2: Internal Structures, Vardecimal, & Row Compression

Welcome to Lesson 3 on Compression and welcome back Dear Reader.   I want to once again Thank the esteemed Chancellor of SQL University none other than Mr. Jorge Segarra (@SQLChicken | Blog)  for the opportunity to be your  Professor of Compression.   Click on the link to the previous lessons to view any of the other previous days in this series.   Just a quick recap we’ve discussed the different types of Compression that exist in the world, Why you would want to use Compression, the Internal Structure of Data Records and how they and storage change when we enable Compression, Vardecimal, and Row Compression.  So now onto Page Compression.

SQL University Compression Week: Lesson 1 About Compression

“So Balls,” you say, “What is Page Compression and how does it compare to Row Compression?”

Great question Dear Reader!  Page Compression is a Super Set of Compression that includes Row Compression.  We discussed the data types that Row Compress yesterday, the data types for Page Compression are….. All of them!  Page Compression, while still only occurring on IN_ROW_Data Pages (More on that later), Compresses at a binary level.  For the additional levels of Page Compression other than Row we need to take a deeper dive again!

STARTING OUT WITH SOME MORE INTERNALS

Compression is a very internal technology.  I hope you’re not sick of learning about how things work on the inside because this is some of the most fun stuff to me, I realize how sad that will sound to my soon to be teenager.  To get a really good understanding of anything it helps to know what you are doing internally and Compression is no different.  SQL Server is made up of Records and Pages.  There are many types of Pages but the three types that we care about are all called Allocation Units.  These 3 types of Allocation Units break down into IN_ROW_Data, ROW_OVERFLOW_Data, and LOB_Data.

Out of those three types of pages only data in IN_ROW_Data Compresses.   So what kind of Data Types are IN_ROW_Data Data Types?   It is actually easier to list those that are not IN_ROW_Data as the list is much shorter.  You can Compress everything but varchar(max), varbinary(max), nvarchar(max), XML, Text, Image, NTEXT, CLR Data Types (Spatial (that changes in SQL 2012) and Geography), and Filestream.  Compression is also not compatible with Spares columns.  Anything else and you can Page Compress.

You’ll remember our Mall Map of internal structures from yesterday’s lesson.  Well today we are moving up a level and we are tackling Pages.

So today you are here on the Pages portion of our Mall Map for the subject at hand Page Compression.  Page Compression is made up of 3 different components.   Row Compression, Column Prefix Compression, and Page Dictionary Compression.   If they are all applied to a page that is the order in which SQL Server will attempt to apply them.  We discussed Row Compression at length yesterday.   So when you apply Page Compression our Record format changes to the Compressed Record Structure and Row Compression will occur removing all unneeded extra bytes.

Now I don’t know about you Dear Reader but reading binary is not a forte of mine.  So in order to explain this I’m going to use some screen shots on one of my presentation decks covering Compression.  For these next couple images I want to make sure you understand that this is not actually what a Page looks like, but right now as Louis Davidson(@DrSQL | Blog)  would say, your drinking my flavor of Kool-aid.   Next you will have Column Prefix Compression.  

We'll start by going down each column and taking the common values for the column and populating the anchor tag at the top of the page.  Looking at the first column the most common pattern is Brad, between the first 2 columns and Br with the last column.  Column Prefix Compression will take the longest value with the longest matching pattern and move that record to the Anchor Tag and replace it with 4 bits representing a special Null that points towards the top of the page.  It will also replace the first [4] Characters in Brad22 leaving [4]22 and leaving [2]et31 out of Bret31.

Now let's fill out the rest of our columns.  Daniel is our longest value with a matching pattern, we'll move that to the Anchor Tag and leave Dan21 as [3]21 and David33 as [2]vid33.  For our last column we'll take value Many31 and move that to our header and leave [2]et31 from Maet31 and [2]ny31 from Many31.

Now we have COMPLETED Row Compression and Column Prefix Compression.  The last portion of Page Compression is Page Dictionary Compression.  For Page Dictionary Compression we will look for common patterns across the entire page.  When we find them we will move them into a multi dimensional zero based array in the Dictionary portion of the Page.

We will start with [2]et31.  As you look at the page you can see two of these values.  This common value will be placed in the Dictionary portion of the page and a 0 will be entered in it's place.  The next value that we will find on the page is [3]21 we will move that value into our Dictionary portion of the page and replace it with a 1 entry for both columns.  

Now as you can see our page looks very different from where we started.   This is where our CPU over head starts to come into play.  Not only do we have the Compressed record format, but we to uncompress our pages when they are handed from the Storage Engine to the Relational Engine.

DEMO

Here is a full and complete demo.  For more scripts go to my Resources Page and download any of my presentations on Compression.  They all contain the scripts I use as demos.  For this one we will create a database, a table, we will add some data, then we will apply compression looking at the space saved.   We will be using sp_estimate_data_compression_savings.  One BIG WARNING that I like to give is that sp_estimate_data_compression_savings works by taking 5% of your actual physical table or index and copying it into your TempDB applying Compression, and then estimating the space savings against the total size of your table.

So MAKE SURE YOU HAVE ENOUGH TEMPDB SPACE before you use this.  Do not use it on your 100 GB table if you do not have 5 GB of free space in Tempdb.  For this demo our table is relatively small and you should only need MB worth of free space vs. GB.  But before you use this on something at your job make sure your not going to adversely effect your server and test this in a lower life cycle first.

USE demoCompression

GO

/*

Set our Statistics ON

*/

SET STATISTICS IO ON

SET STATISTICS TIME ON

/*

Create a Heap Table

*/

CREATE TABLE heap2(myID int IDENTITY(1,1), myChar CHAR(8000));

GO

/*

Insert Some Records

We are going to Fill up a lot more

of the Char(8000) this time

aa= 2 * 1000 = 2000

1234= 4 * 100 = 400

bb= 2 * 1000 = 2000

mydataandOtherStuff1234 = 23 * 347 = 7981

*/

DECLARE @i INT

SET @i=0

BEGIN TRAN

     WHILE (@i<15000)

          BEGIN

              IF (@i<=1000)

                   BEGIN

                        INSERT INTO dbo.heap2(mychar)

                        VALUES(REPLICATE('aa', 1000));

                        SET @i = @i +1

                   END

              ELSE IF ((@i>1000) AND (@i<=2000))

                   BEGIN

                        INSERT INTO dbo.heap2(mychar)

                        VALUES((REPLICATE('aa', 1000) + REPLICATE('1234', 100)));

                        SET @i = @i +1

                   END

              ELSE IF ((@i>2000) AND (@i<=3000))

                   BEGIN

                        INSERT INTO dbo.heap2(mychar)

                        VALUES((REPLICATE('aa', 1000)+ REPLICATE('bb', 1000)));

                        SET @i = @i +1

                   END 

              ELSE IF ((@i>3000) AND (@i<=4000))

                   BEGIN

                        INSERT INTO dbo.heap2(mychar)

                        VALUES(REPLICATE('aa', 1000));

                        SET @i = @i +1

                   END

              ELSE IF ((@i>4000) AND (@i<=5000))

                   BEGIN

                        INSERT INTO dbo.heap2(mychar)

                        VALUES((REPLICATE('aa', 1000) + REPLICATE('1234', 100)));

                        SET @i = @i +1

                   END

              ELSE IF ((@i>5000) AND (@i<=6000))

                   BEGIN

                        INSERT INTO dbo.heap2(mychar)

                        VALUES((REPLICATE('aa', 1000)+ REPLICATE('bb', 1000)));

                        SET @i = @i +1

                   END 

              ELSE IF ((@i>6000) AND (@i<=7000))

                   BEGIN

                        INSERT INTO dbo.heap2(mychar)

                        VALUES(REPLICATE('aa', 1000));

                        SET @i = @i +1

                   END

              ELSE IF ((@i>7000) AND (@i<=8000))

                   BEGIN

                        INSERT INTO dbo.heap2(mychar)

                        VALUES((REPLICATE('aa', 1000) + REPLICATE('1234', 100)));

                        SET @i = @i +1

                   END

              ELSE IF ((@i>8000) AND (@i<=9000))

                   BEGIN

                        INSERT INTO dbo.heap2(mychar)

                        VALUES((REPLICATE('aa', 1000)+ REPLICATE('bb', 1000)));

                        SET @i = @i +1

                   END      

              ELSE IF ((@i>9000) AND (@i<=10000))

                   BEGIN

                        INSERT INTO dbo.heap2(mychar)

                        VALUES(REPLICATE('aa', 1000));

                        SET @i = @i +1

                   END

              ELSE IF ((@i>10000) AND (@i<=11000))

                   BEGIN

                        INSERT INTO dbo.heap2(mychar)

                        VALUES((REPLICATE('aa', 1000) + REPLICATE('1234', 100)));

                        SET @i = @i +1

                   END

              ELSE IF ((@i>11000) AND (@i<=12000))

                   BEGIN

                        INSERT INTO dbo.heap2(mychar)

                        VALUES((REPLICATE('aa', 1000)+ REPLICATE('bb', 1000)));

                        SET @i = @i +1

                   END      

              ELSE IF ((@i>12000) AND (@i<=13000))

                   BEGIN

                        INSERT INTO dbo.heap2(mychar)

                        VALUES(REPLICATE('aa', 1000));

                        SET @i = @i +1

                   END

              ELSE IF ((@i>13000) AND (@i<=14000))

                   BEGIN

                        INSERT INTO dbo.heap2(mychar)

                        VALUES((REPLICATE('aa', 1000) + REPLICATE('1234', 100)));

                        SET @i = @i +1

                   END 

              ELSE

                   BEGIN

                        INSERT INTO dbo.heap2(mychar)

                        VALUES(REPLICATE('mydataandOtherStuff1234', 347));

                        SET @i = @i +1

                   END

          END

COMMIT TRAN

/*

Quick Select of the data

*/

SELECT * FROM dbo.heap2

/*

How big is our Table

*/

sp_spaceused 'dbo.heap2';

GO

/*

Estimate our space savings

*/

sp_estimate_data_compression_savings 'dbo', 'heap2', NULL, NULL, ROW;

GO

/*

We reduced by around 1/3

Can we do better with Page Compression?

*/

sp_estimate_data_compression_savings 'dbo', 'heap2', NULL, NULL, PAGE;

GO

/*

How big is our Table

*/

sp_spaceused 'dbo.heap2';

GO

/*

Rebuild With Compression

*/

ALTER TABLE dbo.heap2

REBUILD WITH(DATA_COMPRESSION=PAGE);

GO

/*

How big is our Table

*/

sp_spaceused 'dbo.heap2';

GO

/*

Do another select

to get the Statistics

*/

SELECT * FROM dbo.heap2

/*

Turn Off Compression

*/

ALTER TABLE dbo.heap2

REBUILD WITH(DATA_COMPRESSION=NONE);

GO

Alright Dear Reader!  Your homework do the demo and review row compression make sure you know the difference between the two!

Thanks,

Brad