Who Do You Work With, Who Drives You, Who Inspires You?

http://www.flickr.com/photos/95952343@N00/403079866/in/photostream/

It is Labor Day here in the U.S.A. where we celebrate the folks that go to work every day by taking a day off.  Trust me the irony of the holiday is not lost on me. 

I was sitting around Casa De La Balls trying to think of a holiday themed blog.  Most holiday’s we are very busy.  There are Kiddos to attend to, meals to cook, and hopefully some football to watch.  So I have yet to do a “Holiday Themed Blog”.  And to be honest this one was a tricky one at first.

“So Balls,” you say “you’ll ramble on like this for a couple of pages and call it a day?”

I hear you Dear Reader, and I’ll get to the point.  Today is all about work, and there is a saying around the place that I currently work that comes to mind.  “The People Make the Difference.”  And there is a lot of truth to it.  For those of you who are not aware I’m currently a Sr. SQL DBA for Publix Supermarkets.  I studied this company before I decided to leave Washington to join them.  And I really liked what I found, I’m not going to blog about them as a company, but we are a very good one and if you’ve never heard of them I’d encourage you to read up on us.

But today it’s all about the people!

WHO DO YOU WORK WITH?

http://www.flickr.com/photos/ro_buk/311934912/

When you go to work every day you can probably close your eyes and tell me the routine.  I park here, I walk through these doors, I put my stuff down, I check email, I go get coffee, blah blah blah.  But when you think of work there should be some people that you think of right away.  

For the past several jobs that I’ve had I can very easily think of the people that when I saw them my day got better.  They were people who would be fun to hang around, were very intelligent at what they did, or had a great personality that made it a pleasure to work with them.

When you get a good team chemistry going you cannot beat it, and you cannot do that alone.  I started out my job history in the IT field as a programmer.  I learned a lot of great things as a programmer.  How to dissect code and look for patterns, how to figure out what is being done, how to look for resources to become familiar with code you’d never written, and how to make the seemingly impossible possible.  That kind of team chemistry requires trust and confidence. 

Trust that the people around you know what they are doing.  That trust frees you to be as good as you can be in your chosen field.  If you’re always worried about Database Performance then you are going to study it, and it is a field that takes a lot of study. 

You also need Confidence.  You should be confident that your teammates can handle their jobs.  You shouldn’t have to worry about your teammates, I learned about SAN’s from a SAN Engineer that was very smart and was eager to share.  I knew I was learning from him because I was confident in his abilities.  I probably babbled on about database just as much as he did about SAN’s.  That freed me up to become the best Programmer/DBA I could be.

WHO DO YOU WORK WITH THAT TEACHES YOU.

http://www.flickr.com/photos/osowska_marta/5873972885/

But I didn’t get there overnight.  I had a lot of help.  I had some really good teachers, colleagues, and friends.  I remember one very smart friend in particular, who was my boss at the time, that whenever I hit upon a problem and I didn’t know the answer I would ask him, and he would never give me a straight answer.  It was incredibly frustrating.  I remember complaining about it a time or two twenty until one day I realized what he was doing.  When I would ask a question instead of giving me the answer right away he was leading me to the next logical step.

 After a while of this I realized that he was teaching me to troubleshoot the way he did.   My questions then changed from “What does it mean when I see X” to “Where would you go to find more information on X”, and eventually the questions stopped.  I had learned what he knew.  However, like any good mentor he always had some tricks up his sleeve that would impress me from time to time. 

As a manager he taught me to be self sufficient.  When I would mentor new people I would provide them with the information that I knew they needed, but then I would help them to ask the questions so they could learn for themselves.

WHO DO YOU WORK WITH WHO DRIVES YOU?

I had another friend who sat right next to me for a couple of years.  My good pal Smitty.  When I first looked at getting certifications I was looking and getting the full suite of .NET programmer certifications.  Smitty kept saying to me, “Balls we should become DBA’s”.  He kept pushing me to go for DBA certifications instead of programming certifications.  I owe a lot to his persistence.

http://www.flickr.com/photos/rogerssg/4991321088/in/photostream/

