Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,928

    Unanswered: SQL Server trace question

    I'm troubleshooting a problem we've been having with a medical research db running slow for remote users for several years. The db is configured with a SQL Server backend and an MSAccess front-end This db is one of many other dbs created and the db itself is only 1 gig in size. I've spent many years optimizing every aspect I could including the relational structure, the vb code, and SQL Server. There is extremely complex logic in the front-end so a lot of time was devoted to making sure the structure and vba code runs efficiently.

    Here are some details:
    1. On SQL Server, a full backup is made of the db every night and a transaction log backup is made every 30 minutes. TranLog file sizes are about 5 meg in size.
    2. The db has about 30 relational tables. Every data table has been carefully indexed, has a primary key and all tables are joined correctly.
    3. For the front-end in MSAccess, when the user opens the db, I use a cloning technique to make a copy of the *.accde file from the shared drive to the user's C: drive. Every user is essentially in their owned cloned *.accde file on their c: drive and should theoretically have a direct connection to the SQL Server drive itself. When a user opens a patient's record, a function runs which essentially opens the data table and pulls the data into an unbound MSAccess form. Once the user closes the patient's record, a function runs which opens the SQL Server table and pushes any data changes made into the table. Although there were some MSAccess forms that had to be designed in a bound fashion, most are unbound. Any bound MSAccess forms were carefully designed so that they only open 1 record instead of the entire dataset and careful attention was made to how the recordsets were opened and which ones were consecutively opened.
    4. 8 years ago I had SQL Server setup on a separate stand-alone computer but 4 years ago our sys admin guy moved SQL Server so that it is part of the RAID system that encompasses all of our other shared drives. This is when weird things started happening with slowdowns for remote users. I've advocated that we should go back to setting SQL Server up separated from our main RAID system.
    5. There are about a dozen non-remote users in the db at the same time and about another dozen in the db remotely.
    6. For non-remote users, everything works great and runs efficiently with no problems. For remote users (at hospitals and other locations), problems continuously happen with slowdowns. We used to use Direct Connect which continuously had connection drop issues (even when no one was in the db). We went back to using VPN which is much more faster and stable. Connections at the different hospitals has been thoroughly analyzed and our new sys admin guy installed devices that would booster any connections. Some remote users will also use hot-spot type connections.
    7. Our old sys admin was a fan of having everything on 1 RAID system and running non-SQL Server backup software throughout the day which wrote into the SQL Server transaction log. This concerned me.

    I've attached snapshots of all the SQL Server traces I ran. What concerns me are all the events that run under our old sys admin username (npwiley) every couple of seconds. These all have very large reads. I've also ran traces that capture when the non-SQL Server backup software runs which definitely causes a slowdown at that time. I can't figure out though how to get rid of the backup software the old sys admin installed and I've advocated that we should create a new SQL Server box on a stand-alone instead one that runs virtually or part of our main system.

    It's been a while since I've exclusively played the DBA role and I've spent a lot time over the years making sure the structure and coding was optimal. If anyone can help me on what might be causing these slowdowns to continuously happen for the remote users, that would be great. We've had multiple discussions on what might be causing slowdowns and remote users tend to always think it's a database issue since they are always in the database throughout the day.

    I've also attached an example of the relational schematic which has taken 5 years to perfect. Careful attention was made to every relationship and every index created (SQL Server tables are linked tables into the MSAccess file.) Again, the database runs awesome for non-remote users.
    Attached Thumbnails Attached Thumbnails SQLServerTrace.JPG   RelationalSchematicExample.JPG   Trace2UserQueries.JPG   SQLTrace3UserQueries.JPG   SQLServerTrace4.JPG  

    Last edited by pkstormy; 03-07-17 at 15:46. Reason: add attachment
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,862
    Provided Answers: 17
    Good news first. The large numbers of reads in the trace all look to be associated with an Audit Logout event, which accumulates all of the I/O across the life of the session. There may not be a problem there, unless these reads are accumulated in a very short amount of time (measured from log in event to log out event).

    I would start off by checking what your top waits are. From the description of remote users waiting more than local users, I would suspect Asynchronous_Net_IO would be fairly high on the list. If the problem is with the disk array, you will likely see PAGEIOLATCH bubble towards the top. There are a lot of benign wait types in SQL Server, so use the query in Paul Randal's blog here:http://www.sqlskills.com/blogs/paul/...here-it-hurts/. Once you have a clear sense of the waits (remember to clear the wait stats, so you get a clear idea of what waits are happening during the work day), post some of the findings here, and we can probably point you further along toward the problem.

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,928
    Thank you! I will run the tests you specified the first chance I get. What bugs me the most is that there is such a big difference on performance for the remote users versus the non-remote users.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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