SQLBalls

I’m Going to SQL RALLY!

2012-02-27T15:19:00.001-05:00

Hello Dear Reader, I’ve just received great news I’M HEADEDTO SQLRALLY 2012! But I didn’t get here on my own, I have you to Thank. And I would like to do just that. Thank You to everyone who voted for me aspart of the recent Community vote! Ireally appreciate it. It is always anhonor to be picked to participate in a SQL event, but it means a lot when yourpeers vote you in.

I promise that you will not be let down, I’ve got not one,but TWO amazing sessions that made it through the voting.

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

Great question Dear Reader, and away we go!

Transparent DataEncryption Inside and Out in SQL 2012

Security is a veryimportant 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 tothat list. Find out What it does andWhat it doesn’t do, How it effects Read-Only Filegroups, Performance,Compression (Backup and Row/Page), What the X.509 Encryption Standard is andWhy you should be careful of what you store and where, and other AdvanceFeatures as well as some tips on how to manage it.

I’ve had a lot of fun presenting on this topic in thepast. Transparent Data Encryption is awonderful technology that we were able to start using in SQL 2008. I was lucky very early on that as soon as Istarted working with 2008 I was working with TDE.

When you use TDE there are some very important things toknow and consider the first is what TDE does and doesn’t do, the second whatphysical changes actually occur within your database, and finally how youmanage certificates and how they affect your recovery scenarios for yourdatabases.

I’ve done a Lightening round version of this presentation forOPASS, the Orlando SQL Server User Group, my friends at Publix, and for SQLSaturday 79 South Florida this past year. This year I start off the year with a Bang presenting on this topic at SQLConnections in Las Vegas, and now I’ll get to present on this at SQL Rally 2012 as well!

SQL Internals,Recovery Models, and Backups! OH MY!

The more you knowabout SQL Server the more you understand how it works. SQL Server is a product we use every day, andmost of us know the big concepts. At the10,000 foot view we know what Databases,Tables, and Columns are. But what makesup those Databases, Tables, and Columns. What are Records, Pages, Extents, and Allocation Units? What are Full, Simple, and Bulk-LoggedRecovery? What are the differencesbetween Full, Transaction Log, Differential,or Filegroup backups? What is a PiecemealRestore? This is an introduction tothese concepts using SQL 2012. In thissession you will learn about the internal Structure, Recovery Models, andBackups and be better prepared to for Future Learning and Managing SQL!

When I first put together this presentation it was meant tobe a leap frog session.

“So Balls,” yousay “I’m not playing leap frog with you.”

No worries Dear Reader, I’m not playing a game, but what Imean by leap frog is I want you to leap ahead. As I’ve studied SQL Server I’ve noticed that there are some fundamental conceptsthat you encounter over and over again.

The reason because they are all interrelated. A.C.I.D., Transaction Isolation Levels, theinternal components of SQL Server’s Relation & Storage Engine, B-TreeStructures, Pages, Allocation Units, Transaction Log management, Recovery Model’s,and Backups (that’s a mouth full OH MY!). I’m not going to make you an expert, but I’m going to arm you withknowledge and concepts to allow you to go forth and be better prepared forfuture learning.

But what we cover will apply directly to how you wouldchoose the type of backups that are appropriate for the database systems thatyou manage.

THANK YOU

Once again Dear Reader and Dear SQL Community, I would justlike to say Thank You. I hope to see youand I hope to see you at SQL Rally 2012!

Thanks,

Brad

SQL Rally 2012 Vote For Me!

2012-02-06T09:42:00.000-05:00

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, onethat I had not exercised very often. Themore I presented the more I learned. I receivedwonderful questions and feedback that helped me grow from SQL Rally 2011,presenting Page & Row Compression How, When, and Why to the PASS Summit2011 where I presented Page & Row Compression: Deep Dive. All of this and there is still more to gointo.

"So Balls,"you say, "What are we voting foragain?"

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

I've submitted a Full Day Pre-Con on Compression to SQLRally 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 ROOMFOR COMPRESSION

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

Come on There’s Always Room for Compression! In SQL 2012 Compression hits with aBang. We should all be taking backups,and Compressing our backups as well. Butwhat advanced feature can cost Compression performance and how can you re-claimit? SQL 2008 we got Backup and Page& Row Compression, SQL 2008 R2 gave us Unicode Compression to add to themix, and SQL 2012 give’s us Page & Row Compression for Spatial Data. On top of that we get VertiPaq Compressionfor Columnstore Indexes. If you arerunning an OLTP shop, working heavily with BI, or a mix of the two Compressionin 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 forCPU. BI heavy shop? Understand WhatColumnstore Indexes are, how they work, and how you can use them.

If you are interested in Compression, looking at using thisat work, or trying to figure out best practices for Compressing Data vote forme! We will Deep Dive Page & RowCompression and look at the internal components, how it works, how you monitorit, and that will lead to a greater understanding in how to apply it. The internals of Columnstore indexes usingVertipaq Compression, we will be spelunking on those internals as well! Come on spelunking on internals, that justsounds fun! *I first heard that phrasefrom Paul Randal(@PaulRandal | Blog), it is his but I'mborrowing it J!

Not only will we be Deep Diving a couple different area'sbut I want this to be an interactive day. I will be putting together a Sample Database that we will use so we canwork together and by the end of the day you will have hands on experience withDetermining 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 aDBA who works primarily with BI. Thereis something for everyone.

GO VOTE!

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

Thanks,

Brad

Pragmatic Works Has Balls...SQLBalls

2012-02-03T00:39:00.000-05:00

http://www.flickr.com/photos/54296260@N05/6036813377/

That is right Dear Reader, I am making the jump, StartingMonday February 6th I will be a Sr. Consultant with Pragmatic Works. Over the last year I've gotten to know quitea few of the people that work with Pragmatic Works. As a company and as individuals they arestaples of the SQL Server Community. Ifyou've been to a SQL Saturday, SQL Rally, 24 Hours of PASS, SQL PASS Summit, orcountless other SQL related gatherings you have undoubtedly come across someonefrom Pragmatic Works.

"So Balls," you say, "Sr.Consultant does this mean you're no longer a DBA? Are you now jumping ship to become a BusinessIntelligence guy?"

No worries Dear Reader, even though DBA isn't in my officialtitle I'll be up to my elbows in all kinds of DBA goodness! A big part of what I'll be doing is workingwith other DBA's and clients with DBA issues. Now let's jump right in.

START WITH WHY

Brian Knight(@BrianKnight|Blog) haswritten about Why he started Pragmatic Works, read CommunityManifesto at Pragmatic Works, but the real question is Why would I joinPragmatic.

I mentioned Simon Sinek (@Simonsinek |Website) yesterday. I was introduced to him by my now fellowCo-worker Tom Brenneman (@TomBrenneman). If you haven't watched Simon's How GreatLeaders Inspire Action TED Talk, take a break clickon the link here. I'll be here whenyou get back. It's good stuff. Heck it's amazing. If you're like me when you watched it yourmind was exploding with ideas and possibilities afterwards. Take a deep breath, now back to yourregularly scheduled blogging.

"So Balls,"you say "What's your Why?"

You are on your game today Dear Reader. I believe at our core we as people all havesomething that we want to fundamentally believe in. Along the way of life Ipicked up a simple oath, "To Believe in the Life of Love, To Walk in the Wayof Honor, To Serve In the Light of Truth. This is the Life, the Way, and the Light". Simple words, but I found I believe inthem. I believe in Love, Truth, andHonor. When we were kids there werethings that we wanted to do, not because of a paycheck, not to get ahead, butthe things WE believed in. We believed in those things so fiercely thatthey shaped what we wanted to become.

I found that I really liked working with Computers, and sothat became my major in college (eventually),and that was the field I went into (eventually). It's a little hard to draw a parallel there. At the best of times I like my computer, atmost I'm happy with it, I certainly don't love it.

What I found was I enjoyedlearning, and I was in the right field for it. Heck I didn't just enjoy it, I found I loved it! Later I got to mentor and work withothers. When I saw them grow and learnit filled me with a sense of pride, but never so much that I didn't hold dearto the idea that I always have more to learn. Eventually I got to the point where I wouldlearn on a topic and I would get to educate others on it. That led me to begin presenting on topicsinternally for the places where I worked and to the people I worked with. Then last year I started presenting to thecommunity, I started blogging, I started volunteering, and most importantly Istated participating.

Doing so I started finding things that I really loved. I love speaking, I know I'm weird but I likegetting up in front of a room and talking about something technical. I love teaching. Jumping in to situations where I don't know alot about an environment and I'm learning on the fly to overcome a criticalsystem outage, almost as fun as white water rafting. There is a certain Truth to unabashedlyteaching, to not withholding information. When I've worked for a place I've taken great efforts to make sure thatwhen I leave they know what I know, documentation, information, scripts, youname it I want them to have it. If Ihave to keep something from you in order to seem important, then what I'm doingis wrong. In our business Ideas are important,but recognizing that People have to think them, and that People are moreimportant is critical. And finally ifyou act with Love and Truth towards others, there is no way that you cannot be perceivedas Honorable.

So Why am I going to work for Pragmatic Works? Because I see a company that believes inempowering people through education and technology. I see a Company that uses the Pragmatic Works Foundation to train theunemployed and helps Veterans transition to technology jobs after they leavethe service. And not only do they helptrain people, but they hold recruiting fairs, they work actively to placepeople. To top it all off they starteddoing this during the HEIGHT of the Recession, when our economy was at itsworst. When profits were at an all timelow for others, Pragmatic was giving away two free seats in each of its trainingsessions to an unemployed technology professional. I see a company that not only believes in theSQL Community but actively encourages it's employees, and offers incentives,for them to participate.

In short my Why is because I believe in Love, Truth, andHonor. And I believe in what PragmaticWorks is doing as a company, and I'm very excited to participate.

WHO BETTER TO LEARNFROM

I attended thePre-con that Pragmatic Works did for SQL Rally. I have sat in their presentations, and I havelearned from them. They are SQL MVP'sthey are Instructors for the SSAS Maestro Course that is as close to a BusinessIntelligence MCM that Microsoft Currently has. If you were going to learn about SSIS you would be hard pressed to findpeople more qualified than Brian Knight, Mike Davis (@MikeDavisSQL|Blog), and Devin Knight (@Knight_Devin|Blog). From a DBA perspective there are top notchSQL guys like MVP Jorge Segarra(@SQLChicken|Blog), Gareth Swanepoel(@GarethSwan|Blog), and Chad Churchwell(@ChadChurchwell|Blog). If SSAS is your bag then I would point you no further than Mr. AdamJorgensen(@adam_jorgensen|Blog).

I cannot say enough good things about this team, and thereare many more amazing MVP's and MVP caliber people that I haven't listed. At Pragmatic I will learn How to be theabsolutely best technology specialist I can be throughout a wide range ofMicrosoft technologies.

Throughout all that learning I'll be Consulting, Teaching,Presenting, and Blogging. I'm veryexcited to get started, and I hope you will come along as I explore this nextphase of my career. Until next time DearReader.

Thanks Again,

Brad

Thank You & Goodbye Publix

2012-02-02T11:33:00.004-05:00

It's been a little quite here at SQLBalls.com Dear Reader, but business is about to pick up. There are a lot of changes in store for this year. The very first of which is that Friday February 3^rd 2012 will be my last day working for Publix. Working for Publix has been a tremendous opportunity for me, and I have grown professionally in ways I never dreamed of. When I left Virginia for Florida Publix was the company I wanted to join. I knew of them, but not a lot about them. The more I learned about them, the more I would come to love them as a company.

“So Balls”, you say, “Where are you going?”

That Dear Reader is an excellent question. And one that we will address tomorrow. For today I wanted to take a moment to say Thank You to Publix.

PUBLIX

Publix is a Fortune 500 Company in size and in Capacity, it has been in the Fortune 100 Top Places to Work for 15 years in a row. The concept is simple one.

Back in the early 1900’s a Man named George Jenkins was the youngest manager in Piggly Wiggly History at the time. He was quite successful. So successful that the President of Piggly Wiggly had told George that if he had any idea’s he could drive up to Atlanta and share them. George was a man full of ideas and he set out on a trip to discuss how they could revolutionize the grocery store business. In those times it took 2 days to travel by car from Fl to Atlanta GA.

When George arrived he sat waiting outside of the President’s office. He saw men in suits enter and over heard some of the conversation. At that point the President’s Secretary walked over to George and said that, They were too busy to meet with him that day, and he should just drive back down to Tampa and come back some other time.

The conversation that George had overheard was about the Golf scores they had shot the previous day, and the course they were planning on going to that day. Needless to say the 2 day drive back down to Florida proved fruitful for George, but not for Piggly Wiggly. George decided that he wanted a place where every Customer and every Employee would be valued above all else. That the cost of doing business would not come at the cost of serving the customer or employees. George quit Piggly Wiggly and went on to found Publix.

True to his word the very first year that Publix was opened the company ran into the red. The reason it lost money that year? From the very first day George issued stock in the company to every employee, and paid a dividend per share. Goerge felt that if you own something you would put more work into it, and from the bag boy up to the owner he wanted everyone to feel pride in their business. That continues to this day. Publix is the largest Employee owned company in the United States. As a matter of fact the only way to get Publix stock is to work for Publix.

When you start with Why great things happen, go watch Simon Sineck if you don’t believe me. Why, George believed that by Serving people and his Employees that everyone would benefit one another. How, he would make that the standard and offer Premier Customer service. What, George just happened to open a grocery store, that went on to become a multi-billion dollar company, dedicated to this day to become the “Premier Grocery Store” in the world.

