SQL Server backend poor performance

[Jim Devenish]

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?

[Rick Brandt]

Jim Devenish wrote:

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.

My first guess would be to see if the performance issues are being caused by
locks being held on the tables/views. That is an area that can make a big
difference with just a few processes beign blocked.

Using SQL Server links to populate ComboBox and ListBox RowSources is a good
way to get a lock applied to that table. Even though that is just a READ
operation if the list is long enough Access will cache in a few pages of
rows and then hold a lock on the table until the entire list is pulled. If
the user never goes to the end of the list and keeps that form open the lock
can persist indefinitely.

I doubt that the DAO/ADO thing would make more than an incremental
difference.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Jim Devenish]

Rick

Thanks for this suggestion. Excuse me asking further but although I am
used to Access I am new to SQL Server.

How do I explore these locks? i.e. which tables/views have locks at any
time. Is there an SQL Server command to give information about these?

Jim

Rick Brandt wrote:

>
My first guess would be to see if the performance issues are being caused by
locks being held on the tables/views. That is an area that can make a big
difference with just a few processes beign blocked.

Using SQL Server links to populate ComboBox and ListBox RowSources is a good
way to get a lock applied to that table. Even though that is just a READ
operation if the list is long enough Access will cache in a few pages of
rows and then hold a lock on the table until the entire list is pulled. If
the user never goes to the end of the list and keeps that form open the lock
can persist indefinitely.

I doubt that the DAO/ADO thing would make more than an incremental
difference.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Rick Brandt]

Jim Devenish wrote:

Rick

Thanks for this suggestion. Excuse me asking further but although I
am used to Access I am new to SQL Server.

How do I explore these locks? i.e. which tables/views have locks at
any time. Is there an SQL Server command to give information about
these?

The SQL Server management tools (Entreprise Manager or Management Studio)
both have utilities for looking at "current processes" which will show locks
and whether any processes are currently being blocked by other processes.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Terry Kreft]

Tools to use are profiler and query analyser; profiler shows you what is
happening, query analyser allows you to look at query plans so you can see
where bottlenecks can occur.

Look closely at your indexing. You can inspect the relative effect of
adding/removing indexes using query analyser. Sometimes an index actually
decreases performance so each time you tweak check out the performance hit.

Check locking, you can do this either using Enterprise Manager or in query
analyser (look at sp_lock in BOL).

Views, personally I hate views, I have seen them abused so often and if a
system is performing poorly it is the first thing I look at. A chum of mine
ended up writing an analysis tool for views because we had so much trouble
with people writing something which worked ok when the system was installed
but then ground to a horrendous halt when the system was put under load.

If you are using linked tables then look at using pass-through queries for
large data updates or for pulling lists you could also look at using local
tables for static lists.

ADO can give a significant performance boost over DAO but not in every case
and is not a cure all option by any stretch of the imagination, it is
something you should look at in order to tweak performance not to try and
rescue a sick dog.
--

Terry Kreft
"Jim Devenish" <in***************@foobox.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...

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?