Sql Server Insane Asylum

PASS Pictures

2009-11-16T16:58:00.001-07:00

I had a wonderful time at the PASS Summit. As I blogged about here I chose a different approach to the summit to try and capture more of the people and carrying my camera all the time with me.

I have hopefully captured enough photo’s that everyone can have a small bit of that fever we all get while at the summit. I have been really happy with how many pictures have come out of the summit in general I’ve tried to add some links below of everyone’s pictures I’m aware of. Some may be in Face book which might require you to be friends with the person other’s are in Flickr where pretty much everyone can see them. I’ve put mine in both to make it easier for people to find. I would suggest if you would just like to browse the pictures Flickr is a much better way to go given how slow and buggy Facebook is. But if you would like to comment and tag your friends then Facebook is the better way to go. :)

Here’s the overall Stats

On Sunday I took about 700 Pictures while out on the Olympic peninsula this was a family Photo shoot I was doing for some good friends of mine. I was also enjoying the beautiful scenery out on the Peninsula something I will hopefully be able to do again soon.

Mon-Friday About 900 pictures total. Just short of 1000K. I’ll have to make sure to up that number next year. Here is a link to all my pictures via Flickr and Facebook. No not all 900+ made the cut.

My Photowalk Pictures on Flickr

My PASS General Session set on Flickr

My PASS General Session on Facebook

The dedicated PASS Photowalk group on Flickr.

@SqLAgentMan (Tim Ford) Photowalk set on Flickr.

@NoDNS (Kris) pictures from the Photowalk on Flickr.

I have seen many more on Facebook but I don’t have public links to them. If anyone would like to post in the comments I’ll be more than happy to add it to the list.

Pat

New PASS Executive Committee

2009-10-31T15:28:00.001-06:00

I wanted to wish a big Congratulations to the new Executive Committee for PASS for next year. The New committee will be

Rushabh Mehta as President.

Bill Graziano as Executive Vice President of Finance

Rick Heiges as Vice President of Marketing

I hope them all the best in making a great next year for the PASS Community.

The official announcement can be found here.

http://www.sqlpass.org/Community/PASSBlog/articleType/ArticleView/articleId/118.aspx

Different Approach to the PASS Summit this year

2009-10-27T08:48:00.001-06:00

Volunteer Committee Last year at the Volunteer Outing.

This will be my 5th PASS summit this year. My second and final as a PASS Board of Director. Each PASS has been different for me but most have all had one theme in common. Meet and Network with many people while learning great things about SQL Server. This year I still intend to Meet and Network with others and learn about SQL Server but I’m also going to document and focus on the people at PASS. Everyone knows I’m a photo nut and tend to have my camera with me to much. Well this year it will always be on me. I’ve decided to forego my normal large laptop/camera bag for just a camera bag and net book. Most of the time my camera will be around me shoulder/neck on my R-Strap ready to take pictures whenever I can.

I’m a visual person and have a hard time remembering names and faces. Hopefully for me this will help me to recognize people for the next PASS Summit. All my pictures will end up on my Flickr Photostream and will be available to those that I have taken pictures of. If you would like a quick headshot or a picture with a friend at PASS feel free to seek me out and we’ll set something up.

If you would like to have fun and see Seattle and take pictures as well we have a Photowalk planned on Monday at 8:00 a.m. Feel free to come join us.

pat

Holy Bingo, I’m a Square!

2009-10-26T08:27:00.001-06:00

That’s right this year at the PASS Summit you get to play Bingo! Not just any Bingo but Twtiter Bingo. Stuart A(@StuartA), Brent O(@brento) and Blythe M(@blytheMorrow) have put together rules and all the details of the contest. Stuart has blogged about it here. Basically you need to find people on the Bingo card on Tuesday you need to get a straight line. Monday two straight lines and then on Wednesday a blackout. Each day once you have gotten the specific pattern you need to turn it into the Quest booth at the expo hall. They will have a drawing for fabulous gifts and prizes. :)

Now I’m telling you all this because I’m a Bingo Square! My handle on twitter is @SqlAsylum. I will be one of the easiest squares to find because I'm typically wearing a red vest as a PASS Ambassador. So most of the time you can see me near the PASS Booth or in the halls helping to direct others to where they are trying to go. I’m also a PASS Board of director and would love to hear your thoughts on the PASS Elections, the conference or the Community in general. Hopefully you will all have a chance to find me. I’ll even tell you the story or my code word if you would like. It’s all about the PASS Conference and it’s history.