At first we both got the 70-431 book together, and my goal was to do a chapter a day.  As I got into the book I realized a lot of what was in there I already did every day.  I was what you would call an, “unintentional DBA”.  It just so happened those things that made up DBA work were my favorite things to do.  While studying for the certifications I found a lot of best practices and would go to our Sr. DBA’s and say “are we doing this”.  When they said no, I would say can I do it.  We were a small shop and the DBA’s were tasked to capacity.  I already had the permissions needed and they said sure.

At a job after the previous one, at a business meeting I met our Vice President of Microsoft Operations.  We had a really nice conversation and we discussed the areas of Microsoft technologies that I was most interested in.  Not surprisingly we discussed SQL Server quite a bit.  A couple months later out of the blue I got an email from the VP forwarding an invitation to the first 24 Hours of PASS.   I signed up and loved every minute of it. 

Fast forward a couple of years and a new place of work and I had some friends, one of whom is none other than Dan Taylor (@DBABulldog | Blog),  that were constantly talking about this SQL Saturday thing.  Last year in Orlando was my first.  And the Impact that my First SQL Saturday had is reflected all over this blog.

When you look at where you are, you’ve got to look at where you’ve been.  Chances are you’ve worked with some people that helped you get there.  Even if it was just a chance email, a someone to study with, or a friend that recommended a new way to learn.  And maybe, just maybe you’ve made someone’s life better just by being you.  We’ve got a saying over at Publix that Mr. George used to say, “Publix will be a little better place, or not quite as good, because of you.” 

WHO SUPPORTS YOU WHILE YOU’RE AT WORK

I had a lot of little things that helped move me to where I am today.  But there was one person who is a constant influence and that is my wife.  How do you say Thank You to someone who constantly supports and encourages you? 

When I wanted to pack up the family move to a place in Virginia hundreds of miles from friends and family for a job, she let me do it.   When I wanted to move even further so I could work for the President, and she would have to give up working because my commute would be so long that I would be gone before the kids woke up and home just around dinner, she did it.   Participating in SQL Saturday’s, SQL Rally, the PASS Summit, SQL Server User Groups, when I do those things she’s pulling double duty with the kids, or taking off work, or re-arranging her schedule.  Raising a family is hard work, and I couldn’t do what I’ve done without her help and most definitely all of her hard work.

So today when we celebrate those who work in the good old U.S.A. take a look around you and think of the people that have touched your life in some way.  Say Happy Labor Day, and know that you appreciate their hard work.  Don’t forget about the hard work that those close to home put in as well, because they are the ones that make everything else possible.

Happy Labor Day Everybody (especially you my Wife)!

Thanks,

Brad

Partitioning a Heap

http://www.flickr.com/photos/wouter28mm/3151414412/

During a busy day sometimes I don’t have a chance to check Twitter.  I’ve written about how Twitter is a wonderful place to learn, Top 5 Reasons You Should Be On Twitter, and I happened to glance down at just the right moment. 

Grant Fritchey (@GFritchey | Blog) asked a question on #SQLHelp. And I thought I knew the answer, YAY, but it turned out I was wrong.   Grant had asked the question if you needed a primary key in order to Partition a table.  Originally I had replied to Grant that I thought you had to have a Clustered Index in order to partition a table.

Some of the top minds in the SQL field quickly set in to give Grant the answer that he needed.  And like any time when you get something wrong this was a wonderful opportunity to learn.  The exchange went like this (You have to read this from the bottom up):

WOW!  That is a lot of information over a very short space.  Whenever I have used partitioning I’ve always used it on a Primary Key Clustered Index. 

It’s a technology that I’ve used that I’m familiar with, but not one that I’ve played with just to see what could happen.  Amit Banerjee(@banerjeeamit | Blog), Aaron Nelson (@SQLVariant|Blog), Paul White (@SQL_Kiwi|Blog), and Dave Levy (@Dave_Levy|Blog) are some pretty ridiculously smart guys.  If they said it could be done I believed them.  However, that's not enough.  You shouldn't just take it on faith alone.  You should go out there and do it yourself.

So with that Dear Reader I figured I’d work up some code we could both play with that demonstrates what the experts where talking about and lets us prove it out at the same time.

CREATE OUR DEMO_PARTITION DATABASE

Let’s Create our Database

/*

Create our Database

That we will use for the

Demo

*/

USE master;

Go

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

     BEGIN

          DROP Database demo_Partition

     END

    

