I Hate Wasting Time

…don’t you?

It’s one of the reasons why I love using the waits and queues methodology for performance tuning. Analyzing the wait events helps me narrow my focus on the specific resource bottleneck. I like to “rule out” potential causes as I go, arriving at a limited set of likely culprits. Sometimes I feel like Columbo:

Me: Just one more thing, you said that ‘nothing changed’ since yesterday, but isn’t it true that you personally loaded nearly a terabyte of data yesterday afternoon?

User: Yes, that’s correct.

Me: And you expected your query to run just as fast? 

User: Of course. Is that a problem?

Me: No, no, of course not. Just one more thing, how long have you been dead from the neck up?

And wasting time also why I was apprehensive about moving towards a virtualized platform. When you go virtual you introduce additional layers of complexity, additional points of possible resource contention. All the more ways to spend additional time trying to hunt down the likely cause of an issue. I hated the idea of not knowing such details.

Quick show of hands: How many of you examine a query (or an execution plan) as soon as a customer complains about performance? Right, we all do that. It’s a natural thing to do, really. You go to the doctor and tell them where it hurts and they examine that area. Fine.

But for those that have virtualized database servers, how many of you will go to examine the virtual layers before looking at that query plan? If you don’t then you are likely wasting your time. If a batch job went from one minute to two minutes and you are asked (i.e., demanded) to find out why, wouldn’t you want to know if anything at the virtual layer contributed to that increase? For example, perhaps the host has an additional four guests running on since yesterday, and they were all competing for system resources at the same time. That would be good to know, right?

As systems become more complex and our data centers become self-aware it is going to be harder and harder for us to determine where the bottlenecks are. Start getting used to the idea of playing detective. Start ruling out specific items and focus on the ones that remain, and do that before you start looking at code. You’ll save yourself time in the long run. Time you can spend reading Dilbert cartoons.