Performance clues

I have a web application that calls stored procedures that sometimes timeout
at 30 seconds. We cannot do it at will, so it makes troubleshooting difficul
t.
I happened to have profiler running during one of these timeout episodes. Th
e
duration was in the 30 second range for the stored procedures, but the reads
were very low (I did not trace for CPU or Writes). Performance Monitor on th
e
CPU was within an acceptable range, as were the Buffer Cache Hit ratio (abou
t
96 %) and the Disk Queue length.
When you have high duration and low reads, does that indicate an area to
pursue.
Using SQL Server 2000, SP4, on Win 2003.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200611/1> When you have high duration and low reads, does that indicate an area to
> pursue.
Looks like a blocking situation to me...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:68fe0ef849e6f@.uwe...[vbcol
=seagreen]
>I have a web application that calls stored procedures that sometimes timeou
t
> at 30 seconds. We cannot do it at will, so it makes troubleshooting diffic
ult.
> I happened to have profiler running during one of these timeout episodes.
The
> duration was in the 30 second range for the stored procedures, but the rea
ds
> were very low (I did not trace for CPU or Writes). Performance Monitor on
the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio (ab
out
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>[/vbcol]|||cbrichards via droptable.com wrote:
> I have a web application that calls stored procedures that sometimes timeo
ut
> at 30 seconds. We cannot do it at will, so it makes troubleshooting diffic
ult.
> I happened to have profiler running during one of these timeout episodes.
The
> duration was in the 30 second range for the stored procedures, but the rea
ds
> were very low (I did not trace for CPU or Writes). Performance Monitor on
the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio (ab
out
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
>
Some other process is blocking the one in question...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Blocking. Is anyone using Enterprise Manager to work with the data?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:68fe0ef849e6f@.uwe...
>I have a web application that calls stored procedures that sometimes
>timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting
> difficult.
> I happened to have profiler running during one of these timeout episodes.
> The
> duration was in the 30 second range for the stored procedures, but the
> reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on
> the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio
> (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>|||As others have already said, blocking is one very common cause of this
behaviour but database file growth during proc execution is another (monitor
for this with the profiler's file growth events).
96% Buffer Cache Hit Ratio isn't actually very high - you could very easily
be experiencing memory problems with BCHR at 96%. It's worth having a look
at the BufferManager's Page Life Expectancy counter as well - it measures
how long buffered pages are surviving in cache before being forced out by
pressure from other memory consumers. If this number falls during long
execution of your proc, it could be that other factors are indirectly
influencing this problem
HTH
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:68fe0ef849e6f@.uwe...
>I have a web application that calls stored procedures that sometimes
>timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting
> difficult.
> I happened to have profiler running during one of these timeout episodes.
> The
> duration was in the 30 second range for the stored procedures, but the
> reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on
> the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio
> (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>