Pat

Photowalking at the 2009 PASS Summit

2009-10-23T08:57:00.001-06:00

It’s that time of year again for the PASS Summit. Which means it’s time for a PASS Photowalk. Tim Ford(@sqlagentman) and myself wanted to get together last year and take some pictures around Seattle. Many friends joined us and the PASS Photowalk was born. We will be leading a Photowalk once again this year Here are all the details.

Where: Sheraton Lobby
When: Monday Nov 2nd 8:00 a.m.
Who: Anyone with a camera. The only requirement to Photowalking is a camera of some sort. If it's a camera on your phone that's fine. The goal is to socialize and meet other PASS/SQL folks and to take pictures while doing it.

Myself and Tim Ford will be leading the Photowalk. We will start off at the Sheraton and hopefully do a quick group shot and then work our way down to Pikes Place Market and then to Sculpture Park near Pikes place. From there will be anyone's guess. We will most likely stop at Pikes Place for some breakfast of some sort. Many of us will have things to attend later in the day on Monday so the walk most likely will not go past lunch time. Hopefully everyone is attending Don Gabor’s session later in the day as well.

We will use #sqlpassphotowalk as the hash tag for the event in case you want to follow where we are on twitter. I’m sure a few other people on the walk will be using twitter. If you know your going to be late email me at pat.wright@sqlpass.org and I’ll pass on my cell phone number so you can call me and find out where we are.

I've created a Flickr group for us to post photo's from the Photowalk. You can visit it here. http://www.flickr.com/groups/passphotowalk/ please Tag your photos with SQLPASS. For ease of searching. If you don’t have a flickr account it’s free to setup. You can see my pictures from last years Photowalk as well to get an idea of the walk.

Here are some suggestions to make this a good photowalk for you.

1. Be sociable. This is about learning and networking.

2. Be prepared. Last year we were lucky with excellent weather. We will see if we are that lucky again. Dress in layers and either carry an umbrella or be prepared to get wet. If your brining a Digital SLR like myself it’s best to have something to cover it up with such as a bag to keep the water out.

3. Walking. While we may stop at a location to take pictures there will be much walking. Wear shoes that are appropriate to do so.

4. Have fun. As with all things at PASS have a good time.

If there’s any questions comments feel free to add them here. I’ve also posted this in the Hotels.sqlpass.org forums .

Hope to see you all there!

Pat

ELECTIONS!

2009-10-16T09:33:00.001-06:00

Currently the PASS Board of Directors election is going on. You can vote for who you think should be on the Board for the next 2 years.

I really just want you to do 2 things.

1. VOTE!

2. Research the candidates and vote for you who you feel is the best candidates to be on the Board of Directors.

There was much lively debate over at Brent Ozar’s blog about Matt Morollo (@mattMorollo) one of the candidates running for the Board. There is information about the candidates as well on the PASS Election website. This information is being presented to you to help make a decision about who you want to vote for.

Having served my time on the Board I can tell you it’s not an easy position and these candidates will have many uphill battles in front of them. We belong to a wonderful community and it’s our responsibility to vote for who we feel are the best candidates. They will have a profound effect on the community as a Board of Director.

Good Luck to all the candidates and thank you for being willing to volunteer for the Board.

pat

Utah Code Camp (Wrap up)

2009-09-20T09:02:00.001-06:00

Another Utah Code Camp has come and gone. I figured I would write up a quick review of the event both for all my readers benefit and for my own to keep a reminder of items I need to work on.

The morning started out rather hectic with the doors not getting open on time. Good idea to remember to always call your location and contacts the night before and make sure the times are right.

Thankfully STG brought in bagels and coffee/OJ so everyone had something to snack on while we were going through registration and getting the doors opened.

The sessions kicked off with a keynote from Dr Alistair Cockburn which I believe did an excellent job. Unfortunately I had many other minor details about the camp that needed my attention so I wasn’t able to make the session but so far from the surveys and others response he did a great job.

We made it through the sessions in the morning well without to many hiccups. Once again we fell a little short on pizza for the lunch hour. The lesson learned here is that for IT folk you have to triple your pizza order not double it.

We discussed Primary key topics, clustered indexes, Olap vs Oltp , and DB architecture in our database roundtable. I think most came out with some good questions answered and lots of good discussion was had by all. Next code camp we are going to plan for a full SQL track to get lots more information out to the people.

I was able to make it to Aaron Skonnard’s Session on Windows Azure and Cloud Computing. I had not seen Aaron speak before I’m happy to say I was not disappointed he did a wonderful job of speaking and confirmed many of my suspicions about the cloud and what we can expect from it(future blog post).

Of course my favorite part of the day is being able to give away stuff. :) We gave away a lot of software this time. Well over 10K in software.

All in all it was once again a great event and I’m already looking forward to the next one. Thank you to all my excellent sponsors that made the camp possible. A HUGE thank you to my presenters and to Craig, Nathan and Justin for helping me so much with organizing the event.

Hope to see everyone at the next Code Camp!

“The Cube” Summary of Peter Myer’s User group presentation.

2009-09-15T09:20:00.002-06:00

Peter Myers is in SLC this week to do some training. He was kind enough to approach the Local SQL PASS user group and offer to do a presentation. I helped out by organizing the location at New Horizons. This would be a good time to check for your Local SQL PASS Chapter if you don’t know where it is. Here’s a list!

Peter’s Presentation was “Developing a UDM with Analysis Services” UDM = Unified Dimension Model. This is a new name MS has given for Cubes in 2008. So don’t let the name fool you UDM =Cubes. Peter did a great overall presentation on Cubes showing us how to create them and enriching them with more features. Here’s some of the key points.

Reasons for Moving to OLAP Structures instead of OLTP

Performance,

Multiple Disparate Systems of data can be combined to one,

Interpretation /One Truth of the data can be controlled in one location.

DW is a continuing process never ending. Answering questions will always just yield more questions.

We all know that the more questions you answer the more questions the business has for you. So when someone says to you that the DW process will be done in 2-3 years it really won’t it will keep evolving. Much of the design and work to load the DW could be complete but more questions will keep coming.

Data Source Views

Allow for the abstraction of your data layer. It’s good to build the foundation properly and setup your DW database in the fact/dimension table schema that you have designed. When that’s not always possible or your given read only access to the data and you can’t change the underlying schema then you need to make those changes in Data source views. Here’s some key items you can do.

Create columns based on other columns.

Create tables based off queries and filter tables based off a query.

Create friendly names to make it easier to use in the cube.

Dimensions

Peter suggested creating your dimensions before allowing the cube wizard to create your dimensions for you. Typically I create my dimension tables in the underlying data structure and then allow the wizard to create the dimensions for me. I agree with Peter though that this will allow you to be more in control by creating the dimensions first and modifying them for your need and then just telling the wizard where to go to get the data. I like this approach much better and plan to move to it in the future.

It’s a good idea to change your Key columns and your Name columns in your dimensions to the data that you actually want. The wizard does not always get this right and should be something that you get the information you are looking for.

Limit your dimensions and don’t show the columns the end user doesn’t need.

Create hierarchies to help the end user navigate better.

SSAS 2008 incorporates best practices into it so if you see a blue squiggly line it’s making a suggestion that it wants' you to change something to meet best practices.

Cubes

Peter really showed us the building blocks to building the Cube so when it came to actually building the Cube it took very little time and worked very well. I’ve been a firm believer in building your foundation first for your cubes so huge props to Peter for doing an excellent job in explaining it this way for everyone.

Excel

I’ve used pivot tables in excel for many years so most of this was review for me but as always you should keep learning new things and I once again learned something new here. I learned that about the formula for =CubeMember() in excel. I was not aware of this before allowing you to place cube data just about anywhere in excel. This will be great for some specific reports that we have that are manually updated right now. I can place a sheet on the work book that has the cube data and just hide the sheet and reference it with this throughout the report that they want. Great added functionality.

Overall Peter not only knew his stuff but was an Excellent presenter. I really have a hard decision to make now on what pre-con I want to attend. :)

I’m planning some follow up blog posts on a few other key items he mentioned. Attribute hierarchies, properties of dimensions and more on using excel and CubeMember()

Thanks again Peter!

When a Second just isn’t fast enough

