[dba-SQLServer] sql performance monitoring

[Arthur Fuller]

As Scott said, the first thing to look at is your queries. First, find all
data- and row-sources that begin with "SELECT" and replace those with named
queries. Next, convert all these named queries into SQL Views. (The reason
for this is that when Access uses a SELECT ... WHERE it first requests all
the data, then filters it on the local machine. When Access uses a SQL
View, the data filtering occurs on the server, not the local app. With a
lot of users, the difference can be dramatic.

If your forms contain tabs, then use Colby''s JIT form-style to postpone
the data-loads until they are needed.

Another technique, especially good for frequently-sed combo- and
list-boxes, is to declare a global variable for each list and populate them
all at once. That way they live in RAM and no round trips are required.
That can result in significant savings, too.

Finally, you could convert some of your queries to Pass-Through-Queries.
They aren't difficult, but if you haven't used them, you may have to do
some reading up on them first.
​
Arthur