Degrading performance. Expert help appreciated

Greetings,
In our current project, we use a combination of SQL Server 2000 and
ADO. Our database is very simple and small: just about 10 tables with
300K records at most (in just one of the tables). We need, however,
very fast responses to our queries. With that in mind, we designed and
optimized all queries and indices in such a way that each query takes
less that 20 milliseconds, as measured using the SQL profiler under a
normal load.
Each client application opens a single connection to the database and
the queries are funneled through that connection. Each query is a
individual transaction, i.e. it is fenced by Begin Tran...End Tran. We
use mostly stored procedures, which are executed via the _Command
object from ADO. In just a couple of cases, we use _Recordset.
Under a *stress* load, one client can submit 20 transactions/sec to
the server.
In this scenario, I noticed that, sometimes, many commands were taking
almost two orders of magnitude more than under the normal load. I used
SQL Profiler to monitor all Statements and SPs taking longer tha 100
msec and, to my surprise, found that, every so often, some command or
SP would take more than 1-2 seconds. What is interesting is that many
of these commands are IF @.@.TRANCOUNT > 0 COMMIT TRAN, which, I think,
ADO implicitly sends to the server. Those usually show with duration 0
(Zero) under normal load.
What I wanted from you, were some ideas on how to go about
troubleshooting this problem, by identifying the underlying cause for
such poor performance. The problem does not seem associated with a
particular command or SP. It also does not seem related to CPU
contention on the server because it is kept really low (about 20%).
Your help is greatly appreciated.
- CDHow necessary is the Begin Tran/End Tran? If you don't need to make a
series of updates atomic then leave this out.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
<crbd98@.yahoo.com> wrote in message
news:edf41e63.0402041449.724d721b@.posting.google.com...
Greetings,
In our current project, we use a combination of SQL Server 2000 and
ADO. Our database is very simple and small: just about 10 tables with
300K records at most (in just one of the tables). We need, however,
very fast responses to our queries. With that in mind, we designed and
optimized all queries and indices in such a way that each query takes
less that 20 milliseconds, as measured using the SQL profiler under a
normal load.
Each client application opens a single connection to the database and
the queries are funneled through that connection. Each query is a
individual transaction, i.e. it is fenced by Begin Tran...End Tran. We
use mostly stored procedures, which are executed via the _Command
object from ADO. In just a couple of cases, we use _Recordset.
Under a *stress* load, one client can submit 20 transactions/sec to
the server.
In this scenario, I noticed that, sometimes, many commands were taking
almost two orders of magnitude more than under the normal load. I used
SQL Profiler to monitor all Statements and SPs taking longer tha 100
msec and, to my surprise, found that, every so often, some command or
SP would take more than 1-2 seconds. What is interesting is that many
of these commands are IF @.@.TRANCOUNT > 0 COMMIT TRAN, which, I think,
ADO implicitly sends to the server. Those usually show with duration 0
(Zero) under normal load.
What I wanted from you, were some ideas on how to go about
troubleshooting this problem, by identifying the underlying cause for
such poor performance. The problem does not seem associated with a
particular command or SP. It also does not seem related to CPU
contention on the server because it is kept really low (about 20%).
Your help is greatly appreciated.
- CD|||sounds like some blocking issues.
when you run your tests, monitor blocks in SQL Server (and\or Deadlocks)
do your sprocs have NOLOCK Hints in them when appropriate ?
Or setting transaction isolation level to READ UNCOMMITTED (Where
Appropriate).
probably worth looking into.
cheers
Greg Jackson
PDX, ORsql