Voice of the DBA

Author: way0utwest

  • Choosing Your Tasks

    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?

    Do you get to choose what happens every day at work?

    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

    The Voice of the DBA Podcasts

  • Clean Code is Easier to Read – SQL Prompt

    I saw a post recently that had query that looked like this:

    select a.*,name, b.*
 from sys.database_principals a, sys.database_permissions b

where permission_name = 'INSERT'
and
b.grantee_principal_id = a.principal_id

     

    Ugly to read, at least to me, and in a poorly written format. The table, table format isn’t ANSI compliant and isn’t recommended. So I did this:

    formatsql

    A little better, and easier to read, but not great.

    SELECT  a.* ,
        name ,
        b.*
FROM    sys.database_principals a ,
        sys.database_permissions b
WHERE   permission_name = 'INSERT'
        AND b.grantee_principal_id = a.principal_id

    However now I can make a few quick edits. Remove the comma between tables and add “INNER JOIN” and then move the AND clause up to an ON clause to give me this:

    SELECT  a.* ,
        name ,
        b.*
FROM    sys.database_principals a
  INNER JOIN sys.database_permissions b
    ON b.grantee_principal_id = a.principal_id
WHERE   permission_name = 'INSERT'

    Much better, and easier to read.

  • Learn to Use Filegroups

    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

    The Voice of the DBA Podcasts

  • Patch Week

    My email account started getting notices of Windows patches yesterday, indicating it’s patch week again. If you manage Windows devices, be sure you are aware of the patches that came out. The May Bulletin on Technet shows 2 issues, one critical, one important.

    I tend to apply patches late, probably a month after release, just to see if anyone reports an issue. It’s up to you, but be aware of the patches, and if you could be affected, make sure you test machines and then schedule the downtime.