I was all proud of myself today, and bragged a little bit on Twitter about a query I'd written. That query gets its own blog here shortly, but first, a worthwhile Twitter conversation with Adam Machanic (@AdamMachanic on Twitter, Adam's blog is on SQLBlog.com) that turned very, very useful:
Me: Okay, that cool query from yesterday that correlates lock timeouts to their queries? Yah, just modified to find… (1/2)
Me: …RPC start statements that have no correlated RPC complete statements within a timeframe. Shows what's bunging up the CPU.(2/2) Will blog!Adam: Why are you hitting lock timeouts on a regular enough basis to need such a query?
Me: BizTalk transactions + UPDLOCK on SELECT
Adam: BizTalk overrides the lock timeout setting?
Me: I don't think so (not sure), but we've got maxed CPU and 2 SPs that run again & again, lotsa waiting & timeouts.
Adam: It sounds like you're referring to "client" timeouts, not "lock" timeouts, if I'm understanding what you're talking about.
Me: In a SS-profiler trace, we have tons of lock timeouts (eventclass 27) showing up. I can associate those w/the SPIDs (1/2)
Me: …that were holding the locks when they timed out. Tells me what's causing the lock contention, which I suspect is (ok 2/3)
Me: contributing to the CPU maxout. Sound crazy? (3/3)Adam: Ah, I see what you're doing. I don't think a lock probe is very expensive but I guess if you're getting millions it adds up.
Me: Suppose emphasis should be more on the high CPU ,& longrunning queries that're CAUSING the timeouts, not the timeouts thmselvs
Adam: Is the duration on the events > 0? If not, they're not 'really' timeouts, just an indication of a lock wait.
Me: HOLY MOTHER OF POTATOES! They're all waits…I so didn't know that, thx
Adam: Glad to help 🙂
Me: I even RTFM, and BOL doesn't make that clear. I can see now how the article would mean that. But now I hav acheived clarity!!
Adam: Glad to help 🙂
Adam: "I was just sitting here, eating my muffin, drinking my coffee, when I had what alcoholics refer to as a moment of clarity."
After I wrote the "HOLY MOTHER OF POTATOES" line, I went and looked up Lock Timeouts in BOL. And yes, it does mention this, but it's terribly cryptic to the untrained eye (like mine):
Lock:Timeout events with a duration of 0 are commonly the result of internal lock probes and are not necessarily an indication of a problem. The Lock:Timeout (timeout > 0) event can be used to ignore time-outs with a duration of 0.
Yeah, I read that two years ago, and disregarded it as making no sense.
So there's our tip for the day! Lock Timouts with duration = 0 aren't really timeouts. And, it's a good segue into the next blog, so…
-Jennifer McCown