“Can you tell me a little about this code?” I asked, keeping my best poker face on.
“I’m really proud of it. It gathers a whole bunch of stuff from accounting, human resources, manufacturing, you name it. I want to be able to track how we’re doing over time.” He nearly hovered in his chair with excitement, eagerly pointing out the different places he was gathering data from. “It’s like having my finger on the pulse of what matters most to the company.”
“That sounds really cool. And there’s some really fine-grained stuff in here – it looks like you really are taking the pulse of the company. How often do you run these queries?”
“It depends – some of them every day, some of them every minute because I need better sampling on those.”
I started to ease into the tough questions. “Every minute, wow. Are you concerned about the overhead?”
“No no, they’re really small queries, only took me a few minutes to write. And it’s not like I’m doing SELECT *,” he laughed.
“Oh, good. And did you index the tables involved? Sometimes adding indexes can really help ease the load of frequent queries.”
He paused for a second. “I never thought about that – but I can’t, right? I can’t add indexes on that kind of thing, can I?” Suddenly he realized he was in unsafe territory.
“No, I guess not. I wonder why that is. But who cares – it’s gotta be worth the cost to get such valuable information, right?”
“Oh yeah! You bet.”
“So how often do you query that data?”
He paused again. “Well, not very often. I’m pretty busy.”
“Did you query them today?”
“Yesterday, or last week, maybe? Like before you called me in?”
His enthusiasm was gone, and he eyed me with suspicion.
“You’re way too busy to query this data, aren’t you? You’re busy putting out fires, fixing people’s mistakes, and keeping the lights on. You don’t really need this data gathered every minute, across all of your servers, do you?”
See, that user was the DBA, and when I showed him the top resource-using queries in the plan cache, they were all his monitoring queries. Queries that hit tables he couldn’t possibly index, because they weren’t actually tables at all – they were dynamic management views that SQL Server had to compute each time he accessed them. Queries that faithfully stored away data in tables he’d never bother to review.
And now you know the rest of the story.