Voice of the DBA

Author: way0utwest

  • T-SQL Tuesday #18 Coming Next Week

    Other than the week I hosted it, I’ve been caught off guard by T-SQL Tuesday most months. This time I caught Bob’s Twitter announcement about #18. The topic this month is CTEs, so you have a week to get things ready.

  • Cascading Human Error

    One mistake could cascade

    I caught this short piece on the Amazon EC2 outageand while it does highlight a potential problem in the world of the cloud, there was one part of it that really stuck out to me. This quote in particular was one that has me worried about the future: “It’s the kind of error an operator could makes as a wrong choice on a menu or the entry of the name of the last network worked on instead of the one needed. In short, it was a human error that’s all too likely to occur with anyone momentarily preoccupied with the price of mangoes or a flare up with a spouse. “ That’s a little scary, and while it’s not necessarily a reason to avoid the cloud, it is potentially a reason to avoid the extremely large companies like Amazon and Microsoft. They have built these large infrastructures, using lots of standardization and hopefully, automation. In the places where humans do have to type commands or make configuration changes, it’s possible that they make a mistake. While that can happen in any size company, when it happens for Amazon, Microsoft, or Google, a large number of people can be affected. In many ways I think we have a problem with extremely large, standardized, and highly dependent infrastructures. While being able to deploy a configuration change to a 1,000 servers at once or reboot 800 for a patch is cool, it’s also potentially a problem. We used to manage nearly 1,000 Windows servers and patch the large majority at one time with SMS, but we also held our collective breaths whenever hundreds of them rebooted at once. One mistake, and it makes for a very long night. And a very long day when you are explaining the issues to management. I don’t think we will ever eliminate human error, but we can definitely minimize it. Strong QA processes should require automated deployments and ensure the exact same deployment run on QA is run in production. If we can stick to that type of process, regardless of the delays it might create, we can minimize lots of human error. Steve Jones

    The Voice of the DBA Podcasts

  • Foreign Keys Help Performance

    I have always put FKs into my database for data integrity purposes. I’ve worked on enough applications that didn’t have FKs, or any RI in place and it was always a nightmare when the application broke down or there were enhancements that allowed duplicates, orphans, or other data integrity problems.

    However I ran across an old post form Grant Fritchey that shows Foreign Keys do more than that. They can actually help performance because the SQL Server database engine knows that there is data in the related tables that matches because of the FK relationship.

    Does that matter?

    If you read Grant’s post, and you should, it shows two different queries of the same data, but one has FKs enabled. That results in a much smaller execution plan, hitting fewer tables. I took Grant’s test and added one more twist.

    I ran both queries in the same batch, with the execution plan. Guess what I found? Check out this image:

    query1

    Guess which query has FKs and which one doesn’t? If you read Grant’s post, you’ll realize the first one has the FKs, but more importantly, if you look at the relative percentages of the batches, you see that there’s a 9x difference in resources.

    Use FKs. They do more than protect data, they speed things up.

  • The Intelligence Cloud

    The Air Force Academy Chapel

    I was thinking to call this the “Military Intelligence Cloud” but no one would believe it was true. Apparently the US Air Force has awarded a contract to build an intelligence platform that will allow different branches of the military and agencies to share data. And, by the way, they will be building a cloud based system.

    I’m not sure why the “cloud” is such a buzzword and why everyone thinks it results in cost savings. Supposedly a “private cloud” based system is being used for cost reasons, and because the public cloud services like Windows Azure and Amazon’s EC2 might not be secure enough.

    However what is a private cloud here? Isn’t it just an outsourced set of systems hosted elsewhere? It seems as though many companies and journalists are selling the cloud as some sort of new technology? Isn’t it just a set of hosted servers that are just virtual machines? Or is it just a set of servers that are standardized somehow in their configuration and capabilities?

    It seems that all the press, and all the commercials from Microsoft might be working, and the collective managerial sheep herd is flocking to the cloud as some amazing new change in the way their systems will work. If that’s the case, I’m not sure this new cloud service will be any more intelligent a move than and other outsourcing decision.

    I think there is promise in a more abstract, more ephemeral type of platform in the cloud, but it seems as thought we might be a long way from actually implementing something like that.

    Steve Jones

    The Voice of the DBA Podcasts