Re: Dead Zune Club

On 13/10/2011 3:08 AM, Chris Wuestefeld wrote:
> John, as you describe it, the problems seems to be time related, right? So
> the first place I'd look is scheduled behaviors that differ between weekdays
> and weekends. Check the SQL Server Agent, as well as Windows scheduled
> tasks.

Yeah, it seems to be time related.

Off the cuff I think it's probably not scheduled activity but just 
typical work week usage that's creating the problem. Will double check 
what's scheduled though (the only scheduled tasks I know of at the 
moment are scheduled to run on the weekend).

> Another thing to consider (but not first, 'cause I've only seen this happen
> once in a blue moon) is the way SQL Server's optimizer works. The first time
> it runs a query, it optimizes for index metrics, etc., with respect to the
> actual values of the input parameters, and caches that query plan.
> Sometimes, for complex stored procs, the set of input values isn't
> representative of normal usage patterns, and a sub-optimal plan gets stored
> and used repeatedly, even though it's garbage. Something as simple as having
> a NULL value for a WHERE criteria on the first execution can cause this.
>
> Your description could fit this like so: peak usage is through the week, so
> the problem query keeps getting hit and stays in cache. Over the weekend,
> usage is lighter (and different), so the problem query can fall out of the
> cache. What's more, the weekend usage may be utilizing the same thing in a
> different manner, causing it to enter the cache with an odd query plan. So
> Sunday afternoon's call gets stuck in the cache all through the week, even
> though it's terrible.

I'm not sure I've understood everything that you've told me here. It 
makes me feel as if my SQL Server isn't the only thing that's slow 
around here. :P

> Of course, you've got to figure out what code to analyze first. A shotgun
> approach isn't going to help there, because you've really got to go over the
> code in question with a fine-toothed comb. But tools like the Activity
> Monitor's "Recent Expensive Queries" can help you locate a problem query.

OK, thanks for the tip.

> Once you find a sub-optimal query, the best tool I've found to understand
> what it's doing is the "SQL Sentry Plan Explorer", a freeware stand-alone
> tool. Google for it.

OK, will do, thanks.