Voice of the DBA

Author: way0utwest

  • 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

  • My Connect Items – Won’t Fix

    There’s been some work to close out items in Connect that aren’t going to be fixed. I don’t know exactly if these are items that aren’t good ideas, are dropped because of a lack of resources, or because no one wants to figure that out.

    However, I had three items closed as Won’t Fix in the last couple days. None are what I’d consider critical, but they were ideas I had to make the product work smoother. I won’t resubmit them, unless I get some feedback that people find them important. Here were my items:

    Remove Auto-close

    Set the database default to simple

    Allow User Objects to be Deprecated

    The last one is the one that I think made the most sense. Not as something that’s critical, but something that might help smooth the way that people work with SQL Server, and help them build better software.

  • 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