CREATE DATABASE demo_Partition

GO

Then we will add some Logical File Groups

/*

Add Filegroups

*/

ALTER DATABASE demo_Partition

ADD FILEGROUP FG2

GO

ALTER DATABASE demo_Partition

ADD FILEGROUP FG3

GO

ALTER DATABASE demo_Partition

ADD FILEGROUP FG4

GO

ALTER DATABASE demo_Partition

ADD FILEGROUP FG5

GO

Then we will associate those Logical File Groups with some Physical Data Files

/*

Add Files and

associate to filegroups

*/

ALTER DATABASE demo_Partition

ADD FILE

(

     NAME=data_FG2,

     FILENAME='<Insert directory Path>\demo_Partition_FG2.ndf'

) TO FILEGROUP FG2;

GO

ALTER DATABASE demo_Partition

ADD FILE

(

     NAME=data_FG3,

     FILENAME='<Insert directory Path>\demo_Partition_FG3.ndf'

) TO FILEGROUP FG3;

GO

ALTER DATABASE demo_Partition

ADD FILE

(

     NAME=data_FG4,

     FILENAME='<Insert directory Path>\demo_Partition_FG4.ndf'

) TO FILEGROUP FG4;

GO

ALTER DATABASE demo_Partition

ADD FILE

(

     NAME=data_FG5,

     FILENAME='<Insert directory Path>\demo_Partition_FG5.ndf'

) TO FILEGROUP FG5;

GO

USE demo_Partition

GO

CREATE OUR PARTITION FUNCTION & SCHEMA

Now that we’ve got our Database we need to create our Partition Function and our Partition Schema.  Our partition Function is a range of values going right or left for a particular data type.  In this example we’ll be using an integer data type and we will be doing a left range for values 2000, 4000, and 6000.

This means that anything <=2000 will go in the Filegroup that we associate with this range of the function, <=4000, and >=6000.

So we should end up with 4 partitions ranging from 1 to 2000, 2001 to 4000, 4001 to 6000, and 6000 & up.

/*

Create Partition Function

*/

CREATE PARTITION FUNCTION compDemoPartionFunc(INT)

AS RANGE LEFT FOR VALUES(2000, 4000, 6000)

GO

Now we will create our Partition Schema which we will use to associate our Partition Function to our Logical File Groups

/*

Create Partition Scheme

*/

CREATE PARTITION SCHEME compDemoPartitionPS

AS PARTITION compDemoPartionFunc

TO(fg2, fg3,fg4, fg5);

PARTITION A PRIMARY KEY CLUSTERED INDEX

http://www.flickr.com/photos/21684795@N05/4406836491/

Now that we’ve done all this, let’s start with the trusted standby of partitioning a clustered Primary Key.  First let's create our table, including our primary key and clustered index.  We’ll drive to the basket and slam dunk this.

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 char(800) DEFAULT 'some product',

     productSKU varCHAR(500) DEFAULT 'Product SKU',

     productDescription varCHAR(max) DEFAULT 'Here is a Generic Production Description',

     Comments TEXT DEFAULT 'here are some genric comments',   

     CONSTRAINT PK_myTable1_myID

     PRIMARY KEY CLUSTERED(myID)

) ON compDemoPartitionPS(myID);  

Now let's populate our data with 10,000 rows.

/*

Let's populate our Primary Key

Clustered Index with some data

*/

DECLARE @i INT

SET @i=0

BEGIN TRAN

WHILE (@i<10000)

     BEGIN

          INSERT INTO myTable1 DEFAULT VALUES;

          SET @i = @i +1

     END

COMMIT TRAN

Now let's take a look at see our breakdown.  We don’t want to just scratch the surface here we want to dig deep and make sure that all of our rows are on the Filegroups that we selected for them.  To do this we’ll be looking at sys.destination_data_spaces and joining sys.partition_schemes as this table holds the pointers for our partition schema.

select

          so.name AS [Table Name]

          ,si.name AS [Index Name]

          ,ds.name AS [Filegroup Name]

          ,dds.data_space_id AS [partition number]

          ,sp.rows

          ,dds.partition_scheme_id

          ,ps.name as [partition scheme]

