SQL Server settings

SQL requirements are listed at the beginning of each section in this guide where appropriate. This section includes some settings that can be used to help improve system performance. For more information, refer to the SQL documentation.
Note: This topic applies to only on-premises environments.
  • Auto shrink: On the application database machine, always have Auto shrink disabled for all databases. If it is disabled, your system will not show significant performance loss related to shrinking the database. If it is enabled, SQL Server checks every 30 minutes to see if it needs to shrink the database; this can cause a huge performance hit. When you need to shrink databases, you can use the DBCC SHRINKDATABASE or DBCC SHRINKFILE commands, or you can use the SQL Server Agent to schedule regular file-shrinking during non-peak hours, instead of enabling Auto shrink.
  • Auto update statistics: We recommend that you enable Auto update statistics for all databases. This feature is enabled by default. With this feature enabled, SQL Server updates the statistics of an index based on this criteria:
    • If the number of rows in a table is greater than 6, but 500 or less, statistics are updated when there have been 500 modifications made.
    • If the number of rows in the table is greater than 500, updates are made when 500 plus 20% of the number of rows in the table have been modified.

    When a SQL Server database is under a very heavy load, this feature can update the statistics during busy times, causing a performance issue. If you find that enabling the feature causes more problems than it solves, you can turn it off, and then manually update the statistics when the database has a lighter load.

    We recommend that you both enable Auto update statistics and update statistics manually.

    See Updating statistics.

  • Tempdb: Set the original size of the tempdb database files to a reasonable size to prevent the files from automatically expanding as more space is needed. If the tempdb database expands too frequently, performance can be affected.
    • To avoid the tempdb database files from growing by too small a value, set the file-growth increment percentage to a reasonable size (10% is usually a good choice). If the file growth is too small compared to the amount of data being written to the tempdb database, then tempdb might need to constantly expand, thereby affecting performance.
    • To ensure good performance, place the tempdb database on a fast I/O subsystem.
    • For better performance, stripe the tempdb database across multiple disks.
    • Use filegroups to place the tempdb database on disks different from those used by user databases.
  • Minimum server memory and Maximum server memory: Set these values based on the size and activity of your instance of SQL Server.
  • MAXDOP: Set the maximum degree of parallelism option to 8 or less by using sp_configure.
Related topics