Introducing What_To_Compress V2

Hello Dear Reader!  I'm about 32,000 feet in the air leaving St. Louis after a great Performance Tuning Workshop for Pragmatic Works.  While there Jason Strate (@StrateSQL | Blog) and I had a two day class, and I was able meet some great people and speak with the St Louis SSUG about the new features in SQL Server 2014.  It was a good trip, but I'm happy to be on the way home.

"So Balls", you say, "The blog title is Introducing What_To_Compress, what is that?"

Great question Dear Reader!  For quite a few years I've been presenting, blogging, and writing about Compression.  On the Resource Page I have a list of those presentations as well as the scripts I use.  I'd been thinking about putting together a script to give compression recommendations since I first did my deep dive at the PASS Summit on the subject back in 2011.

About a year ago I did just that.  I've tossed this script around to some co-workers, friends, SQL People, and MVP's and asked for feedback.  I'm finally at the point that I'm ready to release the first version.  So without further ado, here it is.

WHAT_TO_COMPRESS V2

First off this is not a stored procedure like Jason's sp_IndexAnalysis script.  I'll probably make it dynamic in the future, but that is a next release.  If you want to jump straight to the download click here to get What_To_CompressV2.sql. 

This takes the Best Practices that I've been preaching about and applies it to telling you what to compress.  It looks at every Index, every Table, by partition, and gathers the In_Row_Data, Row_OverFlow_Data, and Lob_Data counts.  It tells you the Percentage of COMPRESSIBLE and UNCOMPRESSIBLE data per table, what the Scan and Update patterns are for your tables & Indexes, and makes a recommendation on the level of compression you should use.

It also gives you my detailed reasoning behind the recommendation that I've given you.  For example:

"The Percentage of Scan and Seek operations is 0.00% and the average amount of Update operations is 0.00%.  Data that can be compressed makes up 100.00% of this table.  There is no workload for the current table.  Please wait for the usage statistics to become representative of a typical work load.  If this is a typical work load, this is an excellent candidate for Page Compression.  Test with sp_estimate_data_compression_savings.  Remember that it takes 5% of the tables size and moves it to tempDB.  Validate that you have enough room on your server to perform this operation before attempting."

"The Percentage of Scan and Seek operations is 0.60% and the average amount of Update operations is 99.00%.  Data that can be compressed makes up 100.00% of this table.  However Based on the workload of this server this table should not be compressed.  If you apply Row or Page Compression it will have a higher CPU cost because of the low Seek and Scan Ratio.  Test with sp_estimate_data_compression_savings.  Remember that it takes 5% of the tables size and moves it to tempDB.  Validate that you have enough room on your server to perform this operation before attempting."

"The amount of Uncompressible data in this table does not make it a match for compression.  Data that can be compressed makes up 17.12% of this table.  While data that cannot be compressed makes up 82.88% of this table."

There is one parameter within the script that allows you to set the number of Pages in a table that you want to consider for compression.  By default the number is set at 8 pages, but you can increase that if you would like.

"So Balls", you say, "This sounds great but isn't their a built in stored procedure that can estimate size compression already in SQL Server?"

Yes, there is Dear Reader.  The built in stored procedure has a few things that we should discuss.

SP_ESTIMATE_DATA_COMPRESSION_SAVINGS

The first thing you should know before you use a tool is how it works, and what it does.  You wouldn't normally use a nail gun to open a beer.  You could, but it's not the right tool for the job.

The way sp_estimate_data_compression_savings works is that it takes the table that you specify, moves 5% of it into tempdb applies the compression you specify, and then extrapolates that estimate out over the size of your entire table.  It does a nice job of taking fragmentation into account in order not to give you an inaccurate information.  The key phrase that defines my root concern is, *it takes 5% of your table and moves into tempdb*.  For small tables this probably isn't an issue.  For VLDBs that have very large tables, this is a pretty big deal.

