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.
- CDThis is a multi-part message in MIME format.
--=_NextPart_000_0074_01C3EB4D.F4E4CB70
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
How 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
--=_NextPart_000_0074_01C3EB4D.F4E4CB70
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
How 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, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
--=_NextPart_000_0074_01C3EB4D.F4E4CB70--|||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, OR|||Are you also using VB transactions? I found performance
improvement by issuing transactions only from my stored
procedures. I believe VB trans cause SQL to SET
IMPLICIT_TRANSACTIONS ON which can increase lock
contention & network round trips. I also saw
improvements by using disconnected recordsets.
Darren Fuller
SQL Server DBA MCSE
>--Original Message--
>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
>.
>
