Stay Positive Kiddo

I say this to my kids whenever I see them getting frustrated, or if a negative attitude is threatening a good time.  No parent wants to go to Disney World and hear, “When are we going to do something fun?”  It is a good way not to go back.  Everyone likes to be appreciated.  When you’re treated poorly there is not a lot of incentive to act in a positive way.

We work in a field where things are constantly changing.  We have to constantly keep learning.  For the majority of people out there, Learning is not our day job.  We have another job that we work, where there are demands on our time.  Hopefully we get chances to use the knowledge we spend our time working so hard to learn.  Learning is tough, it takes time, away from our family’s and away from places like Disney World.

In our quest to continue to learn, and to possibly implement we will hit challenges.  You might try to be an early adopter of a new software product, you may be pushing to get productivity tools like Redgate, or you may be pushing to get best practices adopted.  In our job that is outside of learning you are sure to face challenges.  The most important thing is to stay positive.

“But Balls”, you say, “My Boss Doesn’t Listen, my work place has BAD habits, we don’t follow best practices, when I make a point in a meeting no one listens even though I’m right.  They are never going to change why should I keep trying”

I hear you Dear Reader, and if someone in the IT field hasn’t been in the position you are then they are new to the IT field.  It will happen one day.  But when you Stay Positive, you’re not always doing it for the other guy, sometimes you’re doing it for yourself.

IT’S NOT YOU IT’S ME

If you’ve ever been in the spot where you are the one in the room that is always right, but nobody wants to listen to you, it is very frustrating. 

You can feel like you are shouting at the sky, for all the good that you are doing, and you may be tempted to just quit trying.

You’ve got two choices one choice in this case Blame Everyone Else or Take a Long Hard Look at yourself.  Look at the way you try to lead a conversation.  Is it an Argument between two parties or is it a civil discourse? 

When your Manager or Co-worker's say no to an idea of yours, perhaps they have genuine concerns.  When they speak listen, and see if those are concerns you know how to relieve. 

My outlook is that if I don’t know a technology well enough to alleviate those concerns then I shouldn’t be trying to push it on them.  That my require more learning on my part. 

I like to work up Demo’s, it allows me to learn something and then turn around and have something to show.  They can ask questions, and if you don’t have the answers you go an learn and come back with them.

This all get’s back to Trust.   If you spend a lot  of time shouting, or talking normally but with a lot of disdain then that is exactly what people will expect from you.  If they trust they will have a negative experience with you, you have to over come that first and formost.

Stay Positive Kiddo find a better way, it is out there and you can do it!

NEGATIVE <> ENTHUSIASM

Whatever you do, don’t start spewing negativity like a sprinkler.    Back when I was in college I was part of a Fraternity and we needed to host an event to get people to show up, and consider joining. 

I liked to think that I came up with the plans, that I was the “idea guy”.  Eventually I stopped listening to others because I thought my ideas were right.  One day we were having a meeting and one of the guys suggested, in order to get people to show up to our reqruitment event, having a BBQ in front of the Student Union building, buying glow in the dark cup’s to give away with our phone number and letters on it, a theme for it and T-shirts as well.   To top it off that evening we would have a party and use the BBQ to get people excited and show up.

At first I didn’t like it, I was looking at all the NEGATIVES of what could go wrong.  We would be doing this during lunch time in the middle of a school day, people are going to class not a BBQ.  We are doing this on short notice we need to advertise. People are busy running to class they won't want to stop by.  But I stopped and realized that I was the problem, as I looked around the room all of the other guys were very excited about this idea.  I slowly went around the room and realized everyone was on board with this.  Everyone wanted to do it.  I was the only problem. We did it and It turned out great!

I realized that ENTHUSIASIM was key to this.  We went out and had a great time.  The guys all had a blast.  Win or lose this was a great team building moment for us.  Everybody loves ENTHUSIASIM.  If you see a lot of people having fun, our natural incliantion is to look over and say, WOW what are they doing that looks great!

 You’ve got to have a little fun with it Dear Reader, or at least you need to pull together for the good of the team. 

Sometimes when a user says “Why don’t we try X,Y, or Z?”, even if you know your right instead of just saying no, if there is time and Production isn’t on fire in the background, then take the time to give it a try.  Walk them through it and treat it as an opportunity to learn/teach.  Nobody likes to be told “NO” all the time, sometimes you’ve got to give it a shot. 

Deep down we are all SQL Geek's, PROUDLY, you can always make learning and Demo's fun!  Just Stay Positive Kiddo, you can do this!

RE-INVENTING THE WHEEL OR TAKE THIS JOB…

Under this same argument I will put down sometimes you need to find a better job.  Let’s say you take a good hard look at how the conversations are going, and you find you are positive, you’re growing as a professional, but there is a culture at your work that is hostile or even negative.

There are Pro’s and Con’s to any big decision and I would say look at it hard.  But remember that when you interviewed to work for a company, they INTERVIEWED you, and you INTERVIEWED them.  If they didn’t offer you something you wouldn’t be there.   And if that offer turned out to be smoke and mirrors, then use that to learn in your job search.

Look at the good, look at the bad, and make an educated decision.  After the honeymoon phase of a new job you should have certain expectations of your work place, just as they should have of you.

Thanks,

Brad

Thank You, I'm Speaking At PASS SUMMIT!

I just wanted to say a Big Thank You to you Dear Reader, and everyone that voted for my session!  I've been selected to speak at the 2011 PASS Summit on Compression.  It is a topic that I have really loved speaking and learning about, I'll be blogging more about the topic as I sharpen up all of my material for a Deep Dive!  Here is the abstract from my submission.

Page and Row Compression are powerful new tools. Page & Row with SQL 2008 RTM, and Page & Row with Unicode Compression with SQL 2008 R2. 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!


When I found out I wanted to run up and down the halls high-fiving people!  Was one of the thing's on my SQL Bucket List that I wanted to do.  Truthfully I wanted to make it TO the Summit, as I've never been to one. So the Prep is beginning and I'm super excited!

Thinking about going!  You should, and if you register by June 30th you'll recieve $600 off the full price!  Go sign Up Here.

Once again I just want to say a Big Thank You!

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