There are some well meaning community scripts available on blogs and codeplex that take sp_estimate_data_compression_savings and wrap it in a cursor to estimate the space savings for each table.  They do this estimation for Row and Page compression, for every table in your database.

This step tells us the space savings, but their are other settings we should take into account.  We should look at those before we begin estimating compression savings across the board.  What should we look at first?

1. Our Allocation Units.  Only IN_ROW_DATA compresses.  Tables with a lot of LOB data types may not see any advantage in compression.  Even if they slightly compress the over head on those tables can make queries less efficient.
2. Do we read from our tables?  If we do a lot of scans, seeks, and lookups from our tables this could indicate whether Page or Row compression would give us the best performance.
3. Do we update our tables often?  Notice I said update.  Not delete, not insert, update.  When we apply compression we remove all the extra white space from fixed length data making all data types, that can use compression, in affect variable length fields.  This can lead to increased Page Splits, specifically mid-Page Splits, aka LOP_DELETE_SPLITs.  For more on Page Splits and mid-Page Splits see my blog, How to Find Bad Page Splits.
4. SP_ESTIMATE_DATA_COMPRESSION_SAVINGS doesn't look at any of these.  Why estimate all the different compression types without first identifying your table that are proper candidates and looking at what the overall size of those tables are.
5. You have to have Enterprise Edition to run sp_estimate_data_compression_savings.  You can run What_To_Compress on Standard Edition.

I wouldn't avoid using sp_estimate_data_compression_savings.  However, it wouldn't be the first thing that I run when looking at what to compress.

FEEDBACK

Okay Dear Reader, I need your help.  Run this.  Let me know what you think, what it's missing, anything you can think of.  I'll try to get a v3 out in the next couple months based on feedback.  Most of all if it works, drop me a line!  I love hearing success stories on compression.  Send your emails to: bball@pragmaticworks.com.  And as always Dear Reader, Thank you for stopping by.


Thanks,

Brad

Can You Compress a Temp Table?

 Hello Dear Reader!  We are finishing up the final day of the Performance Tuning Workshop here in Atlanta and I got an interesting question on Compression from Tim Radney (@tradney | Blog).

The question: Can you compress a temp table? Just a quick blog to get the answer out there while Gareth Swanepoel (@GarethSwan | Blog)  teaches the class about Extended Events. 

My guess was yes.  Temp Tables can have statistics, Clustered and Non-Clustered Indexes, while they only exist in the session they are created, I would be they could be compressed.  If you would actually want to compress them is a different discussion, but let’s prove this out.

DEMO

Here’s a quick demo to show you can do this.  So first up we will create our Temp Table specifying with Data_Compression=ROW.

This will create our temp table #myTable1, we will then insert 15000.

if exists(select name from tempdb.sys.tables where name like '#myTable1%')

begin

     drop table #mytable1

end

go

create table #myTable1(

              myid int identity(1,1) primary key clustered

              ,mychar1 char(500) default 'a'

              ,mychar2 char(3000) default 'b'

              ) with (data_compression=row)

go

declare @i int

set @i=0

while(@i<5000)

begin

     set @i=@i+1

     insert into #myTable1

     default values

end

Now let’s use DBCC IND to view the pages associated with our table, and DBCC Page to Validate that our data is compressed.

dbcc ind(tempdb, '#myTable1', 1)

go

dbcc traceon(3604)

go

dbcc page('tempdb', 1, 376,3)

go

Looking at the output of DBCC Page I can see that the CD array for my compressed data is present near the header.  Row compression is indeed on.

Now let’s rebuild this using page compression on a rebuild operation using sp_spaceused to measure the size of the table.

And it is now Page Compressed.  Thanks for the question Tim!  And as always Dear Reader Thank you for stopping by.

Thanks,

Brad

SQL Live 360 Kicks off Today!

Hello Dear Reader!  Today in beautiful Orlando FL at the Loews Royal Pacific Resort the Live 360 Conference kicks off.  Live 360 combines 4 different conferences into one.  Visual Studio, SharePoint, SQL Server, and Cloud & Virtualization all have their own conferences.  I’m helping to kick off the SQL Live 360 by presenting not once, not twice, but three times today!  

Before we dive into the content I want to say a big Thank You to my wife Silva.  Every time I’m presenting at a conference she’s taking off work to be at home and manage the kiddos.  Without her none of this is possible for me.  Thanks Silva!

“So Balls”, you say, “What are you presenting on?”

Excellent question Dear Reader.  We will be kicking off the day with Trimming Indexes Getting Your Database in Shape, next up is Transparent Data Encryption Inside and Out in SQL Server 2012, and we end the day with the Page & Row Compression Deep Dive in SQL Server 2012.  Conferences are expensive and I want to make sure that you get the most out of your experience.  So to help you decide if you should be spending your time with me, I’m placing the decks and demos online now.  They are also live on the Resource Page.  Attendees should have a copy of all of this information on their conference CD, but just in case you didn’t find it here we go!

KICKING IT OFF WITH A BANG

If you’re in town for the conference I hope you get a chance to stop by.  Here are the abstracts for each presentation:

Trimming Indexes Getting Your Database In Shape

Indexes are a wonderful thing.  We should be using them, and we should be maintaining them.  But over time our production databases start to look a little pudgy around the mid-section.  Maybe they are a little bloated with Unused Indexes, maybe they have Duplicate Indexes, and possibly even Reverse Indexes.  The first step to fixing these problems it so see if you have them, and if you do the second is to set about fixing them. You could be costing yourself CPU cycles, I/Op’s, and space and never even know it.

Slide Deck, Demos

Transparent Data Encryption Inside and Out in SQL 2012

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 and What it doesn’t do, How it effects Read-Only Filegroups, Performance, Compression (Backup and Row/Page), What the X.509 Encryption Standard is and Why you should be careful of what you store and where, and other Advance Features as well as some tips on how to manage it.

Slide Deck, Demos

Page & Row Compression Deep Dive with SQL Server 2012

Page and Row Compression are powerful new tools. Page & Row with SQL 2008 RTM, and Page & Row with Unicode Compression with SQL 2008 R2, and Spatial Types in SQL 2012.  We can turn it on, we can turn it off, but we want more!  What are Access Methods and how in the SQL Engine do they affect Compression?  What are the “Gotchas” of Page Compression?  How does Compression differ in the way it treats Non-Leaf & Leaf Level pages?  What additional functionality did we get in DBCC Page, DMV’s, Perfmon Counters, and Extended Events to support our usage of Compression?  How do complex query plans affect Compression?  Come find out!

Slide Deck, Demos

LUNCH!

That’s not all, on top of having 3 great sessions to choose from if you’re still not sick of me we’ve got a speaker round table on Wednesday where I’ll be hosting a table on SQL Server 2012 and new features.  While I may not have presented on them there are a lot of great 2012 topics like Columnstore Indexes, Always On Availability Groups, Always On Clustering Improvements, and more that we could chat about.

Great SQL People like MVP’s Jen Stirrup(@JenStirrup | blog), Grant Fritchey (@GFritchey | blog), Allen White (@SQLRunr | Blog),  Thomas Larock (@SQLRockstar | Blog), and William Pearson (@Bill_Pearson) will be there hosting different tables as well!

WRAP IT UP

Okay with that being said, it should be a fun filled week of SQL Learning.  There will be some wonderful night time activities as part of the conference as well. 

  I won’t make it to all of them, Wednesday night is the baby’s Christmas play at her day care can’t miss that!  For the rest of the week though I look forward to getting to see you all.

If you are in town I hope you get to stop by and say “Hi BALLS!”, and have a great time!

Thanks,

Brad