2009-09-11T10:29:00.001-06:00

So I find myself performance tuning queries and statements all the time for SQL Server. Many times I run into easy statements that are missing an index or calling a function 20 million times and causing problems. These queries typically move from 20 seconds to 1-2 seconds very easily with some quick changes. My problem this week was a query that took 1.5 seconds. I needed to get this to run under 1 second consistently. I knew could get a little more out of the query by looking at the execution plan but I also knew I would have to consider the Architecture and the application itself to really get it the way I wanted it.

I was surprised to find something in the execution plan that helped me out more than I expected. The Key Lookup (In 2000 they are called Bookmark Lookups). These show up in the execution plan and look like this.

You will usually find these in your execution plans along with an Index seek/scan. But what really do they mean? SQL Server has found your data in the index but some of the values you have asked for in the query are not located in the index pages and it must go and lookup the data. This will take a little more time as SQL Server goes and fetches your data. The funny thing about this query is that for the Execution plan it only put cost at 10% on this particular lookup. Which seemed odd to me because when I looked at the IO STATS ( Set STATISTICS IO ON) it seemed like many of my reads were to pull back this data. I decided I would ignore the Cost % and place a covering index on the fields that are getting pulled back. I changed the index that it was using in the seek to add columns in my select statement as INCLUDE columns in my index. This made a larger improvement than just the 10% that it had estimated. It removed about 500ms of time from the query. Which is a lot when your trying to go from 1.5 seconds to <1 second.

Of course you do have a trade off here. By adding the columns to the index I’ve made my index larger and take up more space so don’t go around just adding all your fields into the INCLUDE of a index. You need to evaluate and find out if the trade off is worth it. In this case it is. I’m also making changes to the application so that it doesn’t request columns it doesn’t need then I can remove them from the INCLUDE and save myself some space.

If there is one thing I’ve learned from performance tuning and reading execution plans is never take them at face value. They are a tool that was created to help you make better decisions. They are not always right so make sure to do your homework and really test your procedures and break them down to find the real bottlenecks.

Happy Performance Tuning

24 Hours of PASS

2009-09-04T09:06:00.001-06:00

Ok so I can be honest and say I did not sign up for 24 hours of pass until the day before it started. This wasn’t because I was not interested just to busy with many other things. I figured I would make a few sessions and learn something. Boy was I wrong (this happens a lot for me).

Once I found that I could also watch SQL Rockstar(Thom Larock) live while the sessions were going on I was hooked. The side conversations and the talk going on in the chat was just like being at PASS. It made me want it to be November right now! This made the event for me and really added to it.

But did I actually learn something? Why yes I did. I still only made a few sessions because of my schedule but the few I made it to were great. I attended Steve Jones session on Blogging. This is something I didn’t want to miss as you can see from my Blog I need help. I am taking his advice and going to set aside time for blogging and start getting blogs in a “que” ready to go. So I’ll start simple on my goal and stick with once a week at least. We’ll see how I do.

The other session that left me with good information was Brian Knight’s session on “Loading a Data Warehouse”. Brian is one of those amazing technical people that is very smart and an excellent teacher, I love attending his sessions whenever I get the chance. I have always been hesitant about using the built in lookup tools and dimensions tools in SSIS and preferred to use my own stored procedures and set based methods. This is usually based around performance as Brian mentioned in his talk performance is bad when it has to do it row by row. But for the first time I’ve seen by a BI person he gave some set based alternatives using SSIS. Using these techniques will help me in a current DW I’m designing. I may also *try* and use the Slowly changing Dimension in SSIS but I have a feeling doing it in T-SQL and using Merge will just be easier for me. :)

So would I attend something like this again. Absolutely 100%. Am I extremely stoked to get to PASS in November Absolutely 100%!

A great big thank you to everyone at PASS and all the speakers and moderators for giving their time and volunteering for this. Thank you also to Rick Heiges who started the idea and championed it through the process. Once again Rick I think I owe you a Bacardi and diet coke for this one. :)

PASS Board of Directors

2009-08-27T09:21:00.001-06:00

I have been a Board of director for PASS for the past 2 years. My time as a Board of Director has been and Exceptional experience and I would suggest any volunteer that is on the “fence” about running should put their hat in.

