SQL Server Database Checklist - Database Reference Guide - Foundation 23.1 - Foundation 23.1 - Ready - OnBase - external

Database Reference Guide

Platform
OnBase
Product
Database Reference Guide
Release
Foundation 23.1
License
  • Verify Disk Partition Alignment (for more information, see http://msdn.microsoft.com/en-us/library/dd758814.aspx) and utilize a cluster size of 64 KB.

  • Place the SQL Server Transaction Log on a separate disk array than the SQL Data file(s) to mitigate contention between the files. The Transaction Log is written to sequentially and read from much less often than Data files, which have a high number of random reads and writes. In a SAN environment, the Log should exist on storage that supports writes that occur in less than 10 ms. Data reads and writes should occur as quickly as possible (for example, less than 20 ms). In accordance with Microsoft recommendations, RAID10 (Mirroring and Striping) is recommended for database files (for more information, see http://technet.microsoft.com/en-us/library/cc966534.aspx).

  • Place tempdb on a separate disk array than the SQL Database file(s) and transaction log. Mirroring (RAID 1) is acceptable, as tempdb does not persist and redundancy is not required, but RAID10 is optimal.

  • Pre-size the tempdb file to at least 2 GB (or larger for enterprise implementations) so that it does not auto-grow.

  • Create multiple tempdb data files to reduce contention. Please reference Microsoft's Concurrency enhancements for the tempdb database article for more information. Note that trace flag 1118 is not required. In addition, the number of tempdb data files should be ¼ to ½ the number of processor cores, with a maximum of eight (8) files.

  • Pre-size the OnBase database files so they do not auto-grow. Modify the growth increments to be 100MB for larger files (DBSpaces 1, 2, 6, 9, 2i, 6i, 9i, 10) and 50 MB for all other file groups.

  • Enable instant file initialization (for more information, see http://msdn.microsoft.com/en-us/library/ms175935(v=sql.105).aspx).

  • Enable the Optimize for Ad hoc Workload option to help relieve memory pressure.

  • Enable the Checksum option for Page Verification.

  • Disable SQL Server's use of parallel execution for query optimization in Enterprise Manager/Management Studio. Right-click on the SQL Server instance and select Properties; then select the Advanced page. Change the value for the Max Degree of Parallelism setting to 1 to disable parallelism.

  • Disable the Auto-Shrink options for the OnBase database in Enterprise Manager/Management Studio. Right-click on the database and select Properties; then select the Options page. Set Auto-Shrink to False.

  • Enable the Auto Create Statistics option for the OnBase database in Enterprise Manager/Management Studio. Right-click on the database and select Properties; then select the Options page. Set Auto Create Statistics to True.

  • Set the Auto Update Statistics option for the OnBase database in Enterprise Manager/Management Studio as appropriate. Right-click on the database and select Properties; then select the Options page. Set the Auto Update Statistics option appropriately.

  • Allow SQL Server to dynamically manage memory, and modify the maximum setting. To further reduce overhead, set the minimum and maximum memory to the same value. Right-click on the SQL Server instance and select Properties; then select the Memory page. Set the Maximum server memory (in MB) based on the following calculation: The Maximum server memory (in MB) value should be anywhere from 2GB to 20GB less than the memory on the server, depending on the total amount of memory. A good rule of thumb for calculations is to leave 1–2GB for the OS, an additional 1GB for each 4GB of RAM installed from 4GB to 16GB, and an additional 1 GB for each 8 GB of RAM installed above 16GB. Example values are shown in the table below.

    Server Memory (GB)

    Memory to Reserve (GB)

    Memory to Allocate to SQL Server (GB)

    4

    2

    2

    8

    3

    5

    12

    4

    8

    16

    5

    11

    24

    6

    18

    32

    7

    25

    48

    9

    39

    64

    11

    53

    Note:

    If you plan to run instances of the OnBase Client on the server for processing or printing purposes, which is not recommended, you must further reduce the Maximum server memory (MB) value to allow adequate memory to be available for those applications.

  • Configure maintenance plans to update statistics and rebuild indexes on a regular basis. Schedule the Rebuild Index Task to execute on a weekly basis to ensure statistics are updated using a 100% sample. This will also reduce logical fragmentation. Schedule the Update Statistics Task(with the Update Column Statistics option selected) to execute on a weekly basis to ensure column statistics are updated with a 100% sample.

  • Configure maintenance plans to perform database and log file backups (if applicable) on a regular basis.

  • Configure maintenance plans to verify database integrity on a regular basis.