Amending the Database Sweep Interval Setting
Overview:
On some large databases, performance issues maybe experienced due to the database Sweep Interval setting being set at a higher level than need be. By default the Sweep Interval setting is set to 20000 records. However this can create performance issues if transactional volumes are high. Re-setting this sweep value to ‘0’ (Zero) will prevent the database from storing ‘garbage’ or roll back transactions. This stops the sweep process entirely which can slow down performance as it can take much of the servers resources to perform a Sweep to remove redundant ‘garbage’ records.
NB: There is an offset to this, which means the database will require more frequent Validation / Backups and Restores to remove this ‘garbage’ from the database before it starts to bloat the size of the file. Of course, these housekeeping procedures should be run frequently as they also re-build Indexes which are in essence ‘pointers’ within the database referring to actual records.
For more information on Sweep please refer to this article http://www.firebirdfaq.org/faq312/
Determining the Sweep Interval and Amending it:
Firstly, check the current Database Sweep setting by downloading the attached Command script
Run this command script to determine the current Sweep Interval setting. If the setting is greater that 0, then proceed with the actions below to amend that setting.
cd c:\program files\firebird\firebird_2_5\bin directory
Define the following two variables, it makes life easier, in that you do not have to type in the user name and password every time you issue a command.
SET ISC_USER=SYSDBA
SET ISC_PASSWORD=masterkey
substitute your database path and name as necessary (NB: The database path must
gfix -h 0 c:\Database\Ostendo.fdb
Now check to see the Sweep Interval was change successfully by logging back onto Ostendo and re-running the Command script.
Finally run the dbutiles.exe to perform the following: