Voice of the DBA

Category: Blog

  • I Pulled a Forest Gump

    Last Friday I went for a quick run before we packed up and headed to Steamboat Springs for a week’s vacation skiing with the family. Saturday I snowboarded, came back to the condo, found no treadmill, slippery, icy conditions on the roads, and just stopped running.

    No fanfare, no complaints, not even a note to my wife that night. I just stopped running, cooked dinner and went on with life.

    Some of you have followed me on my journey to run every day for a year. That journey ended last Friday, after 1564 consecutive days of running at least a mile every day. I’ve collected a little data along the my journey. This streak, a little over four years of my life, has encompassed:

    • 1564 days
    • 2810.4 miles
    • 754 miles in 2011
    • 2 countries (US, UK)
    • 15 US states
    • 5 pairs of running shoes (due for 2 new ones now)
    • using Nike +, MapMyRun, and Runmeter technologies for tracking
    • used 2 iPod Nanos (1 died), an iPhone 3, 3GS, and 4 across 4 years.
    • a low outside temp of 8F
    • a high outside temp of 110F
    • an early run of 4:15am at a hotel near Heathrow before a flight
    • a late run of 11:40pm at night, after a long day

    There have been some fun memories like Day 1000, which was celebrated late because of bad data. There were two great #sqlrun’s in Seattle with large groups at the PASS Summit. I have had great runs before SQL Saturdays and SQL Connections, along with other events.

    My middle son has the most runs with me, probably 40-50, but the person with the second most runs, and some of very enjoyable ones, is a member of the #SQLFamily, Allen White. Allen and I ran in Cleveland, Las Vegas, Chicago, Seattle, and Liverpool, UK. I always enjoy seeing him, and look forward to more runs in the future, and perhaps even a marathon some day.

    I’ll still be running, but mixing in other exercise more often. Throughout this streak I’ve played baseball, snowboarded, practiced karate, and played volleyball, but I’ve limited some other exercise I’d like to try more.

    I believe that taking care of your health is an important part of life, and exercise is one way I do that. I’d encourage all of you to find some way to ensure you are using your body, and not just your mind as you move through your career.

  • Predictions for 2013

    I’m on holiday, but I have a set of predictions for 2013 coming on Jan 1. This is a little notice in hopes that a few of you might join in to the meme and make a few predictions for 2013.

    Database related, technology related, car related, write a few things down that you think might come true in 2013 and post a link back here or drop it in the discussion for my SQLServerCentral editorial on Jan 1.

    What do you think will happen in 2013?

  • Recharging Again

    I’m off again, taking a week in Steamboat Springs with my family for the Christmas holiday. We’re spending a week up there, including my oldest son who came back from Seattle to see us.

    It seems like I’ve been gone a lot, with guest editorials and re-publishing old pieces in 2012. That’s good as it means I’m taking most of my vacation. At least I think so. When I look back I see:

    • a few days with my wife in the spring
    • a couple days to work around the house on projects
    • four days for camping
    • seven days of skiing around the holidays
    • four ski days in the spring
    • two other ski days this fall

    That’s not quite all my vacation, but it’s close.

    I wanted to get through all my vacation this year, and really make sure I took time away from work. It’s easy to get sucked into work, especially when you work at home and work on a flexible schedule. However I truly believe that getting away from work and spending time enjoying the rest of your life.

    If you haven’t taken your vacation this year, make that a goal for next year.

  • Building a Full Text Index

    I hadn’t used full-text indexing in production throughout my career. We hadn’t had the need in the applications I worked on, all of them depending on LIKE searches in specific, normalized data.

    However I have always been interested in it and as I try to find data more often in various systems, I’ve been playing with it on the SQLServerCentral systems. One of the first things I had to do on a copy of the system was build an index. It’s surprisingly easy. I’ll build a basic index and explain a few options.

    If you don’t know what a full-text index and full-text search (FTS) is, here’s a short introduction from BOL.

    Creating an Index

    Let’s take a basic table. In this case, let’s look at the AdventureWorks 2008 database. There’s a table called ProductDescription in there without an FTS index. Let’s add one there. First we right click the table and select the full-text index item.

    fts1

    This starts a wizard that we can use to pick the full text index. The first step is basing this on a unique index. The wizard is smart enough to only show those valid indexes for you to choose. I tend to choose the PK in most cases. That’s what I’ll do here. If your situation calls for something different, be sure you understand why.

    fts2

    The next step for is to choose from the available columns that are valid for full text indexes. In this case we only have one, so I’ll pick it.

    fts_2

    Once I do that, I then can examine the other options. The middle item is for Word Breakers. These are the rules by which we decide where word boundaries are.

    fts_3

    These rules can vary by language, and as you see above, there are multiple language choices. We’ll stick with English.

    The last column is for the “type” of data stored in the column we are indexing. This is for the use of iFilters for our data.

    fts_4

    In this case there is no other column that makes sense for a type. If you examine the full text index on the Production.Document table, there is a “FileExtension” column that is specified here.

    fts_6

    That column is named appropriately, but the name has nothing to do with the use here. If the column were named “Type” or “Extention” or even “Blue” it would still work as long as it contained the correct file type extension for the data stored in the full-text indexed column.

    Next we have the change tracking. You have three choices: automatic, manual, or do not track. The default is automatic, which I’ve often used.

    fts_5

    If you have a large index, with large changes, you might choose to manually update the index, but you then need to do that. If you don’t want an automatic population to start after the wizard, then you need to choose “Do not track changes” as noted at the bottom.

    The catalog is like a filegroup for the full text index. There can be multiple indexes in one catalog, but an index is only in one catalog. Note that you can select the actual filegroup in which this will be stored at the bottom.

    fts_7

    I haven’t had enough experience here to give guidance. I’ve just had one catalog for the systems I’ve worked on.

    The stoplist is the list of words ignored in the index. This was called the “noise word list” in SQL 2005 and prior. You can create your own stoplist, or use the system one.

    fts_8

    Here I only have one, though I could not use one and allow all words in the index, which tends to bloat the size.

    Next we have the standard scheduling mechanism in SQL Server. Here it’s applied to the index population, or what you might see as the rebuild.

    fts_9

    No guidance here other than choose what works. If population is an issue, pick a low time to schedule things.

    The last part is the summary.

    fts_10

    My one complaint here is the fact that there is no “Script” button for this. That’s a royal PIA and it’s why I’m showing the GUI here. This is the easiest way to build the index. Once it’s done, you can go into SSMS and get the properties.There’s a script button there.

    fts_11

    However if you press this, you get this

    fts_12

    No scripting for the index, which is a hole. I’ve submitted an item on Connect, which you can vote for.