Other Microsoft SQL Server database options

Simple versus full recovery mode

Databases can be setup in a few different modes of operation that have to do with how the data can be backed up and restored. As a general rule-of-thumb, if you are reading this section to find out what mode to set your database to you should be using simple recovery.

The SQL Server default is typically full recovery mode. The premise of full recovery mode is that a log of all changes you make to the database is maintained along side the database itself. This log must be periodically backed up to incorporate the changes into the database itself. The value of this option is that you can restore from a failure at a very granular level; perhaps minute by minute. The challenge is that if you do not have the technical capability to manage the settings, backups and use them in the restoration of your data they pose an unnecessary challenge. In addition to the database management, you need to be managing the backups of your documents in a similar incremental way as well as other files such as your full text search index. For many customers without a professional database administrator on staff, this burden is not worth the benefit or it would never be possible for them to realize the benefit. 

We see many issues from customers using full recovery without correctly managing its operation. A typical problem is when maintenance plans have not been setup correctly or are not run correctly and the database logs grow very large and either consume all the space on the drive where they are located or drag the performance of their system down substantially.

If you can accept a daily backup of your FileHold system, stick with simple recover mode.

Windows power management settings

When Windows Server is first installed it sets default power management to use the built in "Balanced" profile. This profile will favor slowing the speed of the CPU to save energy. As more CPU is required, it will increase the CPU speed. For an application like SQL Server, this has a side effect of slowing the performance of queries much of the time as a single user query will not be nearly sufficient enough to cause the CPU to be sped up. In fact, the load for SQL Server would need to reach 50 to 60% before the CPU speed is increased. For most customers this means their performance will be slower than it should all the time.

As a general rule of thumb, we recommend that customers consider enabling the high performance power profile as this will ensure the fasted CPU speed is available for each user regardless of the other load on the system. This is also a good setting for a virtual host machine.

The following links are not under FileHold's control, but they provide some additional insight to this topic.



Options that should be disabled

The settings below pertain to each database individually:

  • Auto Shrink — This is a very dangerous setting. It should never be set to true. This means that SQL can control the size of the data file. If SQL thinks the size of the file is too big, it will try to shrink the file or take some space away from it and users who are using the system may find it extremely slow during this process.
  • Auto Close — This setting should be set to false. If it’s set to true, when the last user closes the database it goes into shutdown mode and the next user has to wait until the database comes back online. The bigger the database the longer a user would have to wait.

Options that should be enabled

The settings below pertain to each database individually:

  • Auto Update Statistics - This is typically set ON by default, but it can have the side effect of causing the system to appear to pause at arbitrary times. It may be desirable to turn this off, but do so with caution and make sure regular statistics updates are happening in your maintenance plans.
  • Asynchronous Auto Update Statistics - This is typically set OFF by default. If you have auto update statistics set ON, this option should also be on to prevent queries from waiting until a potentially long automatic statistics update finishes.

It is best to avoid any automatic statistics updates by ensuring your maintenance plans are running with a frequency that is appropriate to the frequency of updates to your database. For example, run them more often when new documents are added, metadata is changed or workflows are executed frequently. You may wish to update statistics on demand if you make a mass data update like changing a dropdown menu value, changing custom naming rules or mass editing metadata associated with a large number of documents.