RE: Dead Zune Club

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.

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.

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.

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.