Publix was founded during the Depression, at a time when new business shouldn’t succeed. When many established businesses went under. George used to get a ride to work with his butcher because instead of buying a car, he spent his money on a delivery truck for his store. He was at every opening of every Publix when he was alive. And there are pictures of him bagging groceries and walking customers out to their cars to load them. The week he died, he had stopped in old store #1 like he did most weeks. To say hi and see how things were running. If you live in South Carolina, Tennessee, Georgia, Alabama, or Florida you will be able to find a Publix. I would highly recommend that you stop by and see what George built for yourself.

THE PEOPLE

I don’t care how many times I’ve said it, or how many times you hear it The People make a difference in where you work. At Publix I have worked with some of the smartest and most dedicated people I have ever met. It is a very common thing at Publix to run into someone who has been with the company over 30 years. There are many who still remember the founder of Publix, Mr. George, affectionately and share stories of him.

One of Publix motto’s is “The People Make the Difference”. And it could not be more true. Whether it be the individual at a store level that goes the extra mile, the people from Corporate and Manufacturing that load up their own trucks to get supplies to Stores and Customers affected by hurricanes, or the many late nights that the IT staff put in to see that the Store Systems that make the Customer experience so seamless continues to keep working. It is the people. The People that work at Publix tirelessly devote their time to charitable efforts without looking for glory or recognition. Their work with the United Way and in sponsoring children and families during Christmas are two that leap to mind immediately. But you don’t have to look much further to find men and women that are active members of their Churches, Volunteers for Boy & Girl Scouts, Board Members of State Colleges, and servants of many other fine organizations.

But that is a big body of people. I worked on the DBA team, specifically on the SQL DBA Team. The men I worked with are some of the finest DBA’s I’ve ever worked with. I learn constantly with them, and they have no shortage with which to teach. When you manage over +3000 SQL Server Instances (Almost 4000) you have to be at the top of your game, or things go wrong.

I could write a profile on each member of the team and offer glowing reviews and recommendations based on their knowledge of Internals, Optimization, Troubleshooting and Performance Tuning, High Availability and Disaster Recovery. They are people that I have learned from, gained amazing insights into managing SQL Server, received amazing Scripts from, they even helped me move!, and I had many wonderful personal conversations with. Needless to say I look forward to keeping in touch with them, because they are amazing individuals that I hope will be life long friends.

Thank You

So I want to take a brief moment to say Thank You. Thank You to all the people I’ve worked with. To the DBA’s that I spent late night’s and weekends with Thank You. To the Analyst, Programmers, Data Analysts, and Managers Thank You. To the co-workers that encouraged me to speak at events, sat in them, and offered me constructive criticism Thank You. I am better for having worked with you and I wish you all nothing but the best.

HERE’S YOUR CHANCE

So now Dear Reader I would like to say to you, Publix will be hiring in the near future. As you can tell from my glowing review, I think this is a wonderful place. An opportunity has arisen that I just cannot pass up, and that spells Opportunity, What Opportunity are you looking for, for you.

Here is the link to the job posting on the Publix Website, click Here. Good luck to you if you decide to apply. And Thank You Again Publix.

Thanks,

Brad

Nothing Changes If You Don't Volunteer

2012-01-25T00:07:00.002-05:00

http://www.flickr.com/photos/cubagallery/4690390650/

Hello Dear Reader, last time on Day's of Our Lives there wasan election, some people resigned, and other's were appointed. Let's join this already in progress. So it appears that the 2011 PASS BoardElections did not go quite as smooth and un-controversial as this old friendlyneighborhood DBA would have liked.

"So Balls,"you say, "What's going on?"

Good Question Dear Reader, I'm glad you asked. Brent Ozar(@BrentO | Blog)has already done a good job of cataloguing not only his comments but just abouteveryone else's so far, read here PASS BoardBrouhaha BS Bulletin. Now thiselection season I tossed my hat in the ring to participate in the NomCom, I'm onthe NomCom What's a NomCom, so I feel a little more personally vested thistime around.

THE NOMCOM GET'SBALLSY(SP?)

Going into the job I had read about the previous year'scontroversy. I wanted to know what I wasgetting into, I also wanted to make sure that the Community first and foremostgot a fair shake. I've had theopportunity to do some very cool things this past year. And hopefully I'll get some more chances todo even more. But I'm not so inundatedinto the PASS nomenklatura that I consider myself an insider. Most the time I feel like Tim Drake that just trickedBatman into letting me into the Bat Cave.

Two Years Ago if you had asked me who ran the PASS board,that was responsible for the PASS Summit, I would have told you that it was insome Super Secret fort up in Redmond where Paul Randal, Andy Warren, SteveJones, Brent Ozar, and Kimberly Tripp sat around with secret decoder ringssolving the world's problems with the help of Microsoft's finest.

So when I submitted to be on the NomCom I did not expect toget on. People were supposed to submit,and then there would be an election to fill the 3 Community slots to give theCommunity-at-Large the majority seats on the Committie. Instead of that, only 3 people submitted andwe were appointed to the slots.

I want that to sink in for a moment, ONLY 3 PEOPLESUBMITTED. Not in a bad way, but in aOMG I COULD HAVE DONE THIS, kind of way. If you had any opinion on this election, you could haveparticipated. Seriously. The time commitments were there, but theywere not nearly what I was expecting.

Very early in the NomCom process Thomas Larock (@SQLRockStar | Blog) said something that stuck with me theentire time. "We are not here to eliminateanyone. Our job is to put forward thebest slate of candidates for the community to decide upon." I knew we were there to trim the slate ifneeded. I wasn't sure how this would beachieved yet, but I never forgot that. We were not there to decide but to make sure that the community coulddecide.

When it came to the candidates we were told that at most wecould present a slate of 12. 6applied. Once again I would like that tosink in. ONLY 6 APPLIED. There was no secondary InternationalCandidates to choose from, there was no one eliminated. As a matter of fact we could have acceptedmore candidates if anyone had applied.

At that point our job consisted of vetting the candidates tosee that each would be able to fulfill the duties of a PASS Board Member, andto rank them and present them to the Board. The Board approved the slate and our job was done.

WHAT SIDE ARE YOU ON BALLS?

Just to clear a couple things up, I like Brent. I think he's swell. As do I Tom Larock, Sri Sridharan, Kendal VanDyke, Geoff Hiten, Bill Graziano, and I would probably like JRJ even though I haven't'met him.

http://www.flickr.com/photos/acrylicartist/6739350957/

Brent reminds me of one part Stephen Colbert another part Intrepidreporter. He would probably win anelection in a LANDSLIDE, (in South Carolina), but his schtick isn't somethingthat lends itself to stuffy board rooms.

I could be wrong, I'd love to see Brent on the Board. But let's face it, the man knows what hewants to do in the SQL world. Sitting onthe PASS Board isn't one of those things, or Brent would already be on it. But just like Stephen Colbert we need to knowwhen we look like @$$holes, and he is the guy that is willing to call us on it.

I feel for Sri. Sri Ithink that you campaigned just as hard as, and harder than most people outthere. The effort you put into yourcampaign really impressed me. You havedone a lot of amazing things down in the great Republic of Texas, and SQL Rallyis going to be an amazing event. I hopeyou do not lose heart in the community.

In the days before personal computers when we just hadtelephones to talk over I remember my Dad once telling me that people were alot braver over the Phone than they were in person. When I asked what he meant by that he told methat, "People would say things over the phone that in person would getthem punched, and if they were brave they wouldn't bother with the phone call."

I think that as adults we have to recognize that Sometimeswe have arguments, Sometimes we shoutwhen we should listen, Sometimes we say things that we regret, and Sometimes wewish that no one was around to see the whole sordid mess. Sometimes you let these things pass becausenobody wins, if you just keep tossing mud. I know I'm guilty of that, and I would bet I'm not alone.

We had 6 fabulous candidates. 3 won. 1 was appointed. Everybody saidif any of these 6 get elected we are all winners. Well congratulations, we got 4 out of 6. Not bad. Not bad at all.

SO WHAT ARE YOU GOINGTO DO ABOUT IT?

http://www.flickr.com/photos/williamkoenig/3654357811/

Sure I think that JRJ would have made a great 7th candidate,sure I wish that as part of the NomCom we would have had a chance to examinehim as we did the other candidates. ButI know from what I'm reading he is like so many others in the SQLCommunity. Great. Passionate. Dedicated.

So next year I'll volunteer. And if I ever feel I'm up for serving on the PASS Board I'll run. In the meantime I'll work with my localchapter. I'll help with the next SQLSaturday Orlando. I'll speak at anyevent and User Group that will have me.

But more importantly you should too Dear Reader! Because the only thing that we the SQLcommunity love more than a little Brouhaha is Volunteers. And you will bewelcomed with open arms. And who knows maybe next year I'll read about you on the NomCom, or vote for you when your running for the PASS Board. A journey of 1,000 miles begins with just one step.

Thanks Again,

Brad

You Should Never Use Backup Log Truncate_Only Except...

2011-12-06T09:04:00.000-05:00

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

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?

2011-11-27T22:40:00.001-05:00

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

A lot has been happening this year. I decided to throw my hat in the ring atbeing a blogger and a presenter on SQL topics a little over a year ago. What started out as a nudge into getting outinto the community resulted in a full fledged dive into the deep end. This has been a year that professionally, Idon'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 beeninvited to submit to a conference, heck I'd never attended a SQL Server UserGroup (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 veryheavily on me lately. You see this word,like all words, can be taken many different ways.

THE EASYOPPORTUNITY

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

These are the kind of opportunities that just fall into yourlap, and are a windfall. You fly intoChicago and one of your buddies just happens to have an extra Cub's ticket forthe game that night. You check into yourcoach flight and get automatically upgraded to first class. Your walking by a ride at Disney and someonewho is leaving says "Hey would you and your kid's like our fastpasses".

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

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

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

THE LEARNINGOPPORTUNITY

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

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

Ahh Dear Reader, but that is typically the way these thingswork out. Ever done a reply all and notrealized the whole company was on it? How about getting auto corrected andtelling 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), yeptold the senior management sorry about making you wet yourself over a serveroutage. I was lucky it was a Japanese company, I only heard from the American staffand they were laughing their @$$es off. How about locking out a production domain account? How about having your SQL Server page to yourC Drive so the first time you present live in front of people your demo thatshould 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 seewho 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 andyou are lying on your back. Nope Ihaven'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 frompulling a muscle, that you hydrate so you can avoid a cramp, and that you willmake darn sure not to repeat. Becauseyou did it once, you got the T-Shirt, and you don't want to ride that rideagain.

THE HARD WORKOPPORTUNITY

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 alittle determination. A couple jobs agoin a land far far away, Virginia, I was working at a job. I enjoyed it I was working hard, and I reallyliked the people I was working for and with. I was busting my hump trying to make myself one of the Key people, oneof the linchpins, and I felt I was deserving of a promotion and a raise.

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

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

The review process consisted of us writing our initialreview. Our managers reviewing andadjusting it, and then meeting with us to come to a consensus on a final reviewthat 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 Ideserved a big raise, and I was ready to go into my review and argue it out andcome out on top.

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

THE MISSEDOPPORTUNITY

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 friendsbecause your phone died. Passing onpresenting at an event so you can attend a family gathering. These are the things that we miss out on inthis game of life. Forget to follow upwith a business contact, go back three spaces.

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

When the baby has a nightmare, or there is thunder outsideshe clings to me. 99.99% of the time shewants Mom. But when she is scared shewants Dad. It's a little thing Iknow. But it makes me happy to be therefor her. I'm not a jerk I'm not happythat she is scared, but knowing that this little person loves and trusts me somuch that just by being beside her she believes I can make thunder andlightning go away is earth shatteringly awesome. Being a Dad means that you are more powerfulthat 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 muchlonger.

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

Sometimes we work late, sometimes we have conference callsduring dinner, sometimes we have trouble calls that keep us away nights andweekends, 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 affectother people. Sometimes that means youare working Saturday morning 12 am to 4 am, sometimes it's Saturday morning 7am to 12 noon. Other days its Thursdayfrom 10 pm to 4 am Friday. It happens tome, it happens to you, it happens to all of us

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

WHAT OPPORTUNITYARE YOU LOOKING FOR

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

Your Mission should you choose to accept it Dear Reader, isto 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 youwill have a Learning Opportunity, hopefully it will just be an EasyOpportunity. And if it is a MissedOpportunity then I hope you learn a little about what was most important, and Ihope you made the right choice.

Whatever it is, whether you know it or not you are lookingfor an opportunity right now. So Goodluck and I'll see you next time.

Thanks,

Brad

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

2011-11-01T09:23:00.000-04:00

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

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

“So Balls”, you say, “Why should I come listen to you talkabout 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 couldlook at that picture to the right and tell you what I see. And I’m not talking, it’s a car and anengine, but what are the components. What is that pink thing? Whatabout the blue thing? I look at thatpicture and I know the basics and that’s about it.

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

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

You need to understand how a Transaction Log works, that SQLhas 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 ServiceLevel Agreements you have with your user.

From ACID to Transaction Isolation Level’s we are going tomake a run at it. I’ll upload the deckand the Demo’s when the presentation is over. Click HERE to go to my Resource Page and get a copy of the presentationand 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

