Need to optimize/repair database

Topic

New Reply

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?

Reply | Quote

Replies

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?

Reply | Quote

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.

Reply | Quote

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?”)

Reply | Quote

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.

Reply | Quote

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/library/ee343986(SQL.100).aspx

Hope that helps

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Thanks for this helpful information.

PS: DBCC CHECKDB reports no errors in the database.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote