Voice of the DBA

Category: Editorial

  • Test Before Deciding

    Do you extensively test?

    Will a new index improve the overall performance of your database server? Will adding another index slow down your insert or update performance? Can adding another file to tempdb make things run quicker?

    The answer to all of those questions is definitively “it depends.” That sounds contradictory, but it’s actually the answer that applies to all of your workloads. Your workload, on your version of SQL Server, on your hardware, with your data sets, will perform differently than the same version of SQL Server on the same hardware than my workload.

    What does that mean for DBAs and developers? It really means that you can’t easily make hard and fast decisions about many of the settings, configurations, and designs you use for performance are just guidelines. The best DBAs I know have lots of guidelines and rules of thumb that they use to begin their design or analysis of a database instance. However they are willing to make exceptions or changes to their “rules” if the situation warrants a change.

    How do they know when to change? They test.

    It’s that simple. Running tests, comparing the changes in performance, measuring the metrics that occur from implementing a particular feature allows a DBA to make an educated guess about whether or not any alteration of your code, your indexes, or your design will bring about improvement. However testing before you make the chance isn’t enough. Even after you have made a change, you need to continue to monitor, essentially “re-testing” in a live system, to determine if the system is still performing well.

    Just trusting that you are “sure” of what to do or not to do is a mistake. Set up tests, gather metrics, and make informed decisions to ensure you servers are operating at peak performance.

    Steve Jones

    The Voice of the DBA Podcasts

  • Looking at SOX

    What do you think of SOX?

    We republished an editorial recently that I wrote early on when the Sarbanes Oxley Act (SOX) was starting to become a part of daily life for IT people. SOX was really written to the rest of the business, requiring accountability from the executives in a corporation. However it seemed to quickly become a burden for many IT departments, with the interpretations of the act requiring additional documentation for all actions. There were often security changes required that interfered with the way in which many technology groups operated.

    The act was passed in 2002, nearly a decade ago. Now that it’s been a part of public corporate life, I wanted to ask the data professionals out there what they think of the act and its impact on their jobs. For this Friday’s poll:

    Does SOX impact your work?

    Is there an increased level of effort and work for you or is it just a part of your job now that you accept and are used to? I would think that after years of being subject to the regulation that most people would be used to it by now.

    I know when the act was first passed there was concern that our IT department would be understaffed for the documentation that was required. However we were an ISO 9001 certified organization, and we quickly realized that much of our work to maintain our ISO status could be re-used for SOX compliance as well.

    The biggest hurdle we faced was in implementing better separation for development and production groups to ensure that systems were accessed by the appropriate individuals. It seemed to me that this was a change for the better at my company, providing better security for us.

    Let us know this Friday how SOX, or even PCI/HIPAA impact your workload.

    Steve Jones

    The Voice of the DBA Podcasts

  • Implicit and Explicit Conversions

    Don't trust implicit conversions

    In a talk recently with some people I had someone note that the always chose to use explicit conversions on data types to prevent any unforeseen issues. That’s what I’d recommend as well. I have seen code in production function for years using implicit conversions, only to start failing when someone finally entered an invalid character in a row.

    How does that happen? Usually when someone is using character data types to store data that can be represented as character data,  even though the data must be dealt with in it’s native format. An example of this is storing a date as a varchar(10) or sticking numerical quantities in a character field to preserve formatting notations like dollar signs, or commas.

    That kind of code can work , pass a QA process, and live for years in a production system. However sooner or later someone will enter data that will break a query and return an error. Depending on your error handling system, this can be problematic to track down because it’s very data dependent. The code might work for some data sets but not for others.

    The best advice I can give is to store data in the proper data types whenever possible, and use explicit conversions when comparing data that might be of disparate types. Don’t always expect ’09/01/2001′ to compare to getdate(), and don’t expect ‘1’ to equal 1 in your code. At some point bad data will get into the system and those comparisons will error out.

    Steve Jones

    The Voice of the DBA Podcasts

  • A SQL Conference in the Sun

    SQL Server Connections is in Orlando this Spring

    The SQL Server Connections spring conference is coming up in a few weeks in Orlando, FL. This is a part of the DevConnections multi-technology conference and this event is the chance to get some great SQL knowledge and inspiration from experts in a fantastic location. Once again SQLServerCentral is sponsoring a track at the conference; Brad McGehee and myself will be down there, looking forward to meeting a few of you.

    I think that conferences are a great way to take a break from work, recharge, and get some excitement back in your job. The speakers at these events teach me about SQL Server, and inspire me to come back and try some new feature or technique in my own environment. I might get the solution to a problem, and usually find that the work is just a little more fun when I return. To me, that’s an ROI that’s hard to argue against .

    DevConnections puts on a great conference event, recognizing that while people want to come learn, they also want to do it in a nice location. This event is in Orlando, with great weather, at a place you can bring your family for a mini-vacation before or after the event. There’s no shortage of things to do in Florida, and it’s a relatively inexpensive place to visit. The conference hotel even offers complimentary transportation to many popular attractions. Who knows, we might even end up sitting by the pool and talking high availability or Resource Governor in the late afternoons. That’s more interesting to me than discussing things in a conference hallway.

    So many people are hybrid IT workers, using more than one technology, and this conference reflects that. The last time I went to a DevConnections event,  I attended mostly SQL Server sessions, but did manage to see an ASP.NET session and a couple Sharepoint presentations. Becoming more rounded in my IT knowledge has paid off for me in the past, and the DevConnection events give you the chance to grow skills in a variety of areas.

    Hopefully I’ll meet a few of you at SQL Server Connections this spring and share a few drinks while we talk about SQL Server.  I should be easy to find in the Hawaiian shirt and cowboy hat.

    Steve Jones

    Save $200 with the “DevCon1” code when you register.

    The Voice of the DBA Podcasts