Some simple adjustments to the configuration of SQL Server can greatly improve the performance of the database environment. Keep in mind that some of these settings may impact the backup approach you employ for MS SQL.
- Set the compatibility level to SQL Server 2008 (100) to take advantage of the latest optimizations.
- Ensure the auto-close property is set to false. This ensures that a page will only make one connection.
- Ensure the auto shrink property is set to false to avoid costly dynamic resizing.
- Ensure the recovery model is set to simple to avoid the high overhead of transaction logs.