This article is designed to provide suggestions to assist database administrators investigate issues with the performance of an Insolvency database.
These are some of the areas to investigate when trying to improve a database server that may be experiencing poor performance:
- Re-index the database (see separate article on maintenance plans)
- Schedule a reboot of the database server (or restart the SQL services)
- Ensure the SQL Server has adequate physical memory (RAM)
- Set the database auto close setting to false
- Ensure the SQL Server Browser service is running
- Check the available space on hard drive
- Shrink the database log file and schedule this regularly (a script is available)
- Check the location of ‘tempdb’ databaseReview whether any other databases are sharing the tempdb and whether other process locks are affecting available resources. Consider moving Insolvency database to another SQL instance so it can have its own dedicated tempdb
- Check the domain name service is resolving the domain name correctly
- Ensure that there isn’t any crashed versions of the application running on a slow workstation or terminal server workstation
- Check if virus scanning software is running (on the server and/or workstation) Set this not to interfere with processing
- Turn the SQL Server autoshrink property to False
- Make sure the database Server Compatibility is set to the highest available 2008 or 2012 if installed
- Remove any deleted jobs from the database by setting up a regular cleanup of deleted jobs C:\Program Files\MYOB\Insolvency\SysTasks.exe /MDEL OR for V2020.03 and onwards C:\Program Files\Insol6\Insolvency\SysTasks.exe/MDEL
- Set up job archiving (this will allow you to archive old jobs - based on criteria of when they were closed - and recall them back to the database by changing the status.
Reboot the server
If you are unable to reboot the server, restart the SQL Server services.
Check online resources for explanations on physical memory.
Auto close setting:
If the AutoClose setting is set to TRUE then after each request the SQL Server “closes” the database. Each time a new request is made there is an overhead associated with “opening” the database. This can cause database requests to be very slow.
This issue may affect the Insol6 Insolvency database where other databases experience good performance because Insolvency is stateless (i.e. it creates a new connection for each each database request).
Before updating the AutoClose setting to FALSE, first check that the setting is set to TRUE so you can determine whether changing the setting has in fact improved the performance.
SQL Server Browser service
This can resolve connection problems.
This service provides a list of available servers and connections to the correct server instance. There may be an issue associating the browser service with an active SQL instance where the SQL server version has been upgraded or where specific account permissions are allocated to start the service (and this user account is not logged on).
Ensure the service is enabled and the service is running.
- Open the SQL Server Configuration Manager
- Click on SQL Server Services
- Right click on ‘SQL Server Browser Service’ and select ‘Properties’ The SQL Server Browser Service properties appear.
- On the ‘Log On’ tab ensure the built in account is set to ‘Network Service’
- On the ‘Service’ tab ensure the Start Mode is set to ‘Automatic’.
Check the size of log files and shrink the log file:
If maintenance on the database log file has been ignored then the database log file can grow large. This can fill up the hard drive and cause problems which database performance.
You can locate the database log file by looking at the database properties.
A script to shrink the database log file is available in here.
SQL uses tempdb to run the ‘current’ database query. Database files may have been relocated from their default locations (on C:\ drive) to a specific drive that has adequate space, however, the tempbd may still remain on the default drive.
If there is inadequate hard drive capacity allocated to the default drive then this can cause performance problems with SQL Server.
The domain name service resolves the server name into an IP address. Where the network service experiences slow performance resolving the IP address of the database server this can cause poor performance running the application.
To overcome this issue enter the IP address into the server name of the connection dialogue. Contact support for the steps to enter the details for the connection dialogue.
Crashed version of the software:
To check if there are any crashed versions:
- Close the application
- Open the System Task Manager
- Click on the ‘Processes’ tab
- Locate and select ‘Insol2.exe’ from the image list
- Click ‘End Process’ Virus scanning software The SQL Server autoshrink property may cause transactions to be queued at various times which in turn may cause transactions to be unresponsive. Turn this SQL Server property setting off as a precaution.
- A script is available to shrink the database and log files. This may be added as a scheduled task as an alternative.
Disable tcpip auto tuning on the workstation
In Windows Vista (and later versions) Ms introduced auto tuning that can affect performance. These commands run on the desktop disable auto tuning:
netsh int tcp set heuristics disabled
netsh int tcp set global autotuninglevel=disabled
If disabling the autotuning doesn’t make any difference you can return it to its original state with the following commands:
netsh int tcp set heuristics default
netsh int tcp set global autotuninglevel=normal