Some good friends of mine had to “help” me the first time I submitted my entry for PASS Board of director. I would suggest anyone out there that has a friend or knows someone considering running for the Board to urge them to do so. I have never regretted putting my name in. I have learned many things from the Board and it’s given me an experience I can’t find anywhere else. The Board is a great group of people and are excellent to work with.

Good luck if you do choose to run!

PASS Call for Nominations.

Pat

Utah Code Camp

2009-08-26T09:12:00.001-06:00

Utah Code Camp is only a short couple weeks away have you registered? This will be a great day of learning about lots of different technologies and networking with others in your community. You can register at www.utcodecamp.com We’ll have lots of giveaways and swag for you to get at the code camp. Just announced we have Alistair Cockburn as the Keynote Speaker for the code camp. Here’s his bio and information.

Alistair Cockburn (pronounced CO-burn) is one of the coauthors of the Agile Manifesto in 2001 and one of the world’s leading experts on Agile methodologies. He is the creator Crystal Clear, a lightweight development methodology. He is credited with defining Use Cases. Dr. Cockburn has written several books on Use Cases, Agile methodologies, and Crystal Clear. He received his PhD from the University of Oslo in 2003 and today lives in Salt Lake City.

So Come out and Join us for a great day of learning!

Getting a NEWSEQUENTIALID()

2009-07-16T11:35:00.001-06:00

I ran into a problem yesterday where I needed to get the ID that was created after an insert. Typically this is something you use @@identity for or Scope_Identity(). I had to use a Guid Though so I couldn’t use those features. I posted the question on twitter and received a quick response a BIG Thank you to. @robboek , @venzann and @mladenPrajdic. For the suggestion on using the Output Clause

Here’s a Standard use of @@identity.

Declare @identity int

Create table #temp
    (Id int identity(1,1) not null,
    ProductName varchar(255) null)
Insert into #temp
    (Productname) Values ('Bacon Cheeseburger')
Select @identity = @@IDENTITY

Select @identity

This doesn’t work if your using a Guid and a NewSequentialID but here is the fix.

Create table #temp
    (Id uniqueidentifier Default newsequentialid(),
    Productname varchar(255) null,
    Cost money not null)
Declare @RowNumber Table (rowid uniqueidentifier)
Declare @rowid uniqueidentifier

    Insert into #temp
        (Productname,Cost)
        Output inserted.Id
            Into @RowNumber
    Values
        ('Bacon Cheeseburger','2.99')
    Select @rowid = rowid from @RowNumber
    Select @rowid

This solution should work on Sql 2005 or Sql 2008

Pat

Best Thing I learned at SQL PASS

2009-06-23T08:27:00.001-06:00

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.

Stranded on an Island……Working?

2009-06-22T16:58:00.001-06:00

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

2009-06-05T13:38:00.001-06:00

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)

2009-06-03T11:24:00.001-06:00

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.

Path to DBA

2009-05-25T11:03:00.001-06:00

Recently I needed to make a list of what tasks I thought would be needed to qualify someone from a Jr. DBA to a DBA. One of the people I was managing had started as a Jr. DBA that we were training and after a year of training and tasks we felt it was time to move him up in rank to a full time DBA. Of course we wanted a checklist of items to prove this so below is the list I used to qualify the person.

1. Fixing corruption issues

2. Determining optimal Disk layout based on application needs.

3. Index suggestions based on Slow running Stored Procedure.

4. Index/performance suggestions based on profiler running against application.

5. Defragmentation, Stats, reindex schedule suggestions.

6. Understanding of Sql Server configuration best practices.

7. Ability to place the db in Single user mode, Rebuild master, Attaching/Detaching Db’s.

8. Understanding Security differences in Role/user security

9. Understanding Isolation levels, difference suggestions for using each one.

10. When is a good Use of (nolock) and for what?

11. What are memory configurations and settings for SQL server.

12. Backup and Restore Db through t-sql and to a point in time.

13. Setting up Db mail.

Along with this list was some specific goals and suggestions for the specific company and application we worked with. A good DBA not only understands the Database system they are working with but they also know the system and how it is used.

I’m sure there are more things people could think of feel free to add more in the comments if you have suggestions for additional items.

SSIS Importing Files

2009-01-20T18:38:00.001-07:00

