Database Settings

Go to: File menu > Utilities module > Utilities menu > Database Utilities.

The Database Settings window is where you can customise the database to best suit your hardware and operating environment. The settings here should allow you to get the best performance from your database.

Several options for database settings are offered on this window. A Use Recommended button, offered in each section, will automatically select the settings recommended by VisionVPM.

There are 4 sections on this window:

The first section is a display of basic database properties such as its name, size and the specific version of SQL Server that you are using.

Database Properties

The second section groups together database settings that tell your database how to behave.

• Auto update statistics: Checked, this will enable the database to remove out-of-date statistics that it may have collected. This will optimise its queries. It is recommended that this should be selected.
• Auto create statistics: Checked, this will enable the database to automatically create missing optimisation statistics. Missing statistics needed by a query for optimisation are automatically built during optimisation. It is recommended that this should be selected.
• Full Mode Recovery (enables transaction logging): Unchecked, this will disable the creation of transaction logging. The transaction log requires additional space and maintenance. The transaction log file is great for large sites that demand a high level database recovery model as it allows for data recovery to a specific date and time.

  To achieve this, the database must also be backed up in a very specific way and restoring to a point in time must also follow strict guidelines.

  Typically this adds a complexity that is best left to large sites that employ database administrators. It is recommended that this should not be selected.

• Auto Shrink: Checked, this will allow the database to recover unused space. While this sounds like a good idea, the database works best when it has free space within its data file. It is recommended that this should not be selected.
• Auto close: Checked, this tells the SQL server to shut down and free resources for the database after the last user exits. When Auto close has been enabled you may experience slight delays as the database re-opens and allocates resources for itself. If you rarely use the database then you may decide to enable this option, but having the database on and ready will give you a much snappier performance. It is recommended that this should not be selected.
• Use Recommended: This will enable and disable the settings to VisionVPM recommendations.

Database Size

The third section is for increasing the amount of physical hard drive space the database will use. The database will perform better when it has plenty of free space to write to. If the database is low on space it will attempt to grow. This may slow down performance or result in a fragmented database file which will also slow performance. We recommend that the database is set to a reasonably large size. The maximum size of a database varies between SQL Server versions.

Typically for the following versions these size restrictions apply:

MSDE 2000: max database size is 2GB (2048MB)

SQL Express 2005: max database size is 4GB (4096MB)

If you have enough free hard drive space then we recommend you set the database to its maximum size but not exceeding 4GB unless you are a very large site. If you are using a version with the size restrictions then you may use the recommended button to make the adjustment.

Server Memory

The fourth section is for setting the amount of physical memory that SQL server will use.

Min Memory allocated to SQL (MB) is the minimum amount of RAM that SQL Server will use. When SQL server starts up, it will secure this amount of RAM.

Max Memory allocated to SQL (MB) is the upper limit of RAM that SQL Server will use.

If SQL Server is low in RAM it will attempt to acquire more RAM but will not exceed the Max limit.

There are generally 2 scenarios for adjusting the min and max limits:

Dedicated SQL Server computer

• A computer that is only used to run SQL Server.
• Min memory is set to about 1/3 of the total physical RAM.
• Max memory is set the total amount of physical RAM available.
• This allows SQL Server to automatically use as much RAM as it needs.

SQL Server on a computer with other applications

• Min and Max memory are set to the same amount.
• This is done so that SQL Server is guaranteed the RAM before another application takes it for its own needs. Guidelines for the min max values are calculated as follows:
• < 3GB of physical RAM. Min max: 1/3 of the physical RAM
• 3GB-4GB of physical RAM. Min max: 1/4 of the physical RAM
• > 4GB of physical RAM. Min max: 1GB.
• You can use either of the Use Recommended buttons to adjust the min max memory values.