Voice of the DBA

Author: way0utwest

  • Learn to Use Filegroups

    This editorial was originally published on May 12, 2011. It is being re-run as Steve is away at SQL Relay today.

    In SQL Server, filegroups are a management technique that I don’t see many people using. It’s amazing how many people ask questions about filegroups on the discussion forums, often unsure of how they fit into a well architected SQL Server. I have tended to use filegroups mostly as a space management technique, when I need to add more disks to my server, but they can be used in many more places.

    We continue to grow our data sizes all the time. While many databases are still measured in the single digits of gigabytes (or smaller), it is fairly common to find many database servers with over a terabyte of disk space. Our disks grow larger and larger, but it seems that data grows faster than disks, requiring larger storage subsystems all the time.

    While our storage grows larger, the tolerance for delays shrinks and demands for better performance increase. That means that data professionals need to be more cognizant of not only how their code is written, but also how they design storage. Tiering storage is one idea that I think has merit, but one that requires some planning.

    In SQL Server, we can’t split a table across filegroups. Or can we? We can partition a table (Enterprise Edition and higher), which can allow us to manage performance and storage appropriately. There is also the recommended practice of only having system objects in the primary partition and using separate filegroups for user data. That allows you to bring a partial database online, again, in Enterprise Edition only, while you restore different filegroups.

    This isn’t the first thing I would recommend you learn about SQL Server, but as you advance your knowledge, you should better understand when and how filegroups can help you. You will use them at some point and being comfortable with a filegroup restore is one of the skills that separates the accidental DBA from the data professional.

    Steve Jones

  • A tSQLt Mistake – Debugging a Test

    While I was working on a test the other day, it kept failing. Not a big surprise, but I couldn’t figure out why. When I looked at tsqlt.testresults, I saw extra rows. Double rows in fact, and that threw me.

    This was my Assemble code.

    -- Assemble
    CREATE TABLE #Expected (
      yearnum int
    , monthnum TINYINT
    , salestotal money
    )


    INSERT INTO #Expected
        ( yearnum
        , monthnum
        , salestotal
        )
      VALUES
        ( 2012, 11,  2500.23 )
    ,   ( 2012, 12,  2200.15 )
    ,   ( 2013, 1,  2656.75 )

    SELECT *
     INTO #actual
     FROM #Expected AS e

    EXEC tsqlt.FakeTable @TableName = N'MonthlySales', @SchemaName='dbo';

    INSERT dbo.MonthlySales
      VALUES
        ( 11, 1000.00)
    ,   ( 11, 1500.23)
    ,   ( 12, 2200.15)
    ,   ( 13, 1000.00)
    ,   ( 13, 1656.00)
    ,   ( 13, 0000.75);

    Here was the output (ignoring the failure messages):

    [tArticles].[test sum of sales by month for multiple months] failed: (Failure) The calculations are incorrect

    |_m_|yearnum|monthnum|salestotal|

    +—+——-+——–+———-+

    |=  |2012   |11      |2500.2300 |

    |=  |2012   |12      |2200.1500 |

    |=  |2013   |1       |2656.7500 |

    |>  |2013   |1       |2656.7500 |

    |>  |2012   |12      |2200.1500 |

    |>  |2012   |11      |2500.2300 |

     

    Hmmm. What’s going on? Why don’t the rows match? If I run the query, I see the results I expect. Is it the query or test?

    In this case, you read the results as showing that I have 3 rows that are the same in my expected and actual tables (@expected and @actual variables in the assert). However I also have 3 extra rows in the actual table, which appear to be duplicates.

    If I go back to the Assemble, I see a pattern that’s a problem. Some people might think these hassles are a way to give up on testing. Some might build a better pattern. I’ll do the latter.

    In this case I create the expected table and then I insert the expected results. Then I create my actual table from the expected one to keep the schema the same and avoid repeating code. However in this case I have a bug.

    The bug is I’m moving the expected results to actual. If I asserted at this point, I’d pass. However then I run the query and insert the results, which happen to be the same as the expected results (my query works). If the query didn’t work, I might really spend a lot of time debugging it, but here I can tell my test code is buggy.

    I have two choices to fix this.

    1. Add a WHERE clause of WHERE 1 = 0 (no rows inserted)
    2. Move the creation of the actual table.

    My first thought was to adjust the pattern to this:

    CREATE TABLE #Expected (
      yearnum int
    , monthnum TINYINT
    , salestotal money
    )

    SELECT *
     INTO #actual
     FROM #Expected AS e


    INSERT INTO #Expected
        ( yearnum
        , monthnum
        , salestotal
        )
      VALUES
        ( 2012, 11,  2500.23 )
    ,   ( 2012, 12,  2200.15 )
    ,   ( 2013, 1,  2656.75 )

    I move the #Actual and #Expected tables together, so that once I get the results set, I immediately create the #Actual copy. I could leave things and do this:

    CREATE TABLE #Expected (
      yearnum int
    , monthnum TINYINT
    , salestotal money
    )


    INSERT INTO #Expected
        ( yearnum
        , monthnum
        , salestotal
        )
      VALUES
        ( 2012, 11,  2500.23 )
    ,   ( 2012, 12,  2200.15 )
    ,   ( 2013, 1,  2656.75 )

    SELECT *
     INTO #actual
     FROM #Expected AS e
     WHERE 1 = 0

    EXEC tsqlt.FakeTable @TableName = N'MonthlySales', @SchemaName='dbo';

    Maybe the best thing is to be careful and do this:

    -- Assemble
    CREATE TABLE #Expected (
      yearnum int
    , monthnum TINYINT
    , salestotal money
    )

    SELECT *
     INTO #actual
     FROM #Expected AS e
     WHERE 1 = 0

    INSERT INTO #Expected
        ( yearnum
        , monthnum
        , salestotal
        )
      VALUES
        ( 2012, 11,  2500.23 )
    ,   ( 2012, 12,  2200.15 )
    ,   ( 2013, 1,  2656.75 )

    Combine the ideas and keep this insulated from refactoring moving or adding code in there.

    Remember, tests are code. This is why they should fail first, so that you have some confidence in your code working and causing a test to pass.

  • Choosing Your Tasks

    This editorial was originally published on May 13, 2011. It is being re-run as Steve is out of town.

    I have been working for SQLServerCentral for nearly a decade. I started in 2002 and in that time I have had to define my own job most of the time. Early on Andy and Brian had a list of things they thought I should be doing, and there were certain things to get done each week, but it was a general list.

    This Friday I thought this was a topic for a good poll. Answer this question:

    What percentage of your time is self-directed?

    By self-directed I mean the tasks that you choose to do because you think they need to be done for some reason. This is opposed to the specific tasks that someone assigns you and gives you some deadline for finishing. If someone asks you to “tune the server”, I don’t consider that a specific task, and you would have to pick items to work on, and determine how much time you spend on them, that’s a self-directed task.

    Do you have a good amount of self-directed time? When I used to manage a series of production servers, I usually had at least half my time as self-directed time. I could look at poor running queries, contact groups with proactive ideas for improving performance or preventing problems. I even had time to schedule DR testing. That took a lot of investment over time. I had to understand each system, set up monitoring and standards, build in data capture and analysis routines, and of course, plenty of alerting mechanisms.

    I think that a great production DBA will have a lot of self-directed time after 6-12 months on the job. A development DBA, however, will likely be constantly responding to code requests and enhancements, which is one reason I prefer the former job.

    Steve Jones

  • A Long Trip Ahead

    This is my last day at home for a long time. At least long by my standards. I head to the airport tomorrow for a ten day trip, not returning to CO until Saturday, Oct 17. I rarely travel more than 4 or 5 days at the most, so this is one of my longer ones.

    My first stop is Orlando. I’m heading over to help teach a DLM workshop for Redgate Software on Friday. This is our Database Source Control workshop that covers some in depth work with SQL Source Control and version control systems. I’ve done a few of these, so this should be easy for me.

    Saturday is SQL Saturday #442 in Orlando. I haven’t been to a SQL Saturday in Orlando in a long time, so I’m excited to get back to the place where these all started. I’ve got one talk on Saturday, talking Encryption, around which I’ll be hanging out with friends and trying to learn a few SQL things along the way.

    Sunday I travel, though at a relaxed pace. I’ll spend the day making my way to Houston before an overnight flight to London on the Dreamliner. It’s a leisurely day, where I’ll probably spend time catching up on Python work because Monday is crazy.

    Monday is a day I dread a bit. I land in London and immediately drive to Cambridge for a few meetings. I’ve got some SQL in the City rehearsals planned before I turn around and head back to London to catch the fun bus to Bristol for SQL Relay. If you map this out, it seems silly, but that’s what I got myself talked into somehow.

    Tuesday is SQL Relay in Bristol. I’ll be previewing my talk for SQL in the City, so I’ll apologize in advance if things aren’t 100% set. However after a day at the conference, I’ll be heading over to Cardiff where I’ll get dinner and try to fix all the things I did wrong during the talk.

    Wednesday is SQL Relay Cardiff.  A repeat of Tuesday in a new city. I’m not sure if everything is the same, but I’ll be (hopefully) delivering a better talk on Wednesday. Wednesday night Grant and I aren’t doing anything, so it’s a few hours to unwind.

    Thursday morning we make our way back to London. Hopefully we manage the train system fine because we have lunchtime and afternoon meetings with people coming down from Redgate during the day. This is the final SQL in the City prep time, as well as a few other in person events, including seeing my boss for only the 3rd time this year.

    Friday is SQL in the City 2015 London. Redgate puts on a great event, and I’m looking forward to another exciting day. Three times on stage for me, so I’m sure when things wrap up around 5 I’ll be quite tired. However no rest, I head to Heathrow for a night in my 5th hotel on this trip.

    10 days. Orlando, Cambridge, Bristol, Cardiff, London.

    I have the feeling I won’t be doing much on Saturday night or Sunday when I return.