See you at SQL Saturday 130 Jacksonville!

http://www.flickr.com/photos/rohicks/4788477593/

Hello Dear Reader, this Saturday April 28^th is SQL Saturday 130 in Jacksonville FL!  The good people from the Jacksonville SQL Server User Group have put together an excellent event.  If you are in the Jacksonville area, (or in FL or GA for that matter) and would like to make it to the event, go register here because we would love to see you!

On Friday the festivities kick off with a great Pre-Con Kevin Kline (Blog|@KEKline)  a full day on Troubleshooting & Performance Tuning for Microsoft SQL Server 2008 R2.  Kevin is one of the top guys in the SQL Server world, he is a Founding member of PASS and a former PASS President, he’s a SQL MVP, an author, and an all around great guy.  This is a full day of training that normally goes for hundreds of dollars, and right now it is only $120 for the full day.  Click on the link above to get more details.

“So Balls,” you say, “What will you be doing at SQL Saturday 130?”

We will get to that in a moment Dear Reader but first let’s go to the line up.

THE USUAL SUSPECTS   

When you go to a SQL Saturday you expect a great line up, and SQL Saturday 130 in Jacksonville is no exception.  Six different SQL Server MVP’s, Six different tracks, two SSAS Maestro’s, all in all over 36 Sessions of pure SQL fun.   As if this wasn’t enough it is free.  That is right Dear Reader, if you have not been to a SQL Saturday in the past you may not know this but they are completely free to attend.  For the full schedule click here.

“So Balls”, you say, “What will you be attending?”

With all of these great speakers it will be tough to choose where to go, but I’m going to do my best!

If you’re new to the blog you may not know this, but I LOVE Compression! 

 One of the most interesting stories on Compression in SQL 2012, in my humble opinion, is ColumnStore Indexes.  So I’m going to start out the day with my friend Gareth Swanepoel (Blog|@GarethSwan) to learn more about them as he presents The Fangorn Forest – ColumnStore Indexes.  Then I’m off to present on Page & Row Compression in SQL 2012 (FYI 99.99% of what I cover works in 2008 R2 as well).  Next I’m off to watch Tom Brenneman (@TomBrenneman)  present on, Job Common Sense 101- Don’t be that Guy!!.  Tom does a lot of work with the Pragmatic Works foundation, runs the Jacksonville SQL Server User Group, and whenever I’ve spoken with him is a very engaging man.  I’m sure his presentation should be very interesting!  

After that I’m off to see Pam Shaw (Blog|@PamShaw)  presenting Taking the Scary out of Monster Reports.  Pam is a flat out expert at SSRS, she recently presented at MagicPASS, my SSUG home away from home, and she is great.  If you work with SSRS I would recommend you stop by and watch her present.  Next up I’m going to attend Chad Churchwell’s (Blog|@ChadChurchwell) session on SQL Server 2012 AlwaysOn for HA and DR.  I’ve had the pleasure of working with Chad since I hopped over to Pragmatic Works, and he is absolutely brilliant, Availability groups are one of the best things to come out of SQL 2012 and I’m really looking forward to seeing Chad’s take on them.  

Finally I’m going to finish off the day watching my friend Rob Volk(Blog|@SQL_R) present on Lego Sets: Relational Building Blocks.  I first watched Rob present at SQL Saturday 85 in Orlando last year, he is funny, engaging, and presenting on a topic I think EVERY DBA should learn about, Set Based Logic.  This is the mathematical foundation of how data set’s inside of SQL server work, and are the most efficient way to move and alter data vs. the Row By Agonizing Row approach (RBAR).  This should be a great way to end the day.

PAGE & ROW COMPRESSION IN SQL 2012

I’m presenting on my favorite topic, data compression.  And I hope you will stop by and join me if you are at SQL Saturday.  Here’s my 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. In SQL 2012 we add Spatial Data Types. 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.”

