Voice of the DBA

Author: way0utwest

  • Kill a SPID

    This editorial was originally published on Sept 5, 2007. It is being re-run as Steve is traveling.

    I know some people get a kick out of running the KILL command. Heck, I’m sure all of us enjoy stopping a runaway process from an annoying user at times. But not everyone seems to understand exactly how databases work. It’s not necessarily a knock on the original poster, after all, most of us had to learn about the ACID properties at some point. Perhaps even after someone dropped a database in our lap without warning.

    If you kill a spid, I saw some confusion about data loss in a recent thread. You won’t lose data, but you could still have a problem. It’s not a technical problem and the storage engine inside SQL Server ensures that the transaction conforms to the ACID properties to ensure data integrity.

    The users, however, might or might not realize that their “work” was not done. It depends on the error handling of your application, what message (if any) that is presented to the user, and if they’re even still around. I’ve seen people start processes they expected to run long and then leave their workstation. If there wasn’t a message on it later, they might assume the transaction had gone through.

    Even if you tell them you’re killing the process, they might still think that some amount of work is completed. There are all sorts of users, with all different kinds of expectations out there, so you should be sure that they understand exactly what is happening.

    And be sure that they know their “work” is lost. From the point of view of someone doing data entry, if the transaction rolls back and the application can’t handle it, they will have to enter information again. So their work is essentially lost.

    Those of us in technology sometimes forget the impact of our systems in the real world. Even when things work well or as designed, they may still be a problem for real people that have real tasks to get done.

  • One Single View

    This editorial was originally published on Oct 9, 2007. It is being re-run as Steve is traveling.

    When I worked at JD Edwards, one of the goals of our business intelligence system was to house a single view of the truth. I recently saw a blog post by Andrew Fryer that does a good job of explaining what this is. Basically it’s a way for us to view some particular slice of data and ensure that it is consistently accepted by everyone in the company as the “correct” data for whatever it represents.

    This sounds a little silly, but it’s actually a problem in many companies. At the recent PASS Summit, Bill Baker did a presentation where he actually showed a realistic example of this as a reason to implement Performance Point Server. If you have a contest at a company for the most sales, who wins?

    You’d think this is easy, but is it the person with the most dollar sales? Do returns count? Should we account for size of a store or hours worked? Obviously we could define this, but depending on how different people might run reports or calculate things in their own spreadsheet, there could be different results.

    It was the first time that I actually understood what Performance Point brings to a company and why you might implement it. Now I’m not plugging Performance Point here, because I think you could wind up with some tool that requires a couple full-time administrators and developers just to get things set up and maintained. And I’m not sure that you can completely control things with permissions and reports. My guess is that people will always want to pull their own data offline and create their own report (often for the purpose of supporting their own position), but it’s an interesting idea.

    The single view of the truth is something I think all DBAs and “data people” want. We want to know that a customer is a customer is a customer. We want to normalize data and have relations that ensure we aren’t duplicating data. We fight through the issues of meanings in one system being translated properly to the next system.

    A single view of the truth is hard to create, but it’s a goal that I think is worth pursuing. And implementing a data warehouse is a great way to get started on this. By talking with business people and forcing them to give you rules and mappings, and then implementing a source system everyone can use, it can really ensure that everyone in the company is on the “same page.”

    And who knows? Forcing business people to define what that single view is might just help them run the business better.

    Podcast Notes

    Joe Sibol - The Great MusicI appreciate any and all feedback from people

    Music from Joe Sibol. I like acoustic music and stumbled onto Joe recently. If you like it, send her a donation, buy a CD or something.

    And if you’re in a band, send me a sample of some music. I’d love to feature some SQLServerCentral.com community talent.

    Podcasts:

  • A Long Day

    Flying all night from Houston, arriving in London at 6:40am, taking a train into the city and then finding a Starbucks to work in was the agenda for today. It worked out, but it feels like a long extension of yesterday, with very little sleep on the plane.

    Hotels don’t usually let you check in this early, which means that I’m struggling a bit to concentrate in the  center of London, people wandering in and out of the Starbucks by my hotel. Hopefully I’ll get checked in soon, and get some rest and then work in a little piece on my presentations for tomorrow. They’re done, but I want a little more practice.

    Traveling is always a little challenging when you need to get work done and you are out of your routine. Add in the lack of cell phone service in another country, and it’s a tough spot to be in today. Fortunately I got most things done before I left home, just working on a few Database Weekly things, which are easier to handle in a busy atmosphere.

    At least I found a Starbucks.

  • Time Zones

    Why don’t we have a function like the one in this article to convert from time zone to time zone? Seems like something SQL Server should have added awhile ago.