from

     sys.data_spaces ds

     LEFT join sys.destination_data_spaces dds

     on (ds.data_space_id = dds.data_space_id)

     left join sys.partition_schemes ps

     on (ps.data_space_id = dds.partition_scheme_id)

     LEFT JOIN sys.partitions sp

     ON dds.destination_id=sp.partition_number

     LEFT JOIN sys.objects so

     ON sp.object_id=so.object_id

     LEFT JOIN sys.indexes si

     ON so.object_id=si.object_id AND sp.index_id=si.index_id

WHERE

     so.type='U'

order by

     ds.name

     ,ps.name ASC

As you can tell from above our partitioned table went on the Logical Filegroups we had created earlier.  Because we associated them with physical Filegroups we can be confident that our data is going to the correct location.

PARTITION A CLUSTERED INDEX (NO PRIMARY KEY)

http://www.flickr.com/photos/39083668@N06/4347173049/

 

So now let’s drive the lane.  We'll do the exact same exercise, but this time with a Clustered Index with no primary key.

Start by creating our table.

/*

Let's create a Heap and then

we will create a Clustered Index

With no Primary Key Constraint

*/

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

BEGIN

     DROP TABLE dbo.myCLX

END

CREATE TABLE myCLX(

     myID INT IDENTITY(1,1),

     productName char(800) DEFAULT 'some product',

     productSKU varCHAR(500) DEFAULT 'Product SKU',

     productDescription varCHAR(max) DEFAULT 'Here is a Generic Production Description',

     Comments TEXT DEFAULT 'here are some genric comments'    

) ;

Now let’s create our index and associate it with our partition schema.

CREATE CLUSTERED INDEX clx_Index1 ON dbo.myCLX(myID)

ON compDemoPartitionPS(myID);

go

Now let’s populate our table and look at the results.

/*

let's insert some data

into our Clustered Index

*/

DECLARE @i INT

SET @i=0

BEGIN TRAN

WHILE (@i<10000)

     BEGIN

          INSERT INTO dbo.myCLX DEFAULT VALUES;

          SET @i = @i +1

     END

COMMIT TRAN

We will use the same query and set of DMV’s that we used before. 

 We see that we didn’t need a primary key to achieve the exact same results as above.

PARTITION A HEAP (NO CLUSTERED INDEX)

http://www.flickr.com/photos/lscan/2225285352/

Now we’ve hit 2 for 2, let’s see if we can go for three.  Same exercise this time we are using a Heap.   Remember a Heap is simply a table with no Clustered Index on it. We’ll start by creating our Heap.

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

BEGIN

     DROP TABLE dbo.myHeapNCI

END

CREATE TABLE dbo.myHeapNCI(

     myID INT IDENTITY(1,1),

     productName char(800) DEFAULT 'some product',

     productSKU varCHAR(500) DEFAULT 'Product SKU',

     productDescription varCHAR(max) DEFAULT 'Here is a Generic Production Description',

     Comments TEXT DEFAULT 'here are some genric comments'    

) ON compDemoPartitionPS(myID);  

Then we will populate it with some data.

/*

let's insert some data

into our Heap

*/

DECLARE @i INT

SET @i=0

BEGIN TRAN

WHILE (@i<10000)

     BEGIN

          INSERT INTO dbo.myHeapNCI DEFAULT VALUES;

          SET @i = @i +1

     END

COMMIT TRAN

Now let’s run the same query as before to see how our data is spread out.

Now this is interesting.  Look at the null in the column for the Index name.  It makes sense we do not have an index on a Heap.  However our Heap is still partitioned.  The home team wins!  And the crowd goes wild!

WRAP UP

http://www.flickr.com/photos/joao/2198486114/

So once again Twitter had found a way to get information from the best and the brightest in the SQL Community.  If you would have asked me before all this I would have told you, that you had to have a Clustered Index on a table in order to partition it.  I’m not sure where I even got that bit of information, but when I picked it up along the way it was wrong.  

I’m glad during a very busy day that I looked at Twitter just in time to learn something new. 

But with new knowledge comes new questions, why would you want to create a partitioned heap, why would you create a partitioned clustered index without a primary key? 

The heap question I could take a stab at, but I wouldn’t want to do it without proofing out a couple theories.  So there we go Dear Reader more questions that will hopefully lead to more learning.

Thanks Again,

Brad