This presentation is always a lot of fun, small crowd or big.  AS what our companies want to track continues to grow, our data volume will only continue to grow, and Compression will be a major component of how to handle that.  Come and learn how you should do it, and I’m not just talking turning it on and off, but really learn what are the internals of compression.  Learn how to decide what you should and should not compress, and walk away with a Monday Morning Checklist that will aid you if you are looking to apply this soon at your job.

SEE YOU THERE

As always Thanks for stopping by Dear Reader, I hope to see you at SQL Saturday and if you cannot make it to my session, I hope to see you at the event!

Thanks,

Brad

SQL Rally 2012 Vote For Me!

Hello Dear Reader!  Last year I tossed my hat in the ring on the world of Presenting.  This was a tremendous experience for me.  Presenting is a whole other skill set, one that I had not exercised very often.  The more I presented the more I learned.  I received wonderful questions and feedback that helped me grow from SQL Rally 2011, presenting Page & Row Compression How, When, and Why to the PASS Summit 2011 where I presented Page & Row Compression: Deep Dive.  All of this and there is still more to go into. 

"So Balls," you say, "What are we voting for again?"

Great Question Dear Reader!  While I've presented loads on Page & Row Compression, in 2012 we are adding Spatial Compression to the mix and Vertipaq Compression that gives us Columnstore Indexes.  Not to mention we still have Backup Compression as well.  Put all that together and I've got enough information to fill a whole day, and that is just what I'd like to do!

I've submitted a Full Day Pre-Con on Compression to SQL Rally 2012.  I am honored to say that I'm up for vote along with some other really great sessions.  And you Dear Reader get to vote and decide on who the final two are that make it.

THERE'S ALWAYS ROOM FOR COMPRESSION

The title of my Pre-Con is "There's Always Room for Compression" and here is a copy of my abstract.

Come on There’s Always Room for Compression!  In SQL 2012 Compression hits with a Bang.  We should all be taking backups, and Compressing our backups as well.  But what advanced feature can cost Compression performance and how can you re-claim it?  SQL 2008 we got Backup and Page & Row Compression, SQL 2008 R2 gave us Unicode Compression to add to the mix, and SQL 2012 give’s us Page & Row Compression for Spatial Data.  On top of that we get VertiPaq Compression for Columnstore Indexes.   If you are running an OLTP shop, working heavily with BI, or a mix of the two Compression in SQL Server 2012 is something you should learn about.  There are 3 bottleneck’s in any Database, find out how Page & Row Compression can help you offload I/Op’s for CPU.  BI heavy shop? Understand What Columnstore Indexes are, how they work, and how you can use them. 

If you are interested in Compression, looking at using this at work, or trying to figure out best practices for Compressing Data vote for me!  We will Deep Dive Page & Row Compression and look at the internal components, how it works, how you monitor it, and that will lead to a greater understanding in how to apply it.  The internals of Columnstore indexes using Vertipaq Compression, we will be spelunking on those internals as well!  Come on spelunking on internals, that just sounds fun!  *I first heard that phrase from Paul Randal(@PaulRandal | Blog), it is his but I'm borrowing it J!

Not only will we be Deep Diving a couple different area's but I want this to be an interactive day.  I will be putting together a Sample Database that we will use so we can work together and by the end of the day you will have hands on experience with Determining what Tables are good candidates from Compression, Compressing them, taking baselines Before and After in order to see what benefits that we have achieved.

Compression can help you if you an OLTP shop or if you are a DBA who works primarily with BI.  There is something for everyone.

GO VOTE!

Even if you do not vote for me there are a lot of really great Pre-Con's that you can vote for, (Like my friend & Co-Worker Bradley Schacht(@BradleySchacht | Blog) "The 3 P's of Presentation (PerformancePoint, PowerPivot, Power View)".  All you need is an active login for PASS and you can vote.  Please Go cast yours today Click Here!

Thanks,

Brad 

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