So I ran into an interesting problem that I haven’t found before on an SSIS import for SQL Server 2005. I was trying to import a large (2+ mil rows) text file that was a Ragged Right format and for some reason after 1.8 mil rows it would fail with the following error message.

[DTS.Pipeline] Error: Column Data for Column "Client" overflowed the disk I/O buffer

[DTS.Pipeline] Error: The PrimeOutput method on component "Client Source" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

I like any person trouble shooting something went out to google and searched for “overflowed the disk I/O buffer”. I found a few good links on it

http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/e312612c-acbf-45e3-b773-3fd84466b4fb/

http://www.bigresource.com/MS_SQL-Column-overflowed-the-disk-I-O-buffer-V666XXcc.html

But none of these worked for me. I attempted to change the [LF] to [CR] to fix my problem. I tried different nvarchar and Unicode strings to allow these fields to come in but nothing ended up working.

Finally I had to open the file and look at it. I couldn’t easily open a 2 GB text file on this server through notepad so I wanted to find something else that could open the file quickly and let me see what was in the file. I asked on Twitter what others had used and several came up with UltraEdit. I went ahead and downloaded the trial and opened up the file it opened very quickly and worked perfectly to quickly show that at the 1.8 millionth row that it had been failing on it didn’t actually end at all. There was no [CR] or [LF] and the rest of the file was just filled with empty space so this data was trying to import the empty rows into all one field causing the overflow.

I’m not trying to make this a commercial for UltraEdit but I would suggest some sort of editor that can help you out when you’ve run through all your other methods. If you import lots of files with SSIS and many different formats then you should consider some sort of editor that can save you some time.

I’m working on a follow up post on my “typical” things I do in SSIS to try and get files to import.

Pat

Evaluation Edition(Days Remaining)

2009-01-08T09:24:00.001-07:00

So I’ve run into an interesting problem that I don’t think I’ve had to deal with to many times and hopefully most of you have not either. How do you find how many days left in your trial of SQL Server? I am in the process of purchasing a MSDN license through my company so I’ve been running on an Eval copy of SQL 2008. One that I got from one of the many MS events I attend. I have no doubt that I will be moving to 2008 but since I didn’t have an MSDN license the only thing I could use was a Eval copy.

Unfortunately it’s taking a long time to get the purchase through so I was curious to see how much time I had left before my copy expires. Unlike many products that tell you every time they open how many days they have left SQL Server does not. I was able to locate it though in Help and About. As shown below though it’s not very helpful since it cuts off the column telling you how many days are left.

So you can either choose the copy info button and paste it into some text file. Or just Expand the column at the Top this will tell you how many days are remaining.

Hopefully you never find yourself to close to the end of an evaluation of software but just in case you do now you have a way to check.

pat

2009 New Year Resolutions

2009-01-05T09:25:00.001-07:00

Ok this post is a little misleading since I don’t make resolutions. I make goals for the year. So I’ll share those here in each of my different area’s.

Computer/SQL/Professional

1. I will deep dive farther into SSIS and DW to truly produce an end to end BI solution for my company.

2. Manage my team to become more effective.

3. Train my team better and give them the tools they need to succeed.

4. Deep Dive into SQL 2008 and future SQL products I can get my hands on.

Volunteer/Blogging

1. Change the volunteer program for PASS to better suit the needs of the organization.

2. Blog at least 2 times weekly.

3. Speak more and Help more at the SQL Server user group.

4. Hold 3 code camp events. (Utah code camp spring and fall, and a PASS Community Connection(march 2009) )

5. Re-start the SLC IT pro user group.

6. Write at least 2 articles. I’m not a good writer so this will be hard for me.

As you can see some of these are vague while others are specific. I know that you should try and make goals specific to follow. For me sometimes it doesn’t go the way I intended but it still ends up getting what’s done and what is needed so my goals are similar in that they give me the direction I want to go but not necessarily the entire map. When I sit down everyday I try and review these items and find things that help me to make that map and get to my goals. Hopefully this year I will be able to meet all of these. The hardest one for me will be the writing. But I’m already working on a solution to that.

Back to Seattle

2008-12-31T07:58:00.001-07:00

Well I’ve been to Seattle 3 times in 2008 and I’m starting off my first trip in 2009 going back. I have been appointed to the PASS Board of Directors for 2009. As most recall I was not elected during the PASS Summit. There were two open positions from people leaving the board last year and I have been appointed to one of them. SQLBatman (Thom Larock) was appointed to the other.

