[thelist] [SQL] Slow queries/sp's

[Ken Schaefer]

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: thelist-bounces at lists.evolt.org [mailto:thelist-
: bounces at lists.evolt.org] On Behalf Of Joshua Olson
: Subject: [thelist] [SQL] Slow queries/sp's
: 
: Hi list, I need a few pointers.
: 
: On a web project, we are running a few sp's to 
: do a bunch of processing. Most of the times, the 
: SP's take less than a minute to execute.
: Sometimes, as of late, the sp's are taking 
: over 20 minutes to complete.
: 
: I have two theories.
: 
: 1.  Something is stealing CPU cycles from the 
: SQL Server 2k instance.
: 2.  There is a race condition somewhere in the 
: web of tables and table access.
: 
: It's not feasible to monitor the server 
: manually 24/7.  Anybody have luck
: with tools to help track down what's going on?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

My thoughts:
You can use Perfmon to monitor CPU
You can use SQL Profiler to monitor what SQL Server's doing.

That said, are these scheduled task/maintenance job/batch process type
things? Once they start running slowly, do they continue to run slowly? Or
does the next time you run them, they run fast? What else is running at the
same time? Have you updated your statistics?

Cheers
Ken

--
www.adOpenStatic.com/cs/blogs/ken/