Voice of the DBA

Author: way0utwest

  • From Great Idea to End Result

    Great ideas come from anywhere

    What’s the time for you IT department to get from great idea to a resulting application? This is a very good piece from CIO magazinethat finds many IT departments are seen as too slow. However there are a number of companies that are trying to innovate and find ways to increase the speed at which IT departments can deploy an application and respond to a business need.

    One great quote in there is “velocity is more important than perfection”,  which is a tenet that I have found to be very true over the years. It’s not that you throw junk out that isn’t well built or tested, but that you don’t try to meet every possible requirement or handle every little issue. The system has to be secure, handle errors, and meet the basic requirements, but it’s more important to get something done and in production than to have it perform and scale perfectly.

    Is that heresy to the developers and DBAs out there? Perhaps, but I think this methodology has to go hand in hand with another mantra I heard fromJason Fried: do more of what works and less of what doesn’t. In this case if a system shows promise and starts to get heavy use, it receives more resources and perhaps gets refactoring in real time, even as it gets enhanced with new ideas.

    “You want IT to be in constant test-and-learn mode”, another quote showing that IT needs to be working closely with the business to try ideas, learn from them, and move forward. The Agile style of development applies, and in some sense I think this is the future for the strategic IT department of the future.

    For the data professional this means that you must learn to model quickly, and with an eye towards a flexible design that might need to change regularly. We need to understand the businesses we work in better so that we can anticipate how requirements might change.

    Management has to buy into the idea that applications will not be perfect, they won’t be polished, and most importantly, they are essentially prototypes that either need to have addition resources spent on enhancements or they should be abandoned quickly. However I think this is a great way to develop internal applications that can provide a nice ROI, and be a more enjoyable way for developers to work.

    Steve Jones

    The Voice of the DBA Podcasts

  • 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