Image
Microsoft SQL Server configuration for the FileHold databases
The information contained in this document should only be performed by a qualified DBA.
When the Microsoft SQL Server is installed, there are some considerations to take into account. These include but are not limited to: the number of processors your server has, the amount of temporary database files, the database data files, and database log file. Microsoft SQL Server creates default setting at install time. These settings likely need to be updated manually after installation to achieve best performance.
While all these recommendations can apply to all supported editions of SQL Server, there are some features of SQL Server that are not present or limited in Express. FileHold suggests that the Express edition be limited to customers with no more than 250000 documents and or 20 users, but it may only be possible to achieve best performance with the Standard or higher edition.
File location tuning
Microsoft SQL Server has two types of files per database: a data file and a log file. The log file is very important when it comes to recovery and crash recovery. There should be one log file writing transactions and actions.
Issues can arise when the log and data files reside on the same disk, mixing the activities or reading and writing from the data file and writing to the log file. If both files are located on separate disks it ensure there should be no conflict for disk access. By default, SQL Server sets these up on the same disk.
Although not typically a question of performance, it is generally not desirable to have the database file(s) location on the system drive of SQL server. When FileHold creates the databases in a new install, it uses the default location configured in SQL server. If this default has not been changed, it will be the system drive.
* There will only be one file when simple recovery mode is selected for the database.
File growth tuning
For each of the four databases, the initial size and growth rate of the two* related database files should be configured. It is recommended to set a growth rate of the data and log files to be a specific size instead of a percentage growth rate.
When SQL expands the data file, it is usually a quick process as it does not have to write zeros to the drive to grow the space; however, for a log file, SQL has to clean the data. It expands the disk space by writing zeros to the drive. This operation can take time and if it happens at the same time a user performing a non-trivial query they may receive a timeout.
A good starting point for growth is to use 256 MB for the database file and 64 MB for the log file.
Image
* If simple recovery mode is enabled for the database there is only one file.
Temp DB tuning
Temp DB file contention occurs when sorting or running processes need memory. If there is not enough memory these processes will write the information into the Temp DB file(s). If separate threads try accessing the same file and one accesses the file, the others are blocked and this can diminish performance.
Match the number of temp DB files to the number of logical cores in the system up to a maximum of 8. If the system has more than 8 cores you should watch for contention before adding more temp DB files. When modifying or setting up new Temp DB log files, it is important to set the initial size and growth to be the same for each file.
Image
Image
Miscellaneous Microsoft SQL Server database options that should not be set
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.