Vault's SQL Server database options (Performance Tweak)

[jclausius]

Vault 6 and later installers for the Vault Server will temporarily turn off some SQL Server database options in order to perform any database upgrades. For some installations, there have been reports where the database options are not restored once the installation has completed.

If you are experiencing poor performance with Vault, you may want to check the database options for Auto Update Statistics Asynchrously, Auto Update Statistics, and Auto Create Statistics is turned on. You can also run the following queries within SQL Server Management Studio to turn on these database options.

Vault Server Professional:

Code: Select all

ALTER DATABASE sgmaster SET AUTO_UPDATE_STATISTICS_ASYNC ON
GO
ALTER DATABASE sgmaster SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE sgmaster SET AUTO_CREATE_STATISTICS ON
GO

ALTER DATABASE sgvault SET AUTO_UPDATE_STATISTICS_ASYNC ON
GO
ALTER DATABASE sgvault SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE sgvault SET AUTO_CREATE_STATISTICS ON
GO

ALTER DATABASE sgvaultindex SET AUTO_UPDATE_STATISTICS_ASYNC ON
GO
ALTER DATABASE sgvaultindex SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE sgvaultindex SET AUTO_CREATE_STATISTICS ON
GO

ALTER DATABASE sgnotify SET AUTO_UPDATE_STATISTICS_ASYNC ON
GO
ALTER DATABASE sgnotify SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE sgnotify SET AUTO_CREATE_STATISTICS ON
GO

ALTER DATABASE sgdragnet SET AUTO_UPDATE_STATISTICS_ASYNC ON
GO
ALTER DATABASE sgdragnet SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE sgdragnet SET AUTO_CREATE_STATISTICS ON
GO