Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL server loading (Access XP/SQL Svr 2000 SP-3)

    My client has a single machine running Windows 2000 Server, Terminal Server, and SQL Server. It's a 1.13 Ghz P3 with 2 gigabytes of RAM and plenty of disk space. We have an Access XP front end using a SQL Server 2000 back end. The clients access the front end using Terminal Server client via a 56k frame relay environment, using Windows 98 as the desktop OS. When the client executes a report in Access, processor utilization goes up to 100% until the queries finish executing. When we get to about 12 or so users, the server slows to the point these reports time out before completion. The SQL Server is using the installation defaults.

    Question one: is this loading to be expected with this hardware, network, and software setup?
    Question two: Any suggestions on how to tweak things for better performance?
    Question three: Any traces I should run to collect additional info?
    Question four: Any questions I should have asked, but am just too dumb to ask? <smile>

    I'm new to this type of problem...I usually just develop.

    Thanks,

    Keith

  2. #2
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL server loading (Access XP/SQL Svr 2000 SP-

    I have a little experience in this area.

    How big are the queries being run? Some queries can really bog down a machine depending on the structure of the query. In some cases you can use stored procedures or views to make getting the data a ton faster. You mentioned that you are using terminal server. Does that mean the the clients are running only a desktop image of the app? This would mean that the app is running entirely on the server. On a previous project, I had 20+ users banging the same database without much of a performance problem. The configuration was pretty similar to what you are using. We had a PIII 733 (dual processor), 1 GB RAM CPU running Citrix Metaframe services.

    Thanks,
    Mark

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: SQL server loading (Access XP/SQL Svr 2000 SP-3)

    It sounds as if the server is pretty much overloaded. For one thing, a session of Terminal Server, running an app such as Access could very well take up 256MB of RAM. So 10 sessions could be taking up much of the server RAM. In addition, SQL Server likes at least 256MB at a minimum - we ususallly configure it to take all the memory of the server and run it on it's own server.

    The second issue has to do with the size of your tables, how you are connecting to SQL Server, and whether or not all tables are in SQL Server. The size of the table can be important if it is not indexed properly, and especially if it is being joined to an Access table in the query. The connection type you are using is probably ODBC, which is how linked tables are typically used, and with good optimization can usually give good results. If the tables are large (say > 500K records), then you may want to resort to stored procedures that are called using a pass-through query or other techniques that can be used to improve performance.

    Finally, I doubt traces would really help you in this situation - and should be aware that Access usually pegs the utilization meter as long as it is running queries - it's just the nature of the beast. That doesn't mean it won't share but you have to force it to make it work.
    Wendell

  4. #4
    New Lounger
    Join Date
    Mar 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL server loading (Access XP/SQL Svr 2000 SP-3)

    Wendell,

    I've never used a pass thru query to access the results of a stored procedure. How do I do that?

    The tables are small, so far (4000 records). All tables are SQL Server. I use SQL user functions to provide search criteria for the views and I've reworked some of them with significant results.

    Keith

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: SQL server loading (Access XP/SQL Svr 2000 SP-3)

    First of all, your stored procedure needs to return a record set - not all do, and as a result you can do things that actually alter the SQL database from Access. If it does, then you can use the process in Access 2002 Help to do that process - it's 9 steps. Just do a search on "pass through query" and it should be the second item on the topic list. The first item in that has the HOW TO of creating a pass-through query. If you want to manipulate the query in code, you may have to use DAO to save the query to the QueryDef collection before you execute it - which makes a very good argument for using a split database with each user having their own front-end.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •