February 4, 2004 at 3:54 pm
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
February 4, 2004 at 4:32 pm
Blocking or deadlock?
http://support.microsoft.com/default.aspx?scid=kb;en-us;271509&Product=sql2k
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply