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 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 SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-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 SQLMonster.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 SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>|||cbrichards via SQLMonster.com wrote:
> 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.
>
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 SQLMonster.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 SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-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 SQLMonster.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 SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>

Performance & Timeout during auto grow in SQL 2000

We had a downtime in our Stress environement during Database Automatic Data
file grow.
The database was preallocate to 20Gb with auto increment 10%.
It seems the reallocation of the 2 Gb take minutes.
We are trying to get Best Practices in SQL 2000 to see what is recommended.
- Having a Dedicated DBA to check DB space available?
- Use or Not Auto Grow? use % versus fixed size?
The idea here is to get business justification and/or use case to help us
deciding Dedicated DBA versus specific tools & technologie.
Hi
Auto grow should only be a last resort. Create the DB big enough, and then
check on it daily/weekly. You can setup an alert to fire an e-mail off if the
free space gets too low. If you have the space, grow the Db to 30Gb when the
server is not too busy.
Regards
Mike
"Fabrice PIERRE" wrote:

> We had a downtime in our Stress environement during Database Automatic Data
> file grow.
> The database was preallocate to 20Gb with auto increment 10%.
> It seems the reallocation of the 2 Gb take minutes.
> We are trying to get Best Practices in SQL 2000 to see what is recommended.
> - Having a Dedicated DBA to check DB space available?
> - Use or Not Auto Grow? use % versus fixed size?
> The idea here is to get business justification and/or use case to help us
> deciding Dedicated DBA versus specific tools & technologie.
|||Hi ,Mike
I'd go with creating a database big enough with auto-grow feature.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ACB025A7-3ACE-4E3D-9FD2-6695A1C3AAB1@.microsoft.com...
> Hi
> Auto grow should only be a last resort. Create the DB big enough, and then
> check on it daily/weekly. You can setup an alert to fire an e-mail off if
the
> free space gets too low. If you have the space, grow the Db to 30Gb when
the[vbcol=seagreen]
> server is not too busy.
> Regards
> Mike
> "Fabrice PIERRE" wrote:
Data[vbcol=seagreen]
recommended.[vbcol=seagreen]
us[vbcol=seagreen]

Performance & Timeout during auto grow in SQL 2000

We had a downtime in our Stress environement during Database Automatic Data
file grow.
The database was preallocate to 20Gb with auto increment 10%.
It seems the reallocation of the 2 Gb take minutes.
We are trying to get Best Practices in SQL 2000 to see what is recommended.
- Having a Dedicated DBA to check DB space available?
- Use or Not Auto Grow? use % versus fixed size?
The idea here is to get business justification and/or use case to help us
deciding Dedicated DBA versus specific tools & technologie.Hi
Auto grow should only be a last resort. Create the DB big enough, and then
check on it daily/weekly. You can setup an alert to fire an e-mail off if the
free space gets too low. If you have the space, grow the Db to 30Gb when the
server is not too busy.
Regards
Mike
"Fabrice PIERRE" wrote:
> We had a downtime in our Stress environement during Database Automatic Data
> file grow.
> The database was preallocate to 20Gb with auto increment 10%.
> It seems the reallocation of the 2 Gb take minutes.
> We are trying to get Best Practices in SQL 2000 to see what is recommended.
> - Having a Dedicated DBA to check DB space available?
> - Use or Not Auto Grow? use % versus fixed size?
> The idea here is to get business justification and/or use case to help us
> deciding Dedicated DBA versus specific tools & technologie.|||Hi ,Mike
I'd go with creating a database big enough with auto-grow feature.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ACB025A7-3ACE-4E3D-9FD2-6695A1C3AAB1@.microsoft.com...
> Hi
> Auto grow should only be a last resort. Create the DB big enough, and then
> check on it daily/weekly. You can setup an alert to fire an e-mail off if
the
> free space gets too low. If you have the space, grow the Db to 30Gb when
the
> server is not too busy.
> Regards
> Mike
> "Fabrice PIERRE" wrote:
> > We had a downtime in our Stress environement during Database Automatic
Data
> > file grow.
> > The database was preallocate to 20Gb with auto increment 10%.
> > It seems the reallocation of the 2 Gb take minutes.
> > We are trying to get Best Practices in SQL 2000 to see what is
recommended.
> > - Having a Dedicated DBA to check DB space available?
> > - Use or Not Auto Grow? use % versus fixed size?
> >
> > The idea here is to get business justification and/or use case to help
us
> > deciding Dedicated DBA versus specific tools & technologie.