Monthly Archives: June 2009

Best Thing I learned at SQL PASS

PASS is running a contest to find out the Best thing you learned at the PASS Summit.  This could win you free registration or a free hotel at Summit 2009.    I wanted to share the best thing’s I have learned at the summit. 

Best Technical Lesson

My best technical lesson was at my first summit I took a Pre-con from Kimberly Tripp.  It was on managing the DB and internals for SQL Server.  Anyone that has  taken one of Kimberly’s sessions knows it’s about so much more.  It always amazes me to see her present.  She speaks fast but concise it’s like she has millions of rows of data stuck in her and she knows just the queries in her head to get the right information to send out. Considering her knowledge of Indexes and SQL Internals this might not be far from the truth. 

During this sessions I learned what system tables were and where they were located and about the internals of how Indexes actually worked.  This has been the one thing that’s helped me more than anything else technically. I continue to learn and be thrilled with how the internals work because of this session.  I spent the next few weeks after pass playing around inside the system tables.  I’ve spent the last few years continuing to read and understand the internal workings of the optimizer and the engine so that I understand SQL Server better.  In a recent project we designed a new schema for a new project we were working on.  Because of this knowledge I had learned I was able to map out indexes on tables based on the queries I had seen from the developer.  When it came time for the app to start running and testing we couldn’t locate any missing indexes in the model for what the system was doing.  Here is some more reasons I attend the summit. 

The Reason I attend the Summit

There is a lot of great technical content at PASS and I personally feel you can’t get a better set of technical DBA style talks anywhere at one time.  But this is not the reason I attend the summit.  I attend because of a “Fruity” drink that I had in Sept of 2004 at dinner with 2 people I had never met before.  I attend because I was given a chance to wear a Walmart vest and help other people at the conference.   I attend because I sat down on a Wednesday morning for the first time at a breakfast table with a bunch people I didn’t know. 

These are all experiences I have had because of PASS.   I still find new things every year that when I attend.  New people to help and in New ways. My goal with attending the summit every year is to help someone to become a better DBA/Developer/Admin.  I do this because I know in doing so it will make me better at what I do.   So my goal each year since my first summit has been to help others to get the most out of the summit. 

One Story

So my good friend Thom (aka SQL Rockstar) interviewed me recently as a PASS Board of Director.  Great Blog article by him go read it if you haven’t already.  If you have well read it again.  :)  An additional story of that first week was Dinner that first night.  Allen, Thom and Myself all agreed to get dinner somewhere as the conference got over that day.  This was before we had tons of Vendor dinners or volunteer Parties to attend to. The Gaylord Palms hotel was a city all in itself and had a large boat in the middle of the atrium area that served as a restaurant. So we decided on the Boat Restaurant inside the hotel.  

We ordered some drinks with dinner the one drink that stood out was mine(Thom and Allen had beer).  I have a tendency to like Mai Tai’s and anything in the Rum/Caribbean type of alcohol.  So naturally I got something from the menu that was a little “Fruity”.  Took Thom a few years to truly give me flack about my Mai Tai habit which lead to a interesting evening in  Denver. I digress that’s another story for another time. 

I remember during the conversation we really just discussed different ways to do things with SQL Server. We discussed volunteering and running chapters (I ran my local chapter at the time).  Overall it helped to set the stage for me to want to continue to volunteer and help others in PASS.  Just knowing that I could meet people like this and learn from them and them learn from me was a huge advantage to me and made me keep wanting to come back.  I’ve attended many conferences for the first time since this first PASS summit. None have had an impact on me like this one. 

So what is the best reason to attend the summit.  As any consultant will tell you “It Depends”. The Best reason for you to attend the PASS Summit might be to meet others and build a network or it might be to find a great technical tidbit you didn’t know.  You can find lots of reasons here on the PASS Site. 

Whatever the reason might be I know one thing is 100% certain you will have people there willing to help and maybe even share a “Fruity” drink with. 

Advertisements

Stranded on an Island……Working?

Ok so an idea like this could only come from the warped mind of Tim Ford (@sqlagentman).  Since I’m actively Blogging again I’ve decided to respond to this one. Thank you Tjay for tagging me.  

So here’s a story Of a lovely lady….oh wait sorry…Forget that. 

Details

“So You’re On A Deserted Island With WiFi and you’re still on the clock at work. Okay, so not a very good situational exercise here, but let’s roll with it; we’ll call it a virtual deserted island. Perhaps what I should simply ask is if you had a month without any walk-up work, no projects due, no performance issues that require you to devote time from anything other than a wishlist of items you’ve been wanting to get accomplished at work but keep getting pulled away from I ask this question: what would be the top items that would get your attention?”

First and foremost I would pour myself a Virtual Mai Tai.  This is an island and no one can yell at me about my fruity drinks here.  As a matter of fact let’s just pour 2 for fun. 