I will be working with the volunteers again this year and I am already working on a new plan to get more of the great volunteers we have involved with PASS. I am sure I will still have a part in PASS Community Connection as I will continue to help others with setting this up.

I’m happy to once again be on the Board. It was a learning experience for me last year. I think this year I can get even more accomplished.

What was I thinking? Talking about slow weeks

2008-12-24T08:15:00.001-07:00

Ok so really I think I forgot to take my common sense pills on Monday. What happens when you say your going to have a slow week or this will be an excellent week to get such and such done. What always happens it get’s busy. Not just a little busy but terribly busy. So far I’ve had 2 full days I even had to leave work late on Monday to get things done plus late nights of working. I’m making a mental note now to never post something like that again.

On the learning side how many people out there deal with instances of SQL Server? If you haven’t do you know how to connect to one? This might seem simple to most and for many dba’s it should be but I was recently training our Junior DBA and he didn’t know how to connect to the instance instead of just the default. So I figured I would give a quick run down for those that don’t know or are just learning.

A default instance is the same name as the computer it’s running on. So say we have a box named SQL1 to connect in SSMS you simply need to connect to SQL1 and provide credentials. Now let’s say we install another instance on that same computer named FOO. In this case in ssms you need to connect to SQL1\FOO you still use the computer name but now you are telling it which instance you want to connect to. Let’s have even more fun and add another instance called BAR now if we want to connect there we would do SQL1\BAR. Pretty simple eh? Again this is something that’s simple and easy to remember but hopefully it helps the few out there that have yet to work with instances.

pat

How do you spend your Slow week?

2008-12-22T08:00:00.001-07:00

For many in the computer world this week is usually a production change freeze week or a slower week at the office. For many DBA’s I know they will have less on their plate this week then they normally do. So how is it you spend your time on a week like this? If the week is slow for you. Some find themselves cramming to get things done before the holiday at the end of the week.

For me I’m still working on some data gathering designs since it’s not affecting my production systems. But I will be taking some time to catch up on the last few weeks. I’ve had a very busy last few weeks and need some review time. Here’s one thing I’ll be reviewing that I haven’t had a chance to get back to.

This was the first year I would have to say we had 3 good Keynote’s at the PASS Summit. I did not like the Dell keynote on Thursday before Tom Casey came up to speak but we’ll just not include that as one of the three. :)

I plan on re-watching these during my “slower” week this week and figured others might like to know where you can get the keynotes for download.

http://www.microsoft.com/sqlserver/2008/en/us/events-webcasts.aspx

David Dewitt did an excellent job talking about the scale out capabilities planned for the future of SQL Server. I hope to be working with this in the future so I’m very excited about it. If you get a chance I would suggest checking them out.

SQL Quiz

2008-12-21T03:10:00.001-07:00

So I was tagged couple weeks ago by Tjay to take part in a SQL Quiz from Chris Shaw.

The Questions for this quiz…

What are the largest challenges that you have faced in your career and how did you overcome those?

1. My first challenge I would say it's not one task but all the task's. Balance is what I find is the greatest challenge. Not only balancing work and family but balancing SQL Servers. Every Server I have come across is always battling and fighting between querying the data and pulling it out for use. It's the most fundamental aspect of a database and usually we find ourselves trying to find ways for those two items to work in harmony more than anything else. We tune sprocs, queries and design all in hopes of finding that happy balance where everything works.

2. My second challenge would be the DW projects I've worked on. My DW projects rarely fit a normal DW project with a team of people and gathering requirements and gathering all the aspects of what are needed and designing a system that works for it. Usually it's a smaller project or one that's expected to do all those things without all the time and effort. Not to say this is bad it's just that you have to be very flexible sometimes when it comes to DW projects. If you can sit down and gather what's needed and build a team and start the ball rolling right great! But if not don't be afraid to jump in head first and get going on your own. Sometimes once they have seen you can do it then it makes it easier to keep going.

Those are what I see as my two biggest challenges. Maybe not specific problems I've come across but I rarely get specific problems.

I'm not going to tag two more people since I'm pretty sure all the people I could tag have already been tagged. :) For now I'll just post my answers.

pat