2011-10-31T06:31:00.000-04:00

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 mytale is a cautionary tale for the future. Not to long ago I thought it was the end ofit all. We lived in a world that was oldand neglected. Our species had evolvedalmost as far as we could. Well almostas far as we could. Our homeworld wasonly 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 theenvironment around us. Politicians usedNOLOCK like it was going out of style. Everybody was making promises of a better and faster tomorrow, butnobody even had the guts to upgrade the Government to 2005, let alone make theleap to 2008 or even R2. Suggestions ofanything like that would get you branded a heretic, and in some cases wouldhave friends and family turning on one another.

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

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

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

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

As we got closer to the ships I saw they were made ofsomething called SSIS, when we were loading I asked the Captain if this was likeDTS. He just smiled and laughed and saidwe were in for a treat. For the firsttime in I don’t know how long I knew I could relax. I looked back just long enough to see theworld burning. This place is heading forthe end of its life cycle. I don’t knowwhy everyone couldn’t see it. I will notforget the error of our ways. We willnot repeat this again, or so I tell myself.

But remember we cannot abuse this new home, we must worktogether, and we cannot repeat the mistakes of our past. If we cannot code a better base then we aredoomed to repeat ourselves. So let usbring about a better world and Object Oriented world that may even use SnapshotIsolation to preserve our natural environment for as long as we can. With all these resources, there should be nocontention amongst us.

They are calling our new world Denali. I like the sound of that. The homeworld had 4 sockets and contains 12cores each, and there is RAM as far as the eye can see. We will have better data governance in thisnew 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

2011-10-17T07:31:00.001-04:00

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

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));

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

COMMIT TRAN

Quick Select of the data

SELECT * FROM dbo.heap2

How big is our Table

sp_spaceused 'dbo.heap2';

Estimate our space savings

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

We reduced by around 1/3

Can we do better with Page Compression?

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

How big is our Table

sp_spaceused 'dbo.heap2';

Rebuild With Compression

ALTER TABLE dbo.heap2

REBUILD WITH(DATA_COMPRESSION=PAGE);

How big is our Table

sp_spaceused 'dbo.heap2';

Do another select

to get the Statistics

SELECT * FROM dbo.heap2

Turn Off Compression

ALTER TABLE dbo.heap2

REBUILD WITH(DATA_COMPRESSION=NONE);

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

Thanks,

Brad

Day 6: Friday PASS Summit 2011 Keynote Live

2011-10-14T11:13:00.013-04:00

Hello Dear Reader! Welcome to the final day of the PASS Summit and the Keynote address by Dr. David J DeWitt. We will do this in the same way as the previous Two Days! So hold tight we are about to begin.

SUMMARY

This has been an amazing Keynote.   Dr. DeWitt is brilliant and he is echoing what we as a community are struggling with. SQL is like our College Team, Favortie Sports Team, Favorite Actor, Favorite Period. When we hear about other RDBMS's there is a knee jerked rivalry, however when we get together after the ribbing, Oracle and SQL DDA's live next to one another just fine.   There is a place for everything and this seems to be a way of Microsoft saying "We can and will work together".

This is a stance many people have wanted to them to take for quite sometime, and it should open up a very interesting future for all of us!

Thanks for reading all this week!

Thanks,

Brad

LIVE BLOG

Update 9:52

He hopes to make it to where PDW can handle both, he would rather do that than strap a rocket on a Turtle (the turle being Hadoop)

Update 9:42

There will be a command line utility called Sqoop for PDW v Next to move from Hadoop to RDBMS.   Even though the demo's favor PDW, Dr. DeWitt stresses there is a place for both and they are both here to stay.

We are looking at the Sqoop Limitations for the Sqoop library. In the example shown Sqoop could cause multiple table Scan's.

We will both have Structured and Unstructured Data. Moving to the 20th Century Why not build a data management system that can query across both universides.   He terms's it an Enterprise Data Manager. Dr. DeWitt is trying to build one right now in his lab.

Update 9:32

Summary Pro's Highly fault tolerant, Realitively easy to write arbitrary distributed computations over very larege amounts of data, Mr framework removes burden of dealing with failures from programmer.

Con's Schema embedded in the application code, a lack of shared schema makes sharing dat impossible. (And the slide changed before I could get that down).

Facebook and Yahoo reached a different conclustion about the declaritive language like SQl than Google.   Facebook when with Hive and Yahoo when with PIG. Both use Hadoop MapReduce as a target language.

We now see an example to find the source ip address that generated the most ad revenu along with it's average. The syntax is very java like.

MapReduce is great for doing parallel query processing, a join takes 5 pages using PIG. The Facebook guys can do the same thing in 5 lines using HIVE. However complaints from the Facbook guys MapReduce was not easy for end user, users ended up spending hours if not days to write programs for even simple analyses. of 150K Jobs facebook runs daily only 500 are MapReduce.

Goals of Hive and HiveQL in an attempt to provide easy to use query language.

Table's in Hive like a relational DBMS, data stored in tabloes. Richer column types than SQL they have primative types ints, flaots, strings, dates & Complex types assicate arrays, lists, structs.

We are looking at Hive Data Storage like a parallel DBMS, Hive Tables can be partitioned. When you partition a Hive type table by an attribute, the name of the file becomes that attribute name, so it compresses the data as it stores it.

We are getting a breakdown of queries, showing data seeks across partitioned data and the way it is optimized if you are looking for the attribute value.

Keep in mind there is on Cost Based query optimizer, the statistics are lacking at best.

We are going to look at some PDW v Next vs. Hadoop, for bench marks.   600 GB 4.8 billion rows.

Doing a scan select (*) count from lineitem, then an aggregate with a group by. It took Hive x4 longer than PDW to return the set.

Now we are going to get more complicated, now we will do a join between the two tables with a partition on the key values.   PDW is x4 times faster with with partitions PDW is x10 faster than Hadoop.

Update 9:22

MapReduce Components Coordiantes all M/R Tasks and events, manages job queues and schedules. So how does this work with HDFS. There is a Job Tracker M/R Layer and a HdFS layer. Job Tracker keeps track of what jobs are running. Each TaskTracker maps to the Datanode. On the data node there is a the data that is managed by a job tracker.

He want's OOHHH's and AHH's for the next slide it took 6 hours to make :). Each row of Data on a Node has 2 tuples. The example is customer, Zip Code, Amount. He moves the data which is located y the Map Task. Our user want's to query certain users and do a group by zip code. He shows across the Named Nodes how the data is orginized.

The mappers per node have data duplication, and unique data each. They produced 3 output buckets each by hash value.   Now we go from Mappers to Reducer. The blocks are stored in the local file system, they are not placed back into HDFS. The Reducers have to pull the data back to 3 different nodes in our cluster.   They now sort and seperate the data by hashed zip code. The data may have some duplicated groups by this point. But my guess would be they change by the end.

The Reducer now sorts them by hash of the Zip Code. It then Sum's all similar hashes and returns the data. In general the actual number of Map tasks is generally amde much larger than the number of nodes used.   Why it Helps deal with data skew and failure. If it sufffers from skew or fails the uncompleted work can easily be shifted to another worker.

It is designed to be fault tolerante incase a node fails.

Update 9:12

When the client wants to write a block the named node tells it where to write it. It balances writesand writes by telling them where to go. The reverse happens when a file want's to read it. the NameNode acts as an index telling the reads where to go to find the data.

Data is always checksumed when it is read and placed on disk to check for corruption. They plan for the Drives to fail, the writes to fail on a Rack, and switches to fail, Main Node failures, and data center failures.

When a data node fails the main node detects that and says what data was stored on the data node. The blocks are then replicated from other copies.   If the Main Node fails, the Backup Node Can failover, there is automatic or manual failover available.   The backup node will rebalance the load of Data.

So a quick sumary this is Highly scalable, 1000's of nodes and massive 1000s of TB files, Large Block Size to maximize sequential I/O performance.   No use of mirroring or RAID, but why? Because it was supposed to be low cost. And they wanted to reduce costs. They use one mechanism triply replicated blocks to deal with a wide variety of failures.

The Negative?   Block locations and rcord placement is invisble. You don't know where your data is!!!!

The MapReduce is next. The user writes a query the system write's a map function and then a reduce function. They take a large problem an divide it into sub-problems.

Perform the same function on all sub-problems and combine them.

Update 9:02

Google started Hadoop, they needed a system that was fault tolerant, and could handle an amazing about of Click stream data.

The imporitant components Hadoop = HDFS & MapReduce HdFS=the file system MapReduce is the process system.

What does this offer Easy to use programming paradigm. Scalibility and high degree of fault tolerance, Low up front software cost.

The stack looks like HDFS, Map/Reduce, Hive & Pig sql like languages, Sqoop package for moving data between HdFS and relational DBMS's.

Underpinnings of the entire Hadoop ecosystem. HDFS design goals, Scalable to 1000s of nodes, Assume failures (hardware and software) are common, Targeted towards small numbers of very large files, write once then read.

We are looking at an example of a file being read into Hadoop. The file is moved into 64 MB Blocks, each block is stored as a seperate file in the local file system eg NTFS. Hadoop does not replace the Windows File system, it sits on top of it.

When the Client writes and loads these, the blocks are distributed amongs the nodes (for the example he is susing a replication factor of 3). As he places more blocks they are scattered amongst nodes.

Default placement policy: The first copy is written to the node creating the file. Second Copy is written to a Data node within the same rack. The third copy is written to a dat node in a different rack, to tolerate switch failures, and potientially in a different data center.

In Hadoop there is a NameNode - one instance per cluster. Responsible for filesystem metadata operations on a cluster replication.   There are backup nodes and DataNodes.    Named nodes are the Master, they are backed up. The Named node is always checking the state of the DataNode's. That is it's primary job. It also balences replication and does IsAlive and Looks Alive File.

Update 8:52

Ebay has 10PB on 256 Nodes using Paralled database system. They are the Old Guard. Facebook a NoSQL System with 20 PB on 2700 nodes. Bing uses 150 PB on 40K nodes. They are the Young Turkey's.   WOW, we uout that Bing uses NOSQL.

It is importiant to realize that NO SQL doesn't mean No To SQL. It means Not ONLY SQL.   Why do people love NOSQL.   More Data Model Flexibility, Relaxed Consistency models such as eventuall consistency. They are willing to trade consistency for Availabily. Low upfront software costs Never learned anything but C/Java in school.

He brings up a slide to show Reducing time to insight, by displaying the way we capture, etl, and load data into data warehouses.

NoSQL want the data to arrive, no cleansing, no ETL, they want to use it and analyze it where it stands.

What are the Major Types of NOSQL Systems.

Key/Value Systems MongoDB, CouchBase, Cassandra, Windows Azure. They have a Flexible data model such as JSON. Records are sharded across nodes in a cluster by hashing a key. This is what PDW does, and we call it partitioning.

Hadoop get's a big plug. Microsoft has decided this is the NOSQL they want to go to.   Key/Value Stores are NOSQL OLTP.

Hadoop is NOSQL OLAP. There are two universed and they are the new Reality. you have the Unstructured NoSQL Systems. And the Structured Relational DB Systems.

The differences Relational Structured, ACID, Transactiosn, SQL, Rigid Consistency, ETL, Longer time to Inisght, Mature, Stability Efficiency.

NoSQL Unstructured, No ACID, no Consistency, no ETL, not yet matured.

Why Embrasse it? Because the world has changed. David remembers the shift from the Networked systems of the 80's to today. And this is now a shift for the Database world where both will exist.

SQL is not going away. But things will not go back to the same, there will be a place at the table for both.

Update 8:42

Rick plugs feedback forms. And today is the last day to buy the Summit DVD's for $125.   That breaks down to .73 a session.

Rick Introduces Dr. DeWitt and leaves the Stage. Dr. DeWitt introduces Rimma Nehme who helped him develop his presentation. She also helped develop the Next-Generation Query Optimizer for Parralel Data Warehouse.

Dr. DeWitt is telling us about his lab, the Jim Gray lab. Where he works every day, and Big Data. This is about very very big data think PB's worth of data.

Facebook has a Hadoop cluster with 2700 Nodes. It is massive. In 2009 there was about a ZB worth of data out there. ZB=100,000,000 PB. 35 ZB DVD's would streach 1/2 way from Earth to Mars.

So Why Big Data. A lot of data is not just input. It is Moble GPS Movements, Accustic Sound, ITunes, Sensors, Web Clicks.

Data has become the currency of this generation. We are living in the Golden days of Data. This wouldn't happen if we were still paying $1000 for a 100 GB Hard drive.

Update 8:32

Rick is announcing the Executive Committee for 2012. He mentions that we are having a Board of Directors Election comming up. Use the hashtag #passvotes to follow it on Twitter.

PASS Nordic SQLRally has SOLD OUT! Then next PASS SQLRally will be in Dallas. Rick plugs SQL Saturday, and all of the work we do. The PASS Summit 2012 will be held November 6-9 in Seattle, WA. You can register right now and get the 2 Day Pre-Con's and the Full Summit for a little over $1300.

Update 8:27

Rushabh is speaking about what Wayne means to him and the community, and presented him with an award for his community involvement.

The first thing that Wayne does is recognize Rick.

Wayne lists all of the different things that he's learned both Technical and Personal. He gives a very nice speech, and leaves us laughing.

Update 8:22

Buck Woody and Rob Farley have just taken the stage to sing a song from Rob's Lightning Talk earlier in the day! Awesome.

I cannot describe how excellent that was. But it will be live on the PASS website, and I'll toss the link out when it is. That was truely worth watching over and over again.

A tribute to Wayne Snyder the Immediate PASS Presidient, who's term is ending, airs. They are bringing Wayne to the Stage to honor him. I work on the Nom-Com with Wayne he is a great guy and truely dedicated to PASS.

