Voice of the DBA

Author: way0utwest

  • Common SQL Server Mistakes – Functions in the WHERE Clause

    This continues my series on Common SQL Server mistakes, looking at more T-SQL mistakes.

    What’s Wrong?

    If you saw a query like this, would you see a problem?

    select
      o.OrderID
      , o.CustomerID
      , o.Qty
    from Orders o
    where datepart( yyyy, o.OrderDate) = '2010'

    If there are 1,000 orders in this table, there probably isn’t an issue. But if there are 1,000,000, then this is an issue.

    Why? Let’s examine the execution plan:

    This table has 1000 rows in it, but it doesn’t use indexing to find those orders that were placed in 2010. Instead it scans all rows. The reason is that the function being used in the WHERE clause means that the index cannot be used.

    Instead, what you would want to do is write the query like this:

    select
      o.OrderID
      , o.*
      , o.Qty
    from [OrderItems] o
    where o.OrderDate >= '20100101'

    In this way, we eliminate the function from the WHERE clause and allow the query optimizer to take advantage of the indexes on the column OrderDate.

    You see similar issues with queries like:

    select
    lastname
    from Person.Contact
    where left(Lastname, 1) = 'S'

    This can be fixed as:

    select
    lastname
    from Person.Contact
    where Lastname like 'S%'

    Basically you want to move the function away from the column and put it on the other side of the comparison so that indexes can be used.

    Too often we have developers writing queries like this, assuming that the functions are efficient. They are, but when they are executed against every row in a table, an index can’t be used for seek operations, which are always quicker than scans for any significant data set.

    When you are writing queries, do your best to avoid functions against columns in your tables. Instead try to rework the query to move the function. An alternative that I’ll blog about another time is computed columns.

  • The SQL Rally Pre Conference Seminars

    There’s a change in how pre-conference seminars are being picked for the SQL Rally event next year in Orlando, FL. You, as the people that may attend, are getting the chance to vote on which sessions you’d like to see.
    Vote Here
    You get to pick from seminars in various categories, voting once for each session. The descriptions of the sessions are here:
    BI Sessions: http://www.sqlpass.org/sqlrally/2011/PreConsforVotingBI.aspx
    DBA Sessions: http://www.sqlpass.org/sqlrally/2011/PreConsforVotingDba.aspx
    Developer Sessions: http://www.sqlpass.org/sqlrally/2011/PreConsforVotingDev.aspx
    Misc Sessions: http://www.sqlpass.org/sqlrally/2011/PreConsforVotingMisc.aspx

    I have a session submitted for the Misc area, which is Finding Your Dream Job, a combination of my Modern Resume work and Chris Shaw’s various presentations on getting a new job. We decided that a half day would be a good way for us to give you practical advice on ways to get a better job, or just a job.

    Whether you vote for us or not, I think this is a great way to get community feedback. I’ve been suspect that PASS has any idea what people want to see, relying on previous year’s limited data. This at least allows the community to have a say in what gets picked.

    I would prefer that voting results were tabulated immediately so we could see what the response is. Based on my experiences, I’m slightly concerned that the community will be over-ridden if PASS thinks something will sell better.

    Also, consider attending the SQL Rally in Orlando next May, especially if you find that the price of the Summit is too high. At an estimated $300 to attend, even with a hotel and some travel, you could get a tremendous amount of SQL training and networking for around $1000. And to me, this is way more valuable than sitting in a classroom for 5 days. Think about it, this could be a great investment in your career.

  • SQL Server 2008 – CDC Retention

    I had done a little work with CDC last year, experimenting with the way that it handles changes in your database. Someone had asked me the question about the retention period since the default of 3 days was not sufficient for their environment.

    BOL lists the default retention period as three days, and this is based on a cleanup job that goes through and removes data from the tables. However you can alter that with a stored procedure: sys.sp_cdc_change_job.

    There are a number of parameters for this procedure, most dealing with managing the load of the cdc jobs. You can alter the cleanup or capture jobs with this, but for the retention period, you want to change the cleanup job.

    @retention is the parameter that indicates the number of minutes that change rows are going to be retained in the CDC tables. If you pass in a NULL, the old retention period is retained. You can include the number of minutes you’d like, up to 52494800 (100 years). I would recommend you choose something less.

    The other thing to be aware of is that you have various intervals that are set, and also a @threshold, which determines how many rows are deleted on each scan. If you limit the cleanup to something less than continuously operating, then be aware that if @threshold is too low, it might not delete all the rows. If you are changing these values to manage the load of deletions, be sure that you are monitoring how many rows are changing as it might grow over time.

  • MERS

    Imagine you have this idea. You’ll build an electronic database designed to record rapidly changing data for assets that move between companies. You make contacts with the various companies and your system acts as the middleman. You are the central database, reducing the costs these companies have by trying to keep their own systems in sync. But what happens when your database becomes the legal record and it’s not up to date?

    That is what might have happened with the MERS system. It is supposed to record the mortgage titles, linking homeowners with their mortgage holder. Since mortgages are often sold many times during their lifetimes, MERS was supposed to reduce the costs and time involved with each sale. By having MERS maintain title, recording fees and time is reduced as everything is centralized. Theoretically, this is what we want centralized databases to do: make things more efficient and accurate.

    But that’s not always what happens. We make mistakes, we have coding bugs, and a single database could easily have many mistakes in it. In fact, this is why having a single database for a distributed system could be a bad idea. Multiple databases can allow you to compare data, and perhaps track down any discrepancies between systems.

    This is one reason that a data warehouse can be a valuable addition to your company. By taking feeds from multiple systems, and standardizing the information, it’s easier to use. However that ETL step can provide a valuable double check of your data, and a good feedback look can enable you to find data quality issues and correct them.

    I don’t know if the MERS system has data quality issues, but if they are going to be a legal system of record, their data has to be accurate.

    Steve Jones