Voice of the DBA

Author: way0utwest

  • Hints Are Not Always Better

    Is this better than an index scan?

    I have always thought that an index seen was preferable to an index scan. It seems like the general rule that so many DBAs and developers follow, looking to convert every scan in an execution plan to a seek. Often that results in better performance, and I’ve seen many people resort to using hints to enforce this behavior in SQL Server when the query optimizer (QO) or Query Processor (QP) fails to choose their indexes.

    This past week Rob Farley wrote a great blog post that taught me something about seeks, scans, and the fact that one is not always better than the other. It has a great title and is worth a few minutes of your time to read: Covering, schmuvvering – when a covering index is actually rubbish. In the post Rob shows that a seek can be worse than a scan in some cases, in his example due to a Residual Predicate.

    I have seen so many people mistrust the query processor in SQL Server over the years, often resorting to hints when it seemed that the best index wasn’t being chosen. I’ve felt like doing that before as well, spending afternoons cursing the developers at Microsoft that their product wouldn’t choose an index that I knew was a better choice.

    Over the years I’ve talked with the people that build the code behind the query optimizer and often it seems someone is submitting a bug in the way the QO/QP works. Most of the time, however, I find my respect growing for that team, and often find that the individual is falling victim to the “it works on my machine” syndrome. Too often someone is observing a single case, a single data set, and limited concurrency, all of which can drastically change the performance of a query on your system when they grow.

    SQL Server doesn’t have a perfect QP/QO system, but it has a very, very good one. Using too many hints almost feels like hard-coding a value in the system. There are times that it makes sense, but they are very rare.

    This post also reminds me that there are so many things to learn about SQL Server, and gaining a deeper understanding of how the internals of SQL Server work can pay off with much more efficient, and scalable code that handles your load as it grows.

    Steve Jones

    PS – This post makes me want to see Rob’s pre-conference session this October at the PASS Summit. Hopefully he will get picked and many of us will get the chance to learn more nuggets like this one.

  • Collation Conflicts in a SQL Server Join

    I went to run this query recently: 

    select TOP 10 * 
 from users a
   inner join Banned b
   on a.username = b.username

    and got this lovely message.

    collation

    I’d seen that message before, so I knew what was wrong. The collations for the two tables were inconsistent. Since this was a database that was upgraded from another version of SQL, and uses objects from a third party, I wasn’t surprised that a specific collation was used. I had created the “b” table myself, using database defaults, and they didn’t match the object.

    I did a quick search since I couldn’t remember the exact syntax for the clause to add to my query. I ended up at a friend’s blog, Pinal Dave’s SQL Authority, and read this post: Cannot resolve collation conflict for equal to operation.

    The fix is easy, add a COLLATE DATABASE_DEFAULT to the join condition to force a specific collation on the field. I could easily have added a COLLATE Latin1_General_CI_AS as well, but since I knew that the second field was database defaults, I did this:

    select TOP 10 * 
 from users a
   inner join Banned b
   on a.username COLLATE DATABASE_DEFAULT = b.username

    Worked fine, and I was on my way.

  • Which Bugs Do You Find?

    Software bugs are usually not this easy to find.

    I was listening to a StackOverflow podcast awhile back and there was a discussion on  programming, debugging, and how software has changed. One of the interesting things was that we have solved many programming issues and are building more complex software, which has resulted in the types of bugs that we often find today are different than the bugs many of us older folks used to deal with in programming.

    When I was writing code as a youth, we often had bugs that were process bugs. We were trying to get software to do X, and struggling with actually how to tell the computer that X should occur. I heard a few people on the podcast mention that they typically didn’t have those bugs too often, at least not once the software was at a point where they would release it for general use. Instead they talked about finding bugs that were issues because the flow of the software wasn’t what the user expected. These bugs were problems in terms of actually determining the way the software should function in order for it to be useful for the user.

    That’s an interesting idea. As we’ve built more frameworks, abstraction layers, and other tools to help us program, you would expect that many simple bugs, things like issues with memory, buffer overflows, etc. would become less prevalent, and more often we’d have these flow bugs that are based on how the software is used. It’s a topic that begs this question for a Friday poll:

    Do you think your code has more bugs because of problems building the algorithm in code, or because of a lack of understanding in how the user will interact with it?

    With more “agile style programming”, rapid development and changing of requirements, one would think there are more of the latter bug. However let us know this week what you think. Reflect on the issues that you have had in writing code, reports, user interfaces, even more complex calculations. Are there more problems with actually understanding the design of what you need to build, or the actual implementation once you understand it.

    Steve Jones

    The Voice of the DBA Podcasts

  • IT and Innovation

    This is a powerful quote:

    “This is the digital world IT must keep up with–one where Apple can view a $40,000 car as an accessory to the iPhone.” – From IT Is Too Darn Slow

    The IT world definitely needs to learn to move quicker, and that means that we need to be more solid in our infrastructure. The basic tasks of setting up a server, getting it configured, building indexes, setting up maintenance, etc. need to be smoother and easier that more time, and more importantly, more resources can go to building new systems.