Day 5: Thursday Summit Keynotes 2011, PRIVATE OLTP Cloud Appliance Announced!

2011-10-13T11:16:00.015-04:00

Hello Again Dear Reader.   I'm at the Blogger's Table today! The Keynote has started and I'll jump right in.

Update 9:55

We are discussing the Hybrid IT view that Microsoft is pushing. The integration between all of their products and the Cloud is very apparent. The code bases are merging, and this will only continue as we progress.

They want to streamline the UI tools, make it so all the products "Just Work" together. This has been a very interesting Keynote. Not as big as the announcements yesterday, but some subtle announcements that I think will have HUGE impact over the next couple years.

Quentin Thanks us and leaves the stage as we watch a video playing on the Appliances. Now on to the Sessions!

Update 9:45

Nice demo the Azure Platform is being used to provide content for Samsung TV in order to have live web applications pushed down to your webenabled TV's

Cihan Biyikoglu is taking the stage to talk about Federations for SQL Azure. This allows the Sharding Patterns to be brought to SQL Azure. This will allow us to access 100's of Nodes and scale out for large scale applications.

Example Blogs 'R' Us, and the unpridictable traffic and continously changing hardware requirements. We can re-partition this on the fly.

Hope you like the Windows phone 7. Azure Market Place, Windows 8, all have a consolidted UI. You can add capacity and re-align your Cloud Based Database Instances on the fly to support user patterns. This is some very interesting stuff.

150 GB Azure Databases and Federation before the end of the year!

Update 9:35

We have a lot of e-books on a drive, we have a full-text index set up on them using sematic search. We are now getting a closer look at how quickly we can retrieve articles, and weight for returned terms. This changes the way we will make internal searches for our Intranet Applications. This is a very powerful tool for anyone looking to utilize that kind of functionallity to their companies internal network.

Next up Juneau & Optimized Productivity. The goal is to unify deployment across Database & BI. They just announced that there is a plug in to deploy the SQL Engine of Express Edition with .NET application code, within the same application deployment.

Now we are onto being able to Scale on Demand. We are now going to get a Demo from Nicholas Dritsas Principal Program Manager for the SQL Server CAT Team on SQL Server Azure.   We are using SSMS 2012 to connect and deploy to SQL Azure.

There are differences in Azure between billing, size, and usage for Business and Web Editions. For the Demo we are using Web Edition. To Access this we can use the Web interface for Azure Manager, which Nicholas is demoing now.

We just got a Cloud Database opened in SSMS, the icon is slightly different very cool! Backup and restore from the Cloud to your Datacenter!? Yep, just announced.

Update 9:25

This is a game changer, Pragmatic Works, Verizon, and Accenture are all early adoptors. Deep dive on this appliance right after the Keynote. I just changed the session I'll be attending.

PDW's performance is imporving why? Because it uses a Rules based Optimizer, where as the rest of SQL Uses a cost based optimizer. Dr. David Dewitt has been helping them impliment a Cost Based Optimizer. Big changes are a-comming.

They just announced Linnux driver support for SQL Server 2012. Change Data Capture for ETL from Oracle to SQL Server 2012 and support for that is now announced!

We are now getting a Demo for semantic search.

Update 9:12

To discuss SQL Server Appliances Britt from the product team is coming up on stage.   To figure out the best black box to create. We are now taking a look at the Dell Parrallel Data Warehouse. We are discussing the way queries are Hashed and sent to Compute Nodes, accross multiple nodes and over 450 Cores.

We are also looking at the HP PDW machine. With multiple racks this system can handle over 700 TB's of data. We are looking at the HP Business Decision Appliance that comes pre-built with Sharepoint. I've seen this first hand it takes 4 clicks to have up and running. It is amazing.

We were just introduced to the HP Consolidating Appliance, it will be available in the next month. This is the first Private Cloude appliance available on the market. 400 Disk Drives, 4 TB of RAM, over 300 cores. This is a beast!

Update 9:07

We have cleansed our data and pushed the clean data back into the Date warehouse. Now we reload the report and it only takes 1 second. It was amazing the difference.

Now we are discussing Data Alerts in Reporting Services. We pick our big customers, we pick customers that are over 1.5 million dollars in gross sales, every 1 day so we can send out Thank You's.

Now we are discussing Organizational Compliance.   The two bullets are Expanded Audit aka User defined Audits, Filtering and User-defined Server Roles. This allows you to seperate DBA rights, from Auditing components.

Now we are talking about Peace of Mind, Production-simulated Application Testing, using System Center for monitoring.

Update 8:57

We are about to get a demo for Data Quality Services. It looks like we may get a ColumnStore Demo after all.

We get a view of a web application, the users are complaining about the performance. It took about 30 Seconds to load.

Now we are creating a ColumnStore Index to fix performance. But there is a still data issue. For that we will be using Data Quality Services. DQS uses Knowledge Bases in order to cleanse your data, you can create your own, or you can go to the Azure Marketplace and get a Knowledge Base to cleanse your record. A quick example you may want to use is Address CASSing.

Update 8:52

Next up is Blazing-Fast Performance.   We have enhancements in RDBMs, SSAS, SSIS, and ColumnStore Indexes. Quentin is now talking about the use of Vertipaq Compression, and how it is the backbone of ColumnStore Indexes. You see that in place PowerPivot, and it will now be in SSAS and in the SQL Engine for use.

ColumnStore's will be treated as an additional Index type. ColumnStore will always be a Non-Clustered Index.

Now we have moved on to Rapid Data Exploration, PowerView + PowerPivot, Administration through Sharepoint, and Reporting Alerts in Cresent/PowerView.   Self-Service BI and empowering the users are theme of this.

Now we are onto the BI Semantic Model, this is the model that actually runs for PowerView/Cresent and that PowerPivot utilizes.   We are now discussing Data Quality Services, next up Master Data Services.

Update 8:47

The Availability Group is completed, and the Dashboard is pulled up so you can see they way it is managed. It integrates Policy Based Mangement to determine and display the health of the AlwaysOn Availability Groups.

Paul is now ebabling the 3 Active Secondaries, and enabling Read Only Secondaries, and showing how in an SSRS report he can set Application intent so the report would automatically go to the read only secondaries effectively offloading the read activtey for reporting with a couple click's.

Update 8:42

Bob is discussing their mission critical application that covers all the in's and out's (litterally) of their orginization. And how essential it is to their company, and to governments. Because they communicate with the Port Authority for each country, other wise an outage can backup ports all over the world.

Paul from the Product Management Team for SQL Server is invited to the Stage to tell us about the technical solution that Mediterranean has in Production.

Paul is showing us a datacenter in NJ, and a particular SQL 2012 Instance running multiple database. He's setting up an Avaiability Group between New Jersey and New York.    New Jersey is the Primary and New York the secondary, (not in real life).
Update 8:37

Quentin said this is the largest release ever for SQL Server.   He cannot talk about all of the different features so he is picking his favorites.

Required 9s & Protection (Always On)
Blazing-Fast Performance (Column Store)
Rapid Data Exploration (Data Explorer)
Managed Self-Service BI

He is discussing the architecture of AlwaysOn with Availability Groups. Bob Erickson Executive VP from Mediterranean Shipping Company in over 142 Countries, over 184 Vessles in their fleet. They are the #1 for Import and Export in the US and #2 in the World.

Update 8:32

Quentin Clark Corporate Vice President of SQL Server for the Microsoft takes the Stage. He recaping the way that SQL 2012 fits into the overall dataplatform Vision that Microsoft has, that was discussed by Ted yesterday.

The Vision

Any Data, Any Size, Anywhere
Connecting with the World's Data
Immersive Experiences Whereever You Are

Foundation for the Future

Mission critical Confidence
Breakthrough Insight
Cloud on Your Terms

Update 8:27

Bill is reviewing the budget numbers for PASS.   They have started a feedbacksite for PASS and are taking suggestions. The PASS Elections are comming up.

Bill introduces Quentin Clark our Microsoft Keynote Speaker.   A video is showing of attendees talking about what we've learned and what we are hoping to bring back from the Summit.

Update 8:22

Lori Edwards was just announced as our PASSion Award Winner for 2011.   Great job Lori!!!

Update 8:17

Bill Graziano takes the stage today is SQL Kilt day. Bill says Hi to his Mom and Dad that are watching, he had the SQL Kilt wearers.

We are recognizing Outstanding Volunteers.

Tim Radney

Jack Corbett

Both are amazing men and I'll need to come back and write more later about this.

Day 4 Summit Keynotes Live, DENALI HAS A NAME! SQL Server 2012!

2011-10-12T11:21:00.022-04:00

Hello Dear Reader, this is my first live blogging. I'm going to do this a little different, I'll be writting in reverse. I owe you a blog on Day 3 and another lesson in Compression for SQL U, and they will be coming but for now the Keynote is about to begin for the opening day . This will stay at the top, but I'll be bloggin in reverse order, so start from the bottom and scroll up to see the updates as they are posted.

Update 10:03

Ted is wrapping this out, he talks about how the community is essential to move this forward. About how we could take the worlds data and use it immedieately.

1st half of the next Calendar we will have SQL Server 2012. He plugs the next two days of Keynote sessions.

Thank You to everyone for comming that wraps it up

Update 9:58

The Windows 8 Tablet is being demoed showing PowerView/Cresent! It is completely Dynamic and internactive.

Ted thanks Amir for taking the stage.

Update 9:53

Amir starts talking about how if you use your data properly you can use it to tell a story. That story is what is going on in your business, and that every business would want to know their own story by the data.

Yesterday they confirmed that they would be able to do Export to PowerPoint from Cresent/PowerView!

Now Amair is showing that Cresent/PowerView is going to work on a Windows 7 Phone, and it is fully functional!

Wow so if your company goes with a Windows 7 phone you can use your self service BI on the Windows phone. Now he is Demoing a PowerPoint on the IPAD 2. He is now demoing PowerView on the Android Samsung Tablet.

Update 9:48

We continue on the Demo, there are a lot genera's to hop from, and it is very interestign to see how internactive this data is.

Amir said Samuel Jackson is no Tom Hanks, but Samuel L Jackson has the most gross ever, Ted cringes. He doesn't know who Alan Richman is and why he is in so many high grossing power house movies. He uses the data to show Alan is in all the Harry Potter and the first Die Hard movie.

Samuel L Jackson has twice as many movies as most actors. The only Actor with more movies than Samuel L Jackson John Wayne. Ha! Nice funny demo with good interplay.

Update 9:43

Our Demo is going to come from Real Data with Cresent/Powerview from BoxOfficieMojo.com, one of my favorite movie sites, and they are owned by IMDB.com.

He is showing the Cresent out lyers for what the number of movies and their sales in a scatter chart.   The top 2 outlyer's Computer Animation kids, and Comedy. He uses the highlighting function in PowerView, and in comedy Meet the Focker's is the #1 comedy of all time according to the data by sales & profit. He does a breakdown of the evolution of comedies by year and timeline.   He does a slicer that on different sales go tive us a card deck view by different profit margines.

He shows how profit margine increases as Hollywood adopted BI, and could look at data like this, drawing quite a few laughs.

He shows how when Toy Story launched from 1995 on computer animation ruled the roost. The most impressive thing is he did all that without touching the keyboard. PowerView is very dynamic on what the user can do with the data.

Update 9:38

Because Data Explorer is a service and the data follows us in the Cloud, this data is mobile and available very quickly.

tim and Nino leave the Stage and Ted returns. Just an FYI this is code named "Data Explorer" in Azure Labs.

Ted is talking about how this ties into Microsoft's Vision for Empouring all users through the tools they use every day.   We are discussing Self Service BI, how delivering this to the end user empowers them, and gives IT a greater roll in Governance.

He is building towards an anouncment, refrencing Cresent/Power View, PowerPivot, and mobile devices. Ted is talking about how he used his mobile Windows Phone to look over his slide deck for today last night while he was out getting a coffee.

He welcomes Amir Netz Technical Fellow to discuss Unlock New Insights, Anywhere. Ted is discussing what it means to be a Technical Fellow. Amir has a distingusihed career in BI. "He was in BI at Microsfot before we had a BI Stack."   With that Ted leaves the stage and turn's it over to Amir.

Update 9:31

They are now discussing what the datasets are that were brought in. There are Bing Services will add refrence data for phone books, they are overlaying the number of High Schools within a 1 mile radious because teenagers like frozen yogurt.

I get the demographic information, however people are laughing because a demo where you track High Schooler's and where they are is a little creepy.   I get it professionally, but they should have done a different age segment.

The point is they are showing how actual Service Calls to Microsoft Applications will be able to be used to provide demographic information to provide analysis on the Azure Market Place.

Now they are using PowerPivot to pull down the information and using Sharepoint to make this available to many different users. They were able to pull disparet datasources to determine the Shopping Centers that teenagers are most likely to shop at, and that is where the targeted location of the store should be.

Update 9:23

The demo is Hontoso Frozen Yogart to figure out the best location for their next store.   They got a SQL Azure Databae and got a normalized score about how their Stores are performing by profit.

They are extracting the data and they will use the Data Explorer to interface with that data, and then return highly relavent recommendations of what they should do next.

He added an Excel spreadsheet of a list of Shopping Centers for the area.   He hit a button called Mashup, that will overlay the data on top of one another and start making comparisions. There is a rank field called relative performance value.

Tim explains to us that Overlays are to the business world what Joins are for us DBA folk. The Azure Marketplace has recommended some data that would show demographic information that would help with the decision. Another Overlay/Join is done, and we now are starting to get recommendations.

We made a 3 way join against Excel, Azure, and the Marketplace in very little time.

Update 9:18

Ted is talking about how we integrate media from disparet sources. Data Quality Services, Family Data, reference Data, Weather & Climate, Health and wellness, and much more all available via the Azure Marketplace.

Microsoft's Vision, being able to Enrich your data with the world's data using "Decision Engines", Empower developers to build new services and applications, offer a Vibrant marketplace ecosystem for the World's Data.

Our next demo is SQL Data Explorer. He welcomes Tim Mllalieu and Nino Bice to the stage.   We are looking at SQL Azure Labs, it looks a lot like Windows 8 or the current Windows Phone.

Update 9:13

Scenerio

He is a web admin who wants to monitor his trafic. The solution, Hadoop on Windows Cluster. He starts showing us HiveQL a query that is similar to Java that will run against Hadoop on Windows Server.

The Hadoop console is basically the command line.   He's telling us about the millions of rows that his multi-node cluster is processing along.   He wants to figure out a better way to see this data. His connector of choice? PowerPivot for Excel using the Hadoop conectors that are available now.

He pulls it all down, and shows us one of the workbooks that are coming with PowerPivot Denali. He's joining this data against SQL Server, and Azure Market place data.

He's showing us the data about people hitting this website, by language.   He now switches over to Sharepoint to show us the server that he is running.   The report refresh took an hour, he is demoing how when you write a report and post it on Sharepoint, it will continue to refresh from disparet datasources and become something a business can rely on.

Ted Thanks Denny and Denny leaves the stage.

Update 9:08

Horton Works is taking the experts that help Google solve it's problems and brought them together. He believes that Hadoop could be storing 1/2 the world's data in 3 years.   He is discussing working with other companies and that hortonworks will be working to expand this use.

This is an Open Source Community project. Eric is very excited that they SQL Server community and all of our activitiy could be providing feed back to this project.

Eric Thanks us all and leaves the stage.

Now Denny Lee, a Principal Program Manager for the SQL Server team, is coming on stage next to discuss Activating New Types of Data.

Denny takes the stage and ask's if we are ready for some Demo's! YES! He says not yet.

Update 9:03

He is talking about participating in the Apache Program. To make sure that SQL interfaces with it in the best way.

Announcements

Apache Hadoop-based distribution for Windows Server and Windows Azure
ODBC Driver and Add-in for Excel, both for Apache Hive
JavaScript Framework for Hadoop
SQL Server and SQL Server Parallel Data Warehouse connectors for Apachee Hadoop

The other Announcement, they have formed a partnership with Horton Works. He just welcomed Erick Baldeschwieler the CEO of Horton Works to the stage to talk about the partnership.

Where would you see or know Hadoop, that is the back end for Google! This is big!

Update 8:58

He is pointing out

40% Data growth rate
15 out of 17 business sectors have more data sotred per company than the US Library of Congress

The vision going forward is in 3 points.

Manage and Process all types of data, mission criticle scale from on premises to cloud, Common management and development between SQL Server and SQL Azure.

We are discussing Big Data now. Here is how he is defining it.

Large Data Volumes
Traditional and non-traditional data sources
New technologies and New Economics
New Insights

He points out that Microsoft because of Search, Email, and all thier other offerings they have over 700 PB's of DATA!

Update 8:53

"We believe the Cloud is a hybrid work place. You will want to keep things in your data center on the ground, and there are things you will want in the Cloud." He just announced very slyly that they are MERGING the code base for the Cloud and Denali. A move forward in that is SSRS in the Cloud, and features that they are delivering in Denali.
They are delivering in 3 area's

Mission Criticle Confidence
Breathrough insight
Cloud on your terms

Those 3 area's in Denali AlwaysOn for High Availability, Column Store Indexes for Denali first introduced in PowerPivot, Cresent which will be Power View in Denali. Thoughts of the Cloud in what they do Juneau will be SQL Database Tools when Denali is released.

He points to some of the customers that have database's in production in Denali: Dell Pilot, Great Western Bank, and others.

DENALI HAS A NAME! SQL Server 2012

Update 8:48

Ted is talking about what we will cover today, Denali and demos.

He is Thanking PASS for all we do to spread the word about SQL Server. He points out that we offer over 400,000 Technical Hours, 79,000 Members, 300 Microsoft MVP's, and 233 SQL PASS Chapters. This is the largest PASS Summit Ever!

"It's been a busy year since we last got together." Yes it has. Choice is a big theme, he is pointing out the advances made in releases for Hardware Devices like Parallel Data Warehouse, the releases in SQL Server 2008 SP 3, 2008 R2 SP 1, the Cloud, Azure Marketplace, Management Portal for SQL Azure.

Update 8:43

He is thanking Microsoft Dell, CA, EMC, Expressor, Fusion-IO, and HP for being sponsors for the Summit. AND A BIG THANK YOU INDEED! We have the largest collection of vendors ever at a PASS Summit.

We are talking about the SQL Server MVP Deep Dives 2 Book that has been on sale exclusively at the Summit. Next week it opens up every where over 53 MVP's contributed to this. One of the great things about this book is that the MVP's do not recieve a dime for this book. Every penny spent goes to Operation Smile to provide dental work for children in 3rd world countries. Great Cause.

Rushabh's rapping up now, and introducing Sr. Vice President of Business Development Platform Division Ted Kummert, he owns all the applications from the Database, Application, and the way they integrate. From the Cloud to the Ground.

Ted said that SQL Server is the most widely adopted Database platform in the world, from Ground to Cloud. Quick dig at Oracle who just hit the cloud, and Microsoft has been ther 18 months already. Nice.

Update 8:38

A picture of our bloggers that are at the blogger table today is posted. a lot of amazing people, Denny Cherry, Brent Ozar, Andy Warren, Jorge Segarra, just an amazin number of people.

I'll try to get the full list out later with links to thier blogs and twitter handles.

We are talking about Twitter and the way that we use that so heavily, some of the # Hashtags, or search terms that we have created.

Right now he is posting information about the SQL CAT Team the clinic hours that they have open, and the different sessions that they have. At PASS this year we have.

93 MVP's
11 MCM's
57 Microsoft Employees
11 SQL CAT Presentations

The importiance of Community and building connections has been a common theme throughout the Summit. The First Timers Program and the session with Don Gabor (Awesome and I haven't done the recap yet), had over 800 people attend it yesterday evening until 8 pm.

Update 8:28

This year PASS has given 430,000 hours of free traing, we have 80,000 members and 1 Global Region. The goal is to get to 1 Million hours of technical training, 250,000 members and 5 Global Regions.

To help achieve this goal 3 international Board of Director seats were added to help influence this from Denmark, Germany, and I missed the third. PASS was a partner in SQL BITS in the UK and they felt that was a very large success.

Update 8:23

Rushabh Mehta the PASS President just took the stage. Apparently Twitter is tipping over!

Rushabh is thanking the Board of Directors, the Committie members, and making sure we know these are the people that we should approach with ideas.

PASS Summit Day 2: Monday Pre-Con

2011-10-11T08:41:00.002-04:00

Our instructor is Maciej Pilecki (@DrHouseofSQL). I’m familiar with Maciej because of MagicPASS, the SSUG I call home. He did a presentation to us on the memory cache back in June. It was amazing and I had promised to write a blog on that, and have not gotten that one published yet. However this one will go first and what a day it has been. Maciej is an MCM (Microsoft Certified Master), and a SQL Server MVP. He has presented at conferences like the PASS Summit, Tech Ed North America, and many many more.

We spent the first half of the day discussing the SQL OS and the way that SQL Server uses CPU’s. The second half of the day was all about Memory, and we’ve got some big changes coming in Denali. So of particular interest is not just how we work now, but how we are going to be working in Memory in the future.

WHAT IS THE SQL OS

SQL Server has it’s own Operating System. WHAT!?! Yep you heard me.

“But Balls”, you say, “You install SQL Server on Windows right?”

Yes Dear Reader you do! However Windows and the way that it handles processing is very different than how we would like processing to occur within SQL Server. Windows uses Pre-emptive Scheduling. Each Thread, work that is being done on your CPU/Processor, is given a quantum of time. Windows as an Operating System must manage that time and make sure everybody get’s a turn.

This is similar to having multiple sugar infused children, all of them run up to you wanting to tell you something. You let one go, and depending on how much they are jumping over one another you may need to stop one before they are done, and have another one start to tell their story. You’ll get back to the first one, but your job is to make sure everybody get’s a turn. Windows will stop your Threads as they are working before they are finished, they will let them have another turn, but they need to make sure all the kids get a chance to talk.

SQL OS is a Cooperative Scheduling OS, it is a bunch of orderly Kiddo’s lining up to take turns telling you a story. And if one needs to Wait and think they go to the back of the line and let the next one speak. They Cooperate to make sure that things happen as orderly as possible, but most importantly if you are telling your story, your Thread is working, no one stops it until it is ready to stop.

“But Balls”, you say, “When did this change occur, this is huge?”

Yes Dear Reader it is huge! But take heart we have all been using the SQL OS since version 7.0. It has been maturing over the years and we see a lot of benefits of that today in 2008 R2 and we will see even more in Denali. We spent the rest of the morning talking about how CPU’s work within the SQL OS, and I learned more that I have time to write. This was an incredible day of training, and I can’t wait to apply some of what I learned.

LUNCH

Some times you just have to smile at how things work out. At the lunch break I went down a little later than the rest of the group. I was walking down and I saw my friend Eric Wishdal (@EricWisdahl | Blog), and I asked if I could join him. Eric is a veteran of many SQL Saturday’s and PASS events in Florida. He is a great guy and has a very sharp wit. So Thousands of miles from home I meet up with a friend who is just up the road.

As we waited through the line and got our food, great meal and props to the folks at PASS, we arrived at the end and I struck up a conversation with Brent Ozar (@BrentO | Blog) and along walks Jeremiah Peschka (@Peschkaj | Blog).

If you are reading my blog more than likely you’ve heard of these guys, they’re kinda a big deal around here. We all sit down at a table as we are eating we are joined by Mike Hilwig (@mikehilwig | Blog). I met Mike at SQL Saturday 79 in South Florida, and he is awesome. It was really nice lunch with great company, as Stan Lee would say ‘Nuff Said!

MEMORY, RESOURCE GOVERNOR, AND EXTENDED EVENTS

We spent the second half of the day learning about how Memory and Buffers work and are managed within the SQL OS, how Resource Governor works internally, how you can bind SQL to multiple ports and affinity each port to a specific set of CPU’s, what Maciej called a poor man’s Resource Governor, looked at Extended Events, and some of the new features coming up in Denali.

This was an absolutely wonderful day, and I learned a lot about a portion of SQL that I had only just started to scratch the surface on.

The rest of the evening was spent writing and in the room. There is a lot to do this week, and I’m looking forward to some of the social activities tonight, but I wasn’t feeling up to going out. I got a pizza and sat down to do a lot of writing.

And now on to day 3!

Thanks,

Brad

Lesson 2: Internal Structures, Vardecimal, & Row Compression

2011-10-11T01:23:00.002-04:00

SQL University Compression Week: Lesson 1 About Compression

Welcome to Lesson 2 on Compression and welcome back Dear Reader. I want to thank the Chancellor of SQL University Jorge Segarra (@SQLChicken | Blog) for the opportunity to be your Professor of Compression. Yesterday we discussed What Kind of Compression SQL Server uses and Why you would want to use Compression. Today let’s get down and dirty with the first level of Compression. But first let’s do a history lesion.

Compression was first introduced in SQL 2005 SP2, as Vardecimal Compression. In SQL 2005 you would have to enable the Database for Compression and then enable the table that you wanted to Compress. The compression would only work on Numeric or Decimal data types.

If you enabled Vardecimal Compression it would store Decimal and Numeric data as a new data type, Vardecimal. But to understand what that data type does we need to talk about the Fixed Length portion of a Data Record.

LET’S START AT THE BEGINNING

When we talk about Compression we have to look at our data from the inside out. So I like to make a reverse pyramid. We understand when we create a table there is a hierarchy to it. Tables have Columns and Rows, Columns and Rows have fields. When you break it down to the smallest unit storage, you would think it is a field, but it’s a little bit different.

Instead of Tables, Columns, Rows, and Fields we have IAM Chains and Allocation Units, Allocation Bitmaps, Extents, Pages, and Records. What we care about with Row Compression are the Records. If you look up above you’ll see what I like to call my Mall map of SQL Internal Storage. And you are here in the Records section today. So let’s take a closer look at what that records looks like.

This is from a slide I have in my Compression talks. If you look above you’ll find the structure of a regular record as it is stored on a page. This is courtesy of Paul Randal (@PaulRandal | Blog) go and look at his Data Structures MCM video it will give you a deeper understanding of how SQL Storage works internally. A Record essentially equals a row of data in a table (the deeper you dive the trickier this get’s but stick with me Row=Record). The part that we care about the most is the Fixed Length Columns. If you declare a Decimal or Numeric data type and insert data into it, that would be stored on the Fixed Length Column of the Record. If you declare a decimal(38,38) and put a number, 0.12345678900123456789012345678902134567, it takes up the exact same amount of space as if you stored 0.1234. That is what a Fixed Length column means, regardless of what you use you take up all the space.

When Vardecimal Compression occurs it changes where the record is stored, it goes to the variable length storage instead of fixed. So it is altering how the storage of a particular record data type occurs. So now instead of 0.1234 taking up 38 bytes of data it only takes up 4 bytes of data, in other words we need only what we use.

SQL 2008 takes it a step further not only do you change how it is stored, but you change the physical storage of Data at a Record level. With Row Compression we added a lot more data types Smallint, int, bigint, bit, smallmoney, money, float, real, datetime, datetime2, datetimeoffset, char, binary, timestamp/rowversion. SQL 2008 R2 added Unicode Compression for nchar and nvarchar. So if it is fixed length and we Row Compress it we remove all the unused space, and here is how we store it.

Paul Randal is a busy man, he has another MCM video on New Database Structures in SQL Server 2008 (go watch it). Compression wasn’t the only new data structure we got with 2008 go have a watch and you’ll learn all about it.

So now that we know all about how Row Compression changes our storage, let’s play around with it.

DEMO

First we need to create our database that we will be using. I’m doing this in SQL 2008 R2, so to make sure your results match up I would suggest 2008 R2 as well, however if you have 2008 for this example we will be fine.

USE master;

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

BEGIN

DROP Database demoCOMPRESSION

END

CREATE DATABASE demoCOMPRESSION

USE demoCompression

So we’ve got our database our next step is to create a table and populate it with some records.

CREATE TABLE dbo.myTable1(

myID int IDENTITY(1,1)

,myChar CHAR(8000)

,CONSTRAINT PK_myTable1_myID

PRIMARY KEY CLUSTERED(myID)

);

Insert Some Records

DECLARE @i INT

SET @i=0

BEGIN TRAN

WHILE (@i<10000)

BEGIN

INSERT INTO dbo.myTable1(mychar)

VALUES('myData');

SET @i = @i +1

END

COMMIT TRAN

Now that we’ve got a Table and Data we have something to play with we need a baeline. Remember KNOW THY DATA! To know our data a little more I would suggest turning on STATISTICS IO & TIME then running a select. Now this is very figurative, if you have a more targeted work load or set of stored procedures you would want to test those by getting the statistics and query plans so you can see IF compression changes anything for the better or worse.

Do a select to look

at the CPU time and I/Os

Before Compression

SET STATISTICS IO ON

SET STATISTICS TIME ON

SELECT * FROM dbo.myTable1

At this point set those baselines to the side. I would use sp_estimate_data_compression_savings on the table specifying ROW Compression. This will take a 5% sample of the table move it into your tempdb and apply the Compression setting that you specify, in this case row. It will return the results and then give you an estimate based on that sample that tells you what it believes your compression rate will be.

sp_estimate_data_compression_savings 'dbo', 'myTable1', NULL, NULL, ROW ;

When I apply compression I like to use sp_spaceused to see the space the table takes up before and after.

How Big is our Clustered Index

sp_spaceused 'dbo.myTable1';

Rebuild Our Table with Compression

ALTER TABLE dbo.myTable1

REBUILD WITH (DATA_COMPRESSION = ROW);

How Big is our Clustered Index

sp_spaceused 'dbo.myTable1';

As you can see we reduced the table from around 80 MB to 288 KB. Now let’s run our query to get our comparison baseline and see how query performance was effected.

Do a select to look

at the CPU time and I/Os

After Compression

SET STATISTICS IO ON

SET STATISTICS TIME ON

SELECT * FROM dbo.myTable1

Home work, go watch Paul Randal’s MCM videos . Take the Row Compression Demo and add some more columns and Fixed Length Data Types to it, and see what you can Compress. Happy Compressing!

Thanks,

Brad

SQL University Compression Week: Lesson 1 About Compression

2011-10-10T10:17:00.003-04:00

Hello Dear Reader! Welcome to Lesson 1 on Compression. I want to thank the Chancellor of SQL University Jorge Segarra (@SQLChicken | Blog) for the opportunity to be your Professor of Compression. Before we talk about how to turn it on, where we should apply it, or even the types of compression that there are available to us there are two burning questions that we need to answer. What is Compression and Why should I use it?

A PICTURE IS WORTH 1000 WORDS, BUT I WOULDN’T’ TRUST IT WITH YOUR DATA

Let’s get a 40,000 foot overview of Compression. When the good folks at Microsoft were looking at adding Compression into SQL Server the first thing they had to answer was, What kind of Compression will we use? If you are new to the Compression game then you may not know that there are a lot of different ways to Compress Data. Sure they all do the same thing, reduce the overall size and foot print of the Data you are Compressing, but we use different types of Compression differently.

On one end of the Scale you have Compression that reduces the overall size of the file but it does so buy stripping out what it deems as unneeded or necessary data. The best example I can think for this is photography. A RAW photograph file is huge. But it contains every last bit of detail that your camera can capture. When you look at some of the breathtaking pictures that line the magazines of scenic landscapes, athletic accomplishment, or new events, those photographs were taken in a format to capture all of the data. When you browse USA Today on your mobile phone, or on your computer you receive this photograph in JPEG, GIF, or PNG format not in RAW. The reason it’s smaller.

“But Balls,” you say, “How did it get smaller?”

The answer Dear Reader is Compression. JPEG, GIF, and PNG are Compressed file formats for pictures. Why do we Compress pictures? Because the human eye can only detect so much detail. Some people can detect more than others, but eventually we hit a threshold were we cannot detect anything more. So the extra Data becomes unnecessary overhead at that point. So we strip it out.

Jim Grey went through the trouble to come up with ACID, We Need to Talk Your Database is on ACID, and Microsoft had adopted it into the core programming of SQL we can assume that they would not want to strip out data. As Database professionals we would hope that we are preserving our data, and we are.

So we know the kind of Compression that we will use must preserve our Data, the next question is how hard do we want to work for it? We could use something like Winzip, and Compress our files down to the lowest possible value, preserving our data and getting it really really small. However that only works if you aren’t planning on doing that every time you want to read it. Have you ever zipped a 2 GB file? It takes a little while. Have you ever unzipped a 2 GB file, takes a little while too. Imagine if every time you wanted to read your multi GB Database you had to sit through the same scenario.

We are still operating a Database, while reducing our Data footprint is laudable we wouldn’t want to do it at the expense of performance. As a matter of fact if at all possible we would want performance to increase not decrease.

I know we are picky and asking a lot, Shrink the data, but don’t loose any, make it smaller but easy to access, and if possible make it work faster (in some not in all cases). Microsoft listened and the particular flavor of Compression we got was Page & Row Compression.

The first stab at Compression actually came up in SQL 2005 SP2 and it was called Vardecimal Compression. It only worked on the Numeric and Decimal Data Types, but it used concepts that we will be reviewing when we discuss Row Compression. In SQL 2008 we got Page and Row Compression and Vardecimal Compression was immediately deprecated. You can still use Vardecimal Compression in 2008, but if you really want to use Compression you will get a lot more bang for your buck with Row Compression, more on that tomorrow.

WHY IN THE WIDE WIDE WORLD OF SPORTS SHOULD I USE COMPRESSION

http://www.flickr.com/photos/ifotbol/3928145113/

Now that we understand the basic idea of what Compression is, the most important question you will ask is Why should I use it! Disks are cheap. I hear it all the time. You’ve probably seen the ad’s for 1 TB hard drives for sale on Black Friday for $30. You are correct Disks are cheap and they are getting cheaper all the time, however, I bet you don’t go and buy the Hard drives for your server on Black Friday.

As a matter of fact if you have SAN, Shared Attached Network storage?, then you need to buy different disks altogether. You probably need to have a port in the back of them that allows for a HBA, Host Bus Adaptor, that takes a fiber cable. The reason? If you are using a SAN device you want the quickest possible connection for your database server. And those HBA ports raise the price of those hard drives. And if you are using Solid State hard drives on a SAN that price goes up even more. Now we are talking from 10’s of dollars to 100’s of dollars. And if you have a SAN your not going to have just one hard drive in there. You will have multiple drives striped in some sort of RAID Array. How cheap does it sound now?

But wait there’s more. If you have a lot of SAN’s that can get pretty complicated. Your probably going to need a SAN administrator. And you may need more than one if you are a big enough shop. So now we’re not just talking hardware but people as well.

All this for a database? One lowly single database? I would say most of us don’t have one Database to worry about. You probably have a Prod (Production) environment, maybe a UAT/Staging, a Test, a Dev (Development) or even a DR/COOP. You may have a Data warehouse as well. You could have multiple copies of your database for multiple projects.

And you probably don’t have just one Application you are supporting. Most places have 10’s, 100’s, and in some cases even 1,000’s of Database across just as many instances that they maintain. When you start thinking about the scope that your data touches, you start to realize just how much you have out there.

As Database professionals we try to normalize our data to prevent data duplication, but by nature of the change control process we will always have it. On top of that there are 3 performance bottle necks we normally encounter; CPU, Memory, and IO (Input/Output).

The option to re-write code is always there, but how often have you been in a situation where management decides to throw heavier hardware at a problem because it is cheaper and more cost effective than a re-write? If you haven’t been there you will eventually. You can toss more CPU on a server to get more processing power. More RAM to get more memory and to speed up operations because we can hold more in memory. But with Disk, if I throw more disk at a server I just get more storage room, it doesn’t help me with performance.

Page and Row Compression can do just that. The cost to the server is CPU cycles which most database servers have to spare. *Just a side note if your server is averaging around 60% CPU utilization or higher I would not recommend using Compression without thorough testing to ensure it would not reduce existing performance. We reduce the size of the structure not only on disk but in memory. Pages of a databases are compressed on DISK and in Memory. This is such an important concept I’m going to say it one more time, on DISK and in MEMORY! So not only do you save room, reduce the size of a table or index. Make it quicker to read off of disk because of a reduced size, but you take up less Page Cache because your Data Pages are compressed in memory.

So you can reduce your data foot print across multiple environments, possibly speed up performance by reducing the number of reads to process a query, and possibly use your memory cache more efficiently. I don’t think the question is why should I use Compression, but why am I not using compression.

WARNING: DANGER WILL ROBINSON

I’m not going to sell you a bill of goods, that is just snake oil. There are some serious things that you need to keep in mind when it comes to Compression.

Know Thy DATA!
Take Baselines
Know Thy DATA!

I like to think of Data Compression like Indexes. You wouldn’t just put an index on a table and never check to see if it is being used. You would check to see that the query plans you have are still running efficiently, and you would monitor your performance.

You need to do the same thing with Compression. Know how your queries perform before hand, know how they perform after Compression. If your CPU shoot’s up you will need to trouble shoot it, we’ll cover this later in the week. If your data types are not right, don’t compress them in the first place, later in the week as well.

HOME WORK

Your Assignment should you choose to accept it is to go read some of the Blogs by the SQL Server Storage Engine Team on the Topic of Compression, Sunil Agwilari is the Project Manager for the Engine Team and he has written a lot on this topic.

Data Compression: Why Do we need it?
Data compression techniques and trade offs

Thanks,

Brad

PASS Summit Day 1: Travel

2011-10-09T23:42:00.005-04:00

I woke up this morning around 2:45 AM. Having packed the day before I just had to get ready to take go and I was out the door. The drive in was a rainy one, but the nice thing about an early morning drive is that the roads were pretty empty so no traffic jams. So a nice smooth ride into the city, and other than getting a little SQL Karaoke practice in completely uneventful.

So now I'm in Tampa and about to board, so this will expand as the day goes along. But here is an early morning picture of beautiful Tampa from the airport! As you can see it is dark and just starting to come to life. The early morning work force is out, people heading home from vacations, and those of us bleary eyed travelers heading for many other destinations. So I'm going dark for the flight, I've got some compression topics to write on for SQL U next week.

A quick picture from the clouds (34,000 feet) and I'm all back to being quite again.

DALLAS FT. WORTH

I'll be less than half way through with the travel portion of the day by now. The big flight will be the almost 6 hours from Dallas to Seattle. I'm looking forward to landing already, I'm not sure how much adrenalin will continue to keep me going but I'm pretty sure I'll need a nap at some point today.

I love walking through airports, there is always something that is there to impress and there is always something cliché as well. I found both.

A quick coffee and bagel and I was off to the Concourse. I met up with some folks that are heading to the Summit as well. One is from the Tampa area and the other is Ed from Arkansas. We hit it off talking about the Pre-Con’s we would be attending and I found a friend to share a cab to the Westin.

Before you know it we were boarding again. The flight is pretty packed this time and I’m in the Isle so I would have thought no pictures from the plane window. However there was a very nice lady from Alaska sitting against the window and when I asked she was polite enough to take a picture as we were passing over the Rockies.

SEATTLE

Finally on the ground. Ed and I shared a cab ride over to the Westin, as they did not have a shuttle at the airport. The Summit is a wonderful opportunity to meet new people, as we come together to learn about SQL Server with one another, and it was fantastic to meet Ed. We agreed to meet up at the lobby and find some food, I was going on a bagel and some yogurt for the day, Ed was hard core and hadn’t had a thing.

While in the lobby I Tweeted the plan that we had, and quickly heard back from the SQL Dynamic Duo of Karla (@KarlaKay22 | Blog ) and Rodney Landrum (@SQLBeat | Blog). They were at the Tap House, which had some of the best jumbilia I’ve ever had.

From here I had a great time with new, old, and current friends. It is amazing how far you can travel in a day, to remember the things that you’ve already known. Several games of pool later, I still can’t play worth a damn. I have the occasional flashes of brilliance that peter out after a max of three good shots. But playing lousy pool is a lot of fun when you’ve got friends to play it with.

From there we did a walk about the city and looked at several different destinations. We headed over to the Convention Center and Registered for the reason we are all here. It was amazing to see all of the people that came to register early. A lot of them were people I had met before, and a couple whom I had been wanting to meet. I got to met Brent Ozar (@BrentO | Blog) who is taller and younger than I imagined. And I mean taller folks. I’m not a short guy, but I’m right on the 6 foot mark, so when people are taller than me I notice. And did I mention he looked younger than I expected J.

With that Rodney, Karla, and myself set off for a hole in the wall noodle shop for a quick fix of our favorite delicacies, I can never resist a good Pad-Thai. And with that I’m heading back to finish up some work on Compression. Time to get a good nights rest. Tomorrow starts the Pre-con’s.

Thanks,

Brad

Top 4 Reasons Next Week is a Big Week

2011-10-07T11:24:00.000-04:00

Hello Dear Reader, next week is a very big week for me SQL wise. Family wise I will be missing the heck out of my wife and my kiddos while I’m half a continent away. Just this last week my Bug lost another tooth, the baby got even cuter (don’t ask me how she does it constantly), my Big Guy got even bigger (seriously my 7 year old has pec’s), and my wife continues to be AWESOME, (the Bug and I had a conference and deliberated on this earlier in the week). So while I’m missing all the other Balls, I’ll be busy out of my mind.

“So Balls,” you say, “What’s keeping you so busy?”

No good way to say it other than list it.

1. My First PASS Summit

2. I’m sitting at the Bloggers Table at the PASS Summit

3. I’m Presenting TWO times at the PASS Summit

4. I’m the Professor of Compression Next Week For SQL University!

Any other week one of those items would keep me fully occupied. Next week I will be utterly consumed learning, teaching, meeting, and greeting. So let’s break it down a little.

MY FIRST PASS SUMMIT

http://www.sqlpass.org/summit/2011/# PHOTO CREDIT: Deepak Puri

This needs no introduction. If you are involved with SQL Server you know that the PASS Summit is the biggest SQL Server Conference in the World. Not North America, not Europe, not Asia, the WORLD. The brightest SQL Speakers in the World will be there. People from all over the Globe, experts from many many nations will be together in one spot. Top Microsoft Experts, the Cat Team, many Microsoft Developers for the SQL Server Team, you name it the PASS Summit has it.

I’ve wanted to go to this event since I started seeing advertisements for it on SQLSeverCentral.com, for the last 4 or 5 years. Getting to watch the streaming keynote last year just made my desire to go grow even more, and now here it is!

If you have never heard of the PASS Summit then go check out the current website. As if that isn’t enough I’m attending 2 of the great pre-cons that are being offered. I will be blogging like a mad man while I’m out there so keep checking back Dear Reader, this should be a fun week!

SITTING AT THE BLOGGERS TABLE

http://www.sqlpass.org/summit/2011/SummitContent.aspx PHOTO CREDIT: Pat Wright

I’ve blogged previously on The Top 5 Reasons You Should Be On Twitter, but I could add a 6^th as well. Opportunity. On Twitter it was announced that PASS was accepting applications to sit at the Bloggers table. I jumped. Then I emailed the contact listed and WAHOO I was selected! As a result I’ll have access to blog from a table of Blogger All-STAR’s and SQL MVP’s, and lil ol’ me J!

So not only will I be at the top event for SQL Server, I’ve got a front row seat. I’ll do live blogging of the Keynotes, and attempt a daily re-cap. As I snap pictures I’ll make sure to get them uploaded and attached to the blog.

PRESENTING TWICE AT THE PASS SUMMIT!

My wife’s best friend had a quote on her Facebook page that I feel describes me to a T, since she told me it I laughed and then I thought, “If I could only get that on a Family Crest”. …. The quote “I’ve got 2 speeds; Off & M@Ther F#(king ON”. That has been me my whole life, when I’m down I’m out. When I’m up and running, there’s no stopping.

During this week I’m presenting a Lightning Round Talk 24-a 5 minute Horror Story, and the Page & Row Compress Deep Dive. I’ve been working on a lot of great Demo’s that I can bring to the table. I presented the Deep Dive recently at SQL Saturday 85, and internally for my DBA team. I’ve gotten a lot of good feedback, and I’m really excited to give this a 3^rd time. The 5 minute Horror Story is a real life adventure, with appropriate details blurred, from Father’s day 2009. Show up to the session and you hear all about the wonderful world of a 24-7 on call DBA and why backups are so important. Not just that you take them, but if something goes wrong that you grab a fresh set.

PROFESSOR OF COMPRESSION FOR SQL UNIVERSITY

Jorge Segarra (@SQLChicken | Blog) is the Chancellor of SQL University. If you’ve never heard of SQL U then you should head over there and take a look. It is a web blog that collects the input from the experts in our field and has them write on a new topic a week.

Think college syllabus but for SQL, with different Topics, and written by MVP’s & Experts. I’m honored beyond belief to be among them, and starting next week I’ll have a Lead Up to the Deep Dive. I’m still writing and formulating as we speak and a lot of this will get finished up Sunday to Monday. But I’ll have a post a day on Compression. We’ll start up slow and work our way through a set of topics that will help you understand Compression and feel good about taking it to your work and finding ways you can apply it.

Alright Dear Reader the break from blogging is over, time to get to the grind stone and get working! I hope your week is a good one.

Thanks,

Brad

I’m On The NomCom! What’s a NomCom?

2011-10-07T06:44:00.003-04:00

Thisis a blog I’ve been wanting to write for a while, I’ve been a little backed upand with the 2011 PASS Summit starting next week, now seemed like the perfecttime.

http://www.flickr.com/photos/patrick-smith-photography/4525976964/

Back inAugust PASS, the Professional Associationof SQL Server, announced that they were seeking applications for the NomCom. I like to participate in things so I startedreading up on it.

I wasn’t as active at this time last year with PASS, I wason the ramp up to becoming more active, but I was still a couple weeks awayfrom attending my first SQL Saturday. So if you are not familiar with it there was a bit of controversy lastyear with the PASS elections and the NomCom. I’m not going to go into it, but I researched it before I submitted myapplication and it is pretty easy for you to find on the IntraWebs if you areinterested.

My participation on the NomCom is a direct result from lastyear. In order to put more transparencyinto the process that is the NomCom, it was decided that PASS membership shouldhave the majority of the seats. So I’m acommunity representative as well as Roy Ernest (@RumblingDBA | Blog) and ½ of my Future Law Firm of Bigun’s and Balls Newly Minted SQL MVP JackCorbett(@unclebiguns | Blog).

After reading over what the commitment would entail andspeaking with my family to make sure they were okay with it I put in an application.

“So Balls”, you say, “What in the name of SQL Server does aNomCom Do?”

Great question Dear Reader.

WHAT’S A NOMCOM

PASS has announced that there will be a Board of Electionsmeeting this year, to learn more visit the PASS 2011 Election Website. As part of the process people will fill outan application and submit to be considered for the election. There are some pre-qualifications that theyhave to meet, you have to be working with SQL Server (Sorry Larry Ellison).... youget the idea.

http://www.flickr.com/photos/mshahab/4935745220/

After all the applications that do not meet thepre-qualifications are taken out of the mix, the applications are then handedover to the NomCom. We review them weigh (fancy word for score) each based on aset of criteria that we received from the current Board of Directors. We will interview the references and eventually we will interview theapplicants and weigh those interviews as well.

We will use these scores to form a rank, and then we willpass these candidates along to the Board, passing along the top number that theBoard Requests to be considered as the slate for elections. The board will review everything we havegiven to them. If they are not satisfiedwith the slate of candidates we put forward. They will push it back to us and give us newinstructions.

So to make a long answer short, What does a NomCom do? We are nothing more than a NominatingCommittee, that ranks the applicants as best we can and hands that over to thecurrent Board of Directors. We will bedoing a lot of heavy lifting.

Just a quick clarification about the criteria used to rankcandidates. We have been meeting forweeks reviewing what the Board has given us and asking for clarification whereneeded, and disagreeing with criteria where needed. It has been a very civil and easyprocess. Everybody wants the communityto vote and decide who is best to serve on the PASS Board, and we are doing ourabsolute best to make sure that who the community wants they will get.

So basically, I have no power, and if people are unhappywith the results I will probably get a lot of people talking to me through theIntraWebs USING CAPS!!!!

WHY WOULD YOU DO THIS TO YOURSELF

http://www.flickr.com/photos/yorkie/188523375/

Well I do enjoy a good roller coaster. And to be quite honest it felt like the rightthing to do. I’m not running for theBoard of Directors. Personally I don’tfeel like I have the experience yet to where that would be a good fit for PASSor for me. The election process thoughis a very important one, and it is one I believe deeply in for better or forworse.

America doesn’t ask much of you, but it does ask that youvote. (okay it wants those pesky taxestoo, but nobody likes them.) You have achance to shape the world around you every four years. And for PASS you get that option a littlemore often. I’ve gotten to meet a lotof the Board of Directors. I like them,they are nice people, and they are very knowledgeable in SQL Server which givesus a common bond immediately.

But when I look at the SQL World around us I want to get more involved and help change things for the better. We've done a lot of good work, but there is always more that needs some doing.

I’m not a deep insider. I still grin like a kid with a backstage pass when I look around at theplaces and people that I’m going to work with. Getting to hang out with guys like Jack and Roy still makes me grin earto ear. As if that isn’t enough roundingout the NomCom is Thomas Larock(@SQLRockstar| Blog) and the Immediate Past Presidentof PASS Wayne Snyder (@SQLWayne ).

I’ll be writing more as the process moves along. I’ve seen several people on Twitterannouncing their intention to run for the board and I’m looking forward tothis.

Thanks,

Brad

SQL Saturday 85 – Thank You Everyone!

2011-10-02T21:43:00.000-04:00

About a week ago we had SQL Saturday 85 in beautiful OrlandoFL. I wanted to do a quick recap and saya BIG Thank You to everyone who participated! From the wonderful people on the planning team Andy Warren (@SQLAndy | Blog), Shawn McGehee (@SQLShawn | Blog), Karla Landrum (@karlakay22 | Blog), the O’Fearless Leaderof MagicPASS Kendal Van Dyke (@SQLDBA|Blog), to 41 Different Speakers, tothe Event Volunteers, to the Attendees, to the Sponsors, and to the great folksat Seminole State College who allowus to use the beautiful facilities, Thank YOU!

“So Balls,” yousay, “What was the best part of the day?”

Well Dear Reader, I would have to say the pictures that Itook, and those that I wish I had taken.

The Day Started out with an early wake up call. I the Dodge Durango was so loaded with drinksfor the event that it was actually sagging, (I wish I had a picture of that). I drove over to Seminole StateCollege and the Volunteers were already there and the tables were set up. We quickly unloaded the drinks, and Ireturned to the hotel to put the projectors and screens from our Pre-Con’s intothe back of the truck.

Between the two trips I saw that we had the road signs updirecting people in, and that we even had a volunteer standing on the Ellipsis toensure people drove the right way. Ourvolunteers are amazing! When you standoutside in the Florida sunshine, for hours, to direct people on an Ellipsis soothers can learn about SQL Server, then that is above and beyond dedication (Iwish I had a picture of that).

And it was off to the races. Making sure people knew where they were going. Walking the halls to make sure that it wasapparent which way to go, and putting up signs when it wasn’t. Taking pictures of the Speakers while theywere entertaining our attendees. AndTweeting more than I do in most weeks. Ididn’t get to attend many sessions. Iwas in my buddy Dan Taylor’s Session, the Deep Dive on Compression that thisBradley Ball guy did (not bad needs some work), and in Mike Davis’s SSIS vs.T-SQL Smackdown.

This was a very gratifying perspective however. Normally I'm here just to learn, but this was completely different. I was there to Serve. To make sure that our attendees made it to where they wanted to go, to make sure that our sponsors had power outlets, to make sure we had signs, to find the correct speaker room, and occasionally to join into the fray and do some speaking.

This was a very different perspective indeed, and as much as I love learning about SQL today it was learning about all the time and hard work that goes into the great events. Learning about the magic that goes on while we slide from one session to another. Thinking about the event in review I'm still left in awe at all the hard work that our volunteers achieved.

So now without further ado, let’s get to some pictures.

SESSION PICTURESFROM 8:30 am to 9:30 am

Erin Stellato (@erinstellato | Blog) You Are My Density: Making Sense of Statistics

William E. Pearson III Getting Started with MDX

Mike Antonovich Introduction to PowerPivot

Andy Warren Building a Professional Development Plan.

Don Stevic The Fundamentals of DBA-Ism

The One & Only Buck Woody From Ground to Cloud for Databases

Bradley Schacht Introduction to SSIS

SESSION PICTURESFROM 9:45 am to 10:45 am

My Buddy Dan Taylor on Useful tools for a DBA or Database Developer

Janis Griffin on Performance Management 2008 MDW-How & Why

Adam Jorgensen Analysis 0 to Cube & PowerPivot

Michael Wells Rapidly Deploying SQL Server with Powershell

Eric Wisdahl SSIS Data Flow Buffer Breakdown

Kendal Van Dyke Working with XML in SQL Server

SESSION PICTURESFROM 11:00 am to 12:00 pm

Nathan Heaivilin Introduction to Execution Plans

Phillip Rosen Biz Intel Dashboards w/ Excel OLAP formulas

Ronald Dameron Powershell by Example

Rob Collie PowerPivot BI & Massive Data Analysis for Humans

Mike Davis Using Script Task and Components in SSIS

Craig Purnell Upgrade Roadmap Taking YOU to R2!

Rodney Landrum T-SQL Alphabet Soup

BRADLEY BALL PAGE & ROW COMPRESSION DEEP DIVE 1:30pm to 2:30 pm

I was up during this time slot so I didn’t get to make itaround to all the other Presenters. There were a lot of great people presenting in this time slot, and Ihear we all did great. I had a nicegroup for my first Deep Dive at a SQL Saturday. This was on Compression, and I got a great question that will deserve afuture blog on the bit structure of nvarchar compressed records.

I got a lot of great feed back, it was positive which isnice. But I think all of the folks thatmade it realized that the second time I present this session it will be at thePASS Summit. So even though it waspositive, I got a lot of good suggestions as far as, expand here, maybe skipthis and make more time for this. Ireally like that feedback because it matched how I felt.

As a presenter I like to tell a story. Even though we are talking technology and itisn’t hot and sexy like the movies, we can still have a good story with a solidflow. I realized I have a little work todo on my story, and it was better to learn that now than at the Summit. There were a lot of great speakers that madeit to my session, Buck Woody (@buckwoody| Blog) stopped by to play the Piano, Eric Wisdahl(@EricWisdahl| Blog), and Craig Purnell (@CraigPurnell | AboutMe) came by to help expand thecast.

I feel really solid aboutthe presentation and I’m looking forward to the Summit!

SESSION PICTURESFROM 2:45 pm to 3:45 pm

Rob Volk Revenge: The SQL!

Patrick Thompson Where to with NoSQL

James McAuliffe SSIShare The Code Man

William E. Pearson III Becoming DAX: An Introduction....

Kevin Boles Parallel Query Execution Deep Dive

Jeff Garbus Rewriting Bad Code

Herve Roggero SQL Azure Performance Considerations

SESSION FROM 4:00pm to 5:00 pm

Once again I wasn’t able to run around, I was a part of theshow this time around! I was the refereefor the SSIS vs. T-SQL Smackdown between Adam Jorgensen(@adam_jorgensen|Blog) and Mike Davis (@MikeDavisSQL | Blog). As always it was a great battle between twoIntellectual power houses.

Mike Davis was displaying how he could fast load a datawarehouse using SSIS, and Adam was doing the same using T-SQL. Both competitors did a great job of layethingthe Smackdown on the data in the form of ETL processes. Mike showed how versatile SSIS is with theability to switch data sources and interface with multiple Database platforms. Adam showed how versatile T-SQL is, tossingout Merges, CTE’s, and old pictures of Mike in the Navy like they were goingout of style.

At the end of the bout I spoke briefly with Vince McMahon(WWE) and took a poll of the audience. I would like to say that the results surprisedme, but Adam had managed to really get Mike’s goat, or was the goat Brian’s (@brianknight | Blog)? Regardless, there was a goat, fisticuffs,Adam was pronounced the winner, and then I was viciously and savagely attackedby Mike. No sooner had he left the roomthen he returned and we all took a bow.

It is an amazing thing to watch professionals that are sogood at what they do that they can have fun doing it. It was something else all together to be apart of the action, and it was a lot of fun. I hear Mike is having a Smackdown in Tampa for SQL Saturday 86. I’ll be there doing my presentation onCompression. I wonder if they have steelchairs there…..

AND WITH THATLADIES AND GENTLEMEN I BID YOU GOODNIGHT

When I was kid I remember sitting in the 6^thgrade watching some cheesy mandatory movie educating us on some subject that mymemory doesn’t care to retain, and one line stood out to me. “Take a picture and remember it in yourheart.”

I laughed about itwith my friends. I believe the phrase “howlame” was uttered, but for some reason it always stuck with me. I take a lot of mental pictures as I go aboutthe day. Some end up in the recycle binjust as fast as they hit the desktop, others never leave.

For years to come I will remember meeting Buck Woody andgetting to drive him to buy Orange Juice, looking at the speaker dinner roomand smiling at all the great people that come together to share a passion, theBBQ Line at lunch, running the halls to take pictures and Tweet about each presenteras they were working their magic, my buddy Dan Taylor’s (@DBABulldog | Blog) first presentation ever, forcingKyle Walker (@WalkerSQLRanger)to get on Twitter, getting to meet Craig Purnell and his wonderful wife Julie,talking with my friend Adam Jorgensen, and having the SQL SMACKDOWN layethed onme by Mike Davis.

Less than a year ago, I walked into my first SQL Saturday inOrlando. I left with a feeling that Ihad come home. I wanted to dive right inwith both feet, and it helped me to grow in ways that I hadn’t even realized Ineeded to grow. So I found it strange to have come full circle, and this year Iwas one of the guys standing up top, getting to toss t-shirts out to the crowd. Andy Warren looked at me and asked how I felt about helping putting something together like this, I just smiled and said it felt Awesome.

We wound up the night at Liam’s Fitzpatrick’s, and I got tospeak with people I’ve connected with over the last year. Eddie Wuerch (@EddieW| Blog) from SQL Rally, Craig Prunell from earlier in the week at MagicPASS, Ron Dameron(@RonDBA | Blog) from SQL Saturday 62 inTampa, Don Stevic(@SQL_Superman| Blog) from SQL Saturday 74 & 79, Nathan Heaivilinfrom last year at SQL Saturday 49 (and 62), Bryan K Mcdonald (@BrianKMcDonald | Blog) from just this week.

To everyone I mentioned Thank You, to everyone I didn’tThank You, Volunteers, Speakers, Sponsors, Attendees, and my fellow TeamMembers Thank You, Thank You, Thank You. And to my friend Jack Corbett (@unclebiguns | Blog), newly minted SQL Server MVP, Thank You for yourrecommendation, your faith, and for the encouragement to get involved!

I hope I see you all next year!

Thanks Again,

Brad

SQL Saturday 85 Pre-Cons

2011-09-23T14:28:00.001-04:00

We are a little over half way through the Pre Confrence Sessions for SQL Saturday 85 and I couldn't be happier.

We are located at the Marriott in beautiful Lake Mary Florida which is just on the north end of Orlando.

Tommorrow is the big day where we will be coming to you live from the campus of Seminole State College.

The Marriott is beautiful the staff has been wonderful, and the lunch that they served was absolutely delicious. The pictures that I'm showing were all taken by me onsite, and I'll grab a few more as this is just a beautiful location for us today.

"So Balls," you say, "How's it going?"

So as an orginizer I have spent very little time in the room with the guys today, which is bad for me, but absolutely wonderful for out attendees. I will be trying to sneak into a room and take some notes at some point but unlike most of my blogs on events this one (unless I get some notes I can take), will be very non-technical.

So I am going to make an effort to get into each session and get some details so you know just what these experts are doing.

BUCK WOODY

Buck has a pretty packed room but I will make it in there and do some learning. Buck is a master at this field and the feedback I've been getting from people has been incredible. So more on this later.

*****Update*******

You can tell he is a Professor when he walks the room, he has a Commanding presence and a Tricksters eyes. I liked him before I met him but seeing him in action I like Buck Woody even more.

The little bit I got to see Buck was teaching all about indexes. Buck’s advice stay away from the hard drive as much as you can. Sucketh Mightely is a technical term and Indexes are great for reads and Sucketh Mightely on writes.

Buck goes on to discuss Indexes and their physical B-Tree structure. He recommends applying 3 rules to Clustered Indexes; 1. The should be Narrow, 2. They should be Unique, 3. They should be ever Increasing. We discussed the difference between Unique Clustered Indexes, and Primary Key Clustered Indexes, and Yes there is a difference!

One of the most interesting things he said with regards to the use of Primary keys and Foreign Keys, you can have a Unique Clustered Index and a Primary Key Clustered Index that have the same constraints. The Algebrizer will use these constraints in the same way when creating the plan to retrieve the physical data. However, deeper in the Algebrizer process the Primary Keys and Foreign key values will assist in computations that will assist the Optimizer.

In other words using Primary Keys & Foreign Keys can make SQL work faster for you. You should be using them. He waded into the Holy war that is Surrogate Key’s vs. Natural Keys. And then he assigned the Audience some hands on in class room work, finding usage statistics and table sizes using only the Built in reports.

I’ve got more Dear Reader and an further update will be coming. Now on to the Pragmatic Works Pre-Con.

PRAGMATIC WORKS

Don't let the picture fool you these guys are such consimate proffessionals, they work hard they play hard but when the guy with the camera isn't in the room they are all business. Once again I'm hearing some great stuff from out attendees and I'll try to get the low-down a little later.

WRAP UP

I'll get to the wrap up a little later. This is just the teaser for now.

Thanks,

Brad

SQL Saturday 85: Pragmatic Works ONE NIGHT ONLY

2011-09-21T13:04:00.001-04:00

We are in the final countdown to SQL Saturday 85, and everything is starting to come together. Yesterday we discussed the DBA Pre-Con for SQL Saturday 85. Today we will discuss the men from Pragmatic Works and the All Day Business Intelligence Workshop.

“So Balls,” you say, “Give me One reason that I should attend the Business Intelligence Workshop?”

I can’t give you one Dear Reader, but I can give you four.

ALLOW ME TO INTRODUCTE OUR TEAM

Mike Davis (@MikeDavisSQL | Blog) is a Sr. Consultant for Pragmatic Works. Mike has been using SSIS to help people all over the US. Mike has spoken at countless PASS events, and will be a speaker for the upcoming PASS Summit 2011. Have I mentioned the books that he has written? Also don’t miss out on his SQL Smackdown vs SSIS Session at SQL Saturday 85, how do you know you’ve got a great instructor? When they know a technology so well they can have fun in any setting and still teach you at a deep technical level.

Bradley Schacht (@BradleySchacht| Blog) is a Consultant for Pragmatic works. I met Brad at SQL Saturday 79 down in South Florida. Brad has presented at multiple PASS events, Code Camps, SQL Lunch, and SQL Server User groups. If you’ve been on BIDN.com you may have run into him helping in the forums or you may have ran into him in SQL Server Central.com. did I mention the book that he co-wrote as well, SharePoint 2010 Business Intelligence 24-Hour Trainer.

Kyle Walker (Blog) is a Consultant for Pragmatic Works. Kyle has been very active in the community and is an active blogger on on BIDN.com. Kyle has presented for the Free Webinar Series that Pragmatic Works does, WEEKLY, as well as appearing at SQL Saturdays, and SQL Server User Groups.

And last but certainly not least is Jorge Segarra (@SQLChicken | Blog). Jorge is a Consultant for Pragmatic Works, that means that he doesn’t just preach BI, he uses BI. In his spare time he’s the Dean of SQL University, which assembles the experts in our field to blog in a Semester Schedule for you to learn, did I mention that he is a Microsoft MVP for SQL Server? Perhaps we could talk about his book, THE BOOK, on Policy-Based Management for SQL Server, or that he will be writing the SQL Bible for the newest version of SQL Server Denali.

ONE WORD TWO SYLABLLES

These are some of the top guys in their field, covering the full Microsoft BI Stack from Beginning to End. In a intimate setting were you will get a lot of one on one time, and the best part Dear Reader?

When you have a group of instructors that know their material inside and out, and you have a setting like this you may learn some tips and tricks, because it is in the venues like this where you get to have the professionals go off script. You learn what they learn. You hear about their experiences in the field.

And maybe just maybe you find out some previews. With Denali barrelling down upon us, and all of the major BI Initiatives that Microsoft will be offering up, this would be a good chance to ask some questions, and maybe see what our Professionals are running on thier laptops.

This is a day that you will not want to miss out on, Click Here to Sign Up for the BI Full Day Pre-con! I hope to see you there, because this is going to be BAD@$$!

Thanks,

Brad

SQLBalls

I’m Going to SQL RALLY!

SQL Rally 2012 Vote For Me!

Pragmatic Works Has Balls...SQLBalls

Thank You & Goodbye Publix

PUBLIX

THE PEOPLE

Thank You

HERE’S YOUR CHANCE

Nothing Changes If You Don't Volunteer

You Should Never Use Backup Log Truncate_Only Except...

What Opportunity Are You Looking For?

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

Tales from the Query Processor

SQL University Lesson 3 Page Compression

Lesson 2: Internal Structures, Vardecimal, & Row Compression

SQL University Compression Week: Lesson 1 About Compression

Day 6: Friday PASS Summit 2011 Keynote Live

Day 5: Thursday Summit Keynotes 2011, PRIVATE OLTP Cloud Appliance Announced!

Day 4 Summit Keynotes Live, DENALI HAS A NAME! SQL Server 2012!

PASS Summit Day 2: Monday Pre-Con

Lesson 2: Internal Structures, Vardecimal, & Row Compression

SQL University Compression Week: Lesson 1 About Compression

SQL University Compression Week: Lesson 1 About Compression

WHY IN THE WIDE WIDE WORLD OF SPORTS SHOULD I USE COMPRESSION

WARNING: DANGER WILL ROBINSON

Data Compression: Why Do we need it?Data compression techniques and trade offs

Data Compression: Why Do we need it?

Data compression techniques and trade offs

PASS Summit Day 1: Travel

Top 4 Reasons Next Week is a Big Week

MY FIRST PASS SUMMIT

SITTING AT THE BLOGGERS TABLE

PRESENTING TWICE AT THE PASS SUMMIT!

PROFESSOR OF COMPRESSION FOR SQL UNIVERSITY

I’m On The NomCom! What’s a NomCom?

SQL Saturday 85 – Thank You Everyone!

SQL Saturday 85 Pre-Cons

SQL Saturday 85: Pragmatic Works ONE NIGHT ONLY

Data Compression: Why Do we need it?
Data compression techniques and trade offs