Voice of the DBA

Author: way0utwest

  • Grabbing SWAG

    It’s getting close to the time for the SQLServerCentral party events this fall, and time to start gathering up SWAG to give to people. In the past, I’ve hosted a SQLServerCentral party at the PASS Summit, and it’s always been a good time. Our first year, 8 years ago, we gave away shirts to people that had registered using our referral code, and it was a madhouse.

    Since then, I’ve had more organized events, giving away a polo shirt and a book to each person along with random prizes at the party. This year, I decided to make a change for a few reasons.

    First, my budget is down. The contract with PASS this year has me getting lower referrals for people that are repeat attendees. That and fewer people registering for now, mean that I’m going to lose money this year. That’s OK, since it’s a marketing event, but I have to watch the budget closer.

    So, I decided to try something new. I went with T-shirts this year, using a big SQLServerCentral logo on the front, ala Superman style. I looked around, and decided to go with DFC Screen Printing, who had a great site and worked with me through email to ensure the artwork was OK. It’s a bit of a leap of faith, but I’m hoping the heavyweight Jerzee’s t-shirts look good.

    We still have a book, though it’s a Stumper’s book this year, and not a “Best Of”. Costs all around are higher, so we had to make a decision. We still might do a “Best of” this year, but it won’t be the giveaway for PASS.

    I’ll still have other prizes available, and will be gathering up some DVDs, books, and other stuff during October. I won’t be at the party, but Brad will have things under control and it should be a great time.

  • Common SQL Server Mistakes – Indexing Every Column

    If one index helps speed up queries, than more indexes should help more, right? They do, but they also come at a price. Both in performance during data modifications (insert/update/delete), and in terms of space since each index must be stored somewhere.

    I have never bothered to index every column of a table. Actually I’m not sure if I’ve indexed every column of any table. Perhaps that’s because I originally came from a dBase/Clipper/Foxpro environment and I had to manage every index manually. Adding too many indexes resulted in a lot of coding.

    In SQL Server, each index is useful in two ways. When someone puts a filter in the WHERE clause, like this:

    select
      CustomerID, TerritoryID, AccountNumber
    from Sales.Customer
    where CustomerID = 10

    then an index on CustomerID will speed up this query. Instead of having to scan all rows of the table, the index on CustomerID can be searched, the correct row found. If this is a clustered index, then the data can be read. If it’s a non-clustered index, the server can retrieve the rowID and then go get the data from the clustered index without reading all the rows in the table.

    In more recent versions of SQL Server, multiple indexes can be used. For example,

    select
      CustomerID, TerritoryID, AccountNumber
    from Sales.Customer
    where CustomerID = 10
      and TerritoryID = 23

    In AdventureWorks, there are indexes on both CustomerID and TerritoryID for this table. It is possible (with lots of data), that the optimizer might choose to scan the CustomerID index for all matching rows and then the TerritoryID index for matching rows, join those results together to get a set of rows for the overall query and read the clustered index for those specific rows. However that’s not something you can count on in SQL Server. Typically one index is used in many queries.

    So why not index every column?

    First, if the columns are large, like varchar(max), text, or varbinary(max) columns, then it doesn’t make sense to build large indexes unless you often query these fields. Even then, a full-text index is likely a better choice.

    Second, each time you change data (insert/update/delete), then all indexes must be updated at the same time. This means that your write performance suffers, and that impacts the server read performance as well since resources are being used to perform those updates. The more indexes, the more work that has to be done in support of any DML statement.

    Lastly, you typically find that most of the time you query a table based on 3-5 fields, and those are the best candidates for indexes. For transactional tables, this is typically the number of indexes that you want to put on each table. Reporting tables, or OLAP type tables, might have more indexes, but these are tables that typically receive mostly read activity, and rare write activity.

    Which columns do you index? Pick those columns that often appear in your queries, and that are fairly selective. You can always query the missing index DMVs for help in choosing which indexes the optimizer things it might use.

  • SQL Saturday#52 – Colorado Recap

    This past weekend was the first SQL Saturday in Colorado, and it went very well. I think everyone enjoyed it, the organizers were pleased, and I’m hoping that we end up with 2, or more, events next year.

    This was my 5th SQL Saturday of 2010, and it was interesting. This was the first one that was being held in a new type of venue. Most of the events have been at local colleges or universities, with the one in Charlotte at the Microsoft offices. However the Denver organizers secured the use of A church, Cherry Creek Presbyterian, for the day. They have a large center that they use fro school with a gym/cafeteria that was great for lunch, and lots of classrooms.

    I was really looking forward to my event, with this being my home state, and I think the event ran great. A few impressions on the event.

    Speaker Dinner

    Colorado is also the home of Confio, and they sponsored our speaker’s dinner at the Great Northern Tavern. I’ve been there before, and it was nice that we had a private room in the back to chat in. We had a waiter serving drinks while I had the chance to see people from Boulder, Colorado Springs, and Denver, in addition to the people from out of town.

    I like having a fairly quiet place for the outside events since the primary purpose is networking, and when a place gets too loud, it’s hard to spend time talking.

    This was a nice informal event, and an excellent meal. As I normally see, most people leave between 8 and 9 to go back and prep for the next morning. I left around 9 myself, heading home.

    The Venue

    Being in a church for a technical event seemed a little funny to me, but I was willing to try it. The Boy Scout troop my son is in uses LDS churches for some events, and it always goes fine.

    This was a large venue, using two buildings and a large parking lot. I knew where it was, but I am sure some people were wondering if it was the right place as the church signage is set back a bit from the street and you need to enter the parking lot to see it. I saw a paper SQL Saturday #52 sign once I turned in, but for people not in the local area, they might have turned into one of the 3 or 4 other churches on the same street before they found it.

    There were signs all over the building, outside and in, once I arrived. There was also a whiteboard that listed all the tracks, sessions, and rooms. Apparently the church moved some rooms at the last minute, so good job to the organizers to post the sessions.

    Four tracks were in one building, and 2 in the main building with the cafeteria. It was a short walk, and there were volunteers helping people find their way around.

    The cafeteria was large, and had AV wireless microphones for announcing things. Most rooms also had microphones as well, and they seemed to work well. A few rooms struggled with sun leaking around drapes or in windows and made projectors slightly hard to see, but it was useable.

    Only one strange room had the speaker sitting to the side and near the rear of the room. Other than that, the venue was fantastic.

    Food

    Breakfast was the typical mix of coffee, muffins, and bagels. A variety of cream cheese, and plenty of it was available. I was disappointed not to see decaf, but I survived. Someone mentioned no tea, or milk, and I can understand that not everyone drinks coffee. However since this is a budget event, I think we need to surface the choices, or lack thereof, better.

    Lunch was charged for, and catered with a sandwich and small salad (potato or pasta) in a bag. A mix of choices, including vegetarian entrees. there were apples or oranges and chips as well. Plenty of soda and water in tubs, and there was some left over at the end.

    Since we were in two buildings, I was surprised to see tubs of soda/water in each building, allowing people to grab something no matter where they are. Typically things are centralized, but not here. There also was an afternoon snack for people. cookies, bags of nuts or trail mix, again in both buildings for attendees to enjoy.

    Tracks/Sessions

    Six rooms, six tracks, with a variety of speakers. I walked around and checked out a few of the sessions. It seemed that most of the sessions had between 10-30 people. With about 160 or so people attending, that made sense. It seemed that people were interested, and they enjoyed the sessions overall. Some of the feedback mentioned both more beginner and advanced sessions, and I think there’s likely room to hold events that focus more on either beginner or advanced sessions.

    We had Kevin Cox from Microsoft, and a few people from Confio presenting technical sessions. It’s good to get a variety of speakers, and topics, and I liked the fact that there were a number of Professional Development sessions as well.

    SWAG

    Only two vendors at this event, both getting tables in between the rooms and the cafeteria, so they had good traffic flowing. They both seemed to like the event, and that’s good.

    We did two giveaways at this event. Since we had a large lunch crowd, after I did my mini-keynote at lunch, we drew about 10 names out of the morning evaluation sessions and gave away some books and shirts. In the afternoon we did the same thing, but had about 30 items to let go. We also let our two vendors draw names from their lists before we drew the rest of the names.

    One minor issue here: we had both overall evals, and session evals mixed in the same box. Since some had names on them and some didn’t, it was hard to pull the paper from the box. Since that was my job in the afternoon, I was struggling to keep things moving. I’d like to see a consistent form.

    After Party

    I thought the after party was very well attended. It was at the Baker Street Pub, just about 3-4 blocks away and I would guess 60-80 people attended. Lots of speakers, but lots of other people. Jason Horner put together a mini-BINGO card that had a number of entries on it like:

    • Has a SQL 2000 certification
    • Has traveled over 100 miles
    • Has used merge replication in production
    • Has a > 1 TB database

    and more. People were supposed to walk around and network, and I liked this. It helped to get people to talking a bit instead of just signing cards.

    It felt like better networking at this party than a number of other after parties I’ve attended and I think having it close to the venue helped.

    Overall

    This was a great event, and I was thrilled to see if go over well. No major snags and everything seemed to run smoothly. There were a few advantages to using a church, and it’s something I’d like to see other people consider.

    I also informally asked if people would like more events (2-3 a year) and perhaps weekday events before my keynote at lunch, and most people were open to them. Not many people minded a Saturday event, but then again, this is a self-selected group out on a Saturday.

    One last note. I had prepped my keynote on a teleprompter, and in paper, but left the paper at home. It’s a little awkward to work with paper, at least for me. My machine managed to lock up near the beginning of my keynote, and I was forced to work from memory.

    Fortunately I know enough not to ramble too long and cut a 15-20 minute speech down to 10 minutes. A lesson for me, have a backup plan.

    Photos from Tim Mitchell’s photostream.

  • Hot Skills

    What are the hot skills to work on in 2011? I found this article that gives you a list of some of the things that companies will be looking for according to a survey in Computerworld. It’s a fairly standard list of generic skills that companies are looking for, but it ended with something that I think that people often overlook: business acumen and communication skills.

    How many of us have to work with someone regularly that is not a technologist? How many times do you have to explain something to a business person in a way that is devoid of technical jargon? I would bet that most of us have to do that. However  it seems that too many technology professionals still want business people to better understand what we provide with our computer hardware and software. Instead of us learning more about the business, we want the business to tell us what we should do.

    Building your soft skills, especially  your communication skills, is important, but too often technology professionals give lip service to any effort. We tend to write poorly, we struggle to explain concepts like disaster recovery in a way that those outside of technology can understand.

    I like to write, but I also learned early in my career that there was a huge advantage to being able to express myself clearly to everyone that I had to work with. I talk about this at The Modern Resume, but there really is no substitute for making some type of effort to learn to write better. It’s a great skill that I’m sure will provide benefits in 2011, and every year after.

    Steve Jones