SQL Server backend poor performance

I have converted an Access back-end to SQL Server back-end but am
having some problems. The Access to Access application has been
running well for some years.

I have successfully copied all the data to SQL Server and linked the
tables to the front end .mdb (I am not using .adp). Some queries were
performing poorly so I have converted these to Views and linked to
them.

Everything works well with good response but when about 8-10 users are
in the whole thing slows down to an unbearable extent. Using the
profiler I can see that there are between 20 - 30 connections during
the time that response is good, but suddenly with no apparent increase
in the number of connections the whole system grinds to a (near) halt.
This morning response was terrible with only about 3 users and fewer
than 10 connections.

I am at a loss as to how to analyse the problem. I cannot expect
others to solve the problem but I would welcome guidance on how to
explore the poor performance.

I am using DAO but wonder whether I should move over to ADO. I have
studied the book by Chipman and Baron and could convert the VBA code to
use ADO. What I cannot see how to do is specifically use ADO for such
things as bound forms or combo-boxes. If a form is bound to a query or
to an SQL statement how does one choose whether this uses DAO or ADO?

I am seeking help on three topics:
1 what tools or techniques are available to track down the causes of
poor performance?
2 should I change to ADO?
3 how do I make bound forms use ADO?

.

Relevant Pages

  • Re: Cross Transactions between ADO & ADO.Net
    ... There is one thing that might help here, if you're using SQL Server. ... SQL Server supports something called "bound sessions", where two connections ... > some code will be ADO & some code will be ADO.Net. ... > the DTC cordinator from COM+ to handle the transactions between ADO.Net & ...
    (microsoft.public.dotnet.framework.adonet)
  • app roles and ADO connections
    ... I'm having problems with the combination of SQL Server ... application roles and ADO connections. ... problematic since the approles is strictly a SQL Server ... In some cases ADO will create additional connections ...
    (microsoft.public.sqlserver.security)
  • Re: Check a connection is closed
    ... > Is there a way which I can view on SQL Server which will tell ... > me if any programs running queries against the server are leaving ... > connections open? ... The reason for the datediff is that ADO employs connection pooling, ...
    (microsoft.public.sqlserver.programming)
  • Re: Access 2007->SQL Server2005 "connection was forcibly closed",G
    ... connections need to be returned to the pool to be ... Enterprise version of SQL Server 2000. ... server user login to be sure that it is not mixed with other running clients). ... Every new client opens again 30 connections if I open 30 tables ...
    (microsoft.public.sqlserver.connect)
  • Re: Access 2007->SQL Server2005 "connection was forcibly closed",G
    ... an Access database in former times this was a lot of work which took me ... closed connections - but all of these errors are in the version which used ... the SQL Server 2000 and everything worked ... client for programming easy, fast and good applications. ...
    (microsoft.public.sqlserver.connect)
Loading