Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Need to optimize/repair database

    We have a Windows front-end application that uses an SQL database as a back end. The application has an update function that saves the user's changes to the database and refreshes the display from the database. This function has always been fairly slow, sometimes taking a minute or two, but suddenly it has started taking an abnormally long time, sometimes 20 or 30 minutes or more.

    The initial save (write) part of the function seems take only a short time, and it is the subsequent refresh (read) part that seems to take much longer. The update speed has always been slightly affected by the size of the database, which has been growing slowly and steadily, but the sudden extreme slowness of the update function seems to be something else. We have tried restarting the server, running a chkdsk /f, checking for stuck processes, etc., but everything seems normal.

    Is there something in SQL Server Management Studio 2005 similar to the Repair and Compact function in MS Access that might help?
    Last edited by Murgatroyd; 2011-07-08 at 23:42.

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Client-server development requires a different mindset from a tradicional Access app, where you have the database local to your front-end. One very important precaution is the amount of data transferred between the client and the database server. Can it be that with the growing number of database records, too much data is being moved between client and database? Have you tried running the client queries in question in SQL Server Management Studio to check if the issue is with the database server's response times?

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    You don't mention if the front-end application is Access? If so, is it a .mdb file or a .adp file? The two formats use different methods to connect to the SQL Server back-end. Some sense of the size in G-bytes would also help. Are you running a maintenance procedure on the database to check for corruption and to compact it? If not, check the size of the SQL Server log file - that is one thing that can make an app grind to a halt. By default, the log file grows by 10% when it needs additional space. If you have a 10 G-byte log file, that means it would request 1 GB of file space when it ran short on room. If the log is large, you probably want to truncate it after you have a known good backup of the database. To be of much more help, we'll need further details on how the front-end and back-end interact.
    Wendell

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your replies. The front-end application is a Windows program. The developer and source code are not available. The SQL database file is about 2 GB, and the log file is about 100 MB. The database indexes have been rebuilt, but this has not helped. You mention a maintenance procedure to check for corruption and to compact the database. We are not currently running any maintenance procedures, hence my question about this. ("Is there something in SQL Server Management Studio 2005 similar to the Repair and Compact function in MS Access that might help?")

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    There is a SQL Server command called "DBCC checkdb()" that will run a series of exhaustive tests agains your database and identify any errors it finds. However, I would create a periodic maintenance task that is executed by SQL Server Agent. That will run the checkdb and typically also include a compact of both the database (.mdf) file and the log file. SQL Server Management Studio does have the Maintenance Wizard tool to help you create such a task, and can also include a backup of the database to a specified location, as well as several other tasks.

    If that doesn't resolve your issue, and I am suspicious that it won't, then I think your best option would be to find an experienced SQL Server DBA who can look at the system where the database is hosted and identify other potential issues that may be the root cause of your problem. They would be asking questins such as how much free disk space do you have on the drive(s) where the database is stored, what kind of redundancy technology are you using, are all of the tables properly indexed, etc. In addition they may be able to do performance profiling to identify where the bottleneck is. Clearly the kind of performance you are seeing is not acceptable in the long term, and probably not even in the near term. Hope this helps.
    Wendell

  6. The Following User Says Thank You to WendellB For This Useful Post:

    Murgatroyd (2011-07-10)

  7. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for this helpful information.

    PS: DBCC CHECKDB reports no errors in the database.
    Last edited by Murgatroyd; 2011-07-10 at 23:08.

  8. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I rather expected it would not - it sounds more like either a hardware related issue (disk space, disk errors, etc.), or some issue related to growth over time of the database in some fashion that the original design did not account for. Let us know how you get on - it is likely to be an interesting problem.
    Wendell

  9. #8
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Given the described worsening response times with the increase of the number of the database records, I would probably bet on the client app retrieving too much data for what it actually needs. If you know your way around SQL Server Profiler, I would probably have a look first at what is being requested by the app from the database, and see if that can be the cause for the performance issues. It may also be caused by poor indexing and if it is, it can be improved also by using profiling and using the outpout as an input for the indexing advisor. If it is an app badly built for a client server architecture, you may find there is no way around it other than changing the app.

  10. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Unfortunately, everything you've described on how the application works is somewhat vague. When you say "the application has an Update function", it would seem to indicate that changes are stored locally until they are synched with the server. I imagine at that time, the server is passing info back to the local app on any changes that have been made by other users; and that's probably killing you. Quite frankly, I think it is an application problem (in how it determines how much info to pass to and request from the server), and not a SQL server problem.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  11. #10
    Lounger
    Join Date
    Jun 2010
    Location
    Manchester, NH
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts
    In addition to what has been discussed previously, you might want to review this paper the addresses Query Exectuion Plans and how they can impact performance: http://technet.microsoft.com/en-us/l...(SQL.100).aspx

    Hope that helps
    Bob Oxford
    Software Wizards, Inc.

  12. #11
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for further replies; however, because the front-end application is a Windows program, and the developer and source code are not available, we are unable to examine or modify the application. A new application is being developed and should be available soon, but meanwhile we can only investigate the platform and environment of the existing application.

Posting Permissions

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