After consuming one(this won’t take me long).  I would sit down with the next drink and start to look at much of the architecture of my current data world I live in.  I have recently changed companies and positions leaving behind a Operational world that I knew very well.  For the better part of 10 years I have been on a call, on call, or in a call for most of those 10 years.  For that time I managed and worked with many systems, some I loathed and some I loved.  I’ve moved into the role of designing those very systems now from the ground up.  I would spend my first little while on the island remembering many of the things I learned in the operational world and trying to catalog them into my head(maybe writing them down).  So I can build my systems in a way that others can avoid the problems I faced. 

I would then (this is after 2 more Mai Tai’s of course) Start to look at  my current company and current data needs and start to find where data is needed and how it is needed. I need to find ways to help the masses get to it efficiently and quickly.  While keeping in mind the needs of the DBA’s/System admins that some day will be managing this system and hopefully don’t have to answer a call while hiking in Zion’s National Park (yes you can get cell coverage). 

The second to last thing (after consuming more Mai Tai’s) I would do on the virtual island is to look to the future and see how my data system can be placed into the Cloud.  This is something I both trust and fear all at the same time.  It’s one of those great technologies that you say can both help us and hurt us at the same time.  Something I’m watching very closely and how it will effect the needs of the data systems. 

The Last thing I would do is of course take a Photo Walk around my virtual Island.  I would of course invite Tim Ford to this if he can find a way to fly out to my virtual island if not I would do my best to capture the essence of the bits and bytes of my virtual island. Although I might need a tripod to get any steady shots because at this point I’m most likely drunk. 

Since I’ve had to much to drink during this blog post I will not bother to Tag anyone else as I will most likely end up tagging the wrong people.  🙂

Pat

Quick Testing with Fusion IO Card

Some of you have probably heard of the Fusion IO Cards.  If not you can check out more about them here.  I got one of these for a very short demo  yesterday.  I had 1 day with this card and I was only informed of this the night before as well so it made testing difficult.  I had a simple goal in mind for this card which helped me to keep testing to a minimum.

My Goal for these cards were to place Tempdb on the card.  Since every join/hash table/query execution hits tempdb I see this as a quick way to gain some performance without moving around lot’s of Databases.  All I had at my disposal for this quick test was a server with 3 drives setup in Raid 5 housing the C and basically everything else.  When life gives you lemons Call @sqlagentman and @sqlrockstar and make Margarita’s! 

So I wanted to test the write performance since that was something i could quickly setup and test.  I setup a simple loop that created 1 million row temp tables by pulling data out of another table that I had that had 12 million rows.  The loop would Create the table insert the data then drop the table.  I decided I would do this in loops of 10 and watch the disk queue length during this time. 

The First run with Tempdb on the Raid 5 with 3 Drives(2 effective).  Ran the test in 1:19 seconds hit a Max que Length of 253 and Averaged a que length of 53. 

I then Stopped SQL moved over tempdb to the Fusion drive and re-ran.  Same test same loop It took 21 seconds and only got to a max Que length of 1 and Averaged 0.  Much less of a hit.

I wanted to see if I could get higher Que lengths so I switched it to 10 million row tables and ran it again.  This time it took 2:54 seconds actually hit a Max disk que of 13 and averaged about 1 for the disk que length. 

At this point I really wish I could get some more time and setup another set of drives to do more dedicated tests but that’s just not in the cards for me right now.  I would still suggest based off these numbers having such a high level of difference that if you can look into these cards as a hardware performance tuning option.  I know I am suggesting to purchase one and when I do get it in house I’ll have time to do more tests. 

If you also want to look at SSD(Solid State Drives) vs Hard Drives @paulRandal referenced a great blog post from James Hamilton on the differences you can find it here.

Reasons for NOT using Varchar (MAX)

Let me start by saying I am not against using Varchar (MAX). I love the idea of it, it has greatly simplified the use of LOB data types in my opinion.  I am simply listing out some points I used recently to not use Varchar (MAX) . I recently reviewed a DB structure and found that ALL the Varchar fields were set to Varchar (MAX).  Things like Name, Username(I would be very interested to see a 8GB username),  City, etc…   I’m not one to tell someone though you have to change your design “just because”  I said so.  I wanted some reasons.  I posted the question on Twitter and looked around online for some good reasons here are the ones I ultimately ended up using. 

1. Adding a Text Data type / Varchar (MAX) to a table removes the ability for online re-indexing. –Thanks @paulRandal

2. Performance Degradation.  This goes back to another point about only get what you need.  Their was Varchar(Max) on almost every table adding overhead to that many tables causes Performance Pain.  Thanks @AaronThehobt for pointing that out. 

3.  Poor Design Practice.  In general you should only be grabbing what you need.  Creating a field that can store just about any amount of data just to hold 100 characters is simply a wasteful operation.  I would suggest finding out what the business need/ logical need for the field and then use that to define the structure.  Thanks @peschkaj for bringing the design aspect up.

So hopefully this will help you to convince someone else in the future to avoid using this as a catch all for any string based field.  Feel free to add comments with other suggestions for reasons NOT to use it.