SQL Server Database - Best Practices

Database Best Practices:

1.     The primary file group must be totally separate, and should be left to have only system objects, and no user defined object should be created on the primary file group. Also, the primary file group should not be set as the default file group. Separating the system objects from other user objects will increase performance and enhance the ability to access tables in cases of serious data failures. We can fallow this best practice if is there any huge database.

2.     If there are N physical disk drives available in the system, then try to create N files per file group and put each one in a separate disk. This will allow distributing disk I/O load over multiple disks, and will increase performance.

3.     Put the transaction log file on a different physical disk that is not used by the data files. The logging operation (Transaction log writing operation) is more write-intensive, and hence it is important to have the log on the disk that has good I/O performance.

4.     Do not let SQL Server fire the "Auto grow" feature too often because it is a costly operation. Set an "Auto grow" increment value so that the database size is increased less frequently (say, once per week). Similarly, do not use the "Auto shrink" feature for the same reason. Disable it, and either shrink the database size manually, or use a scheduled operation that runs in a timed interval (say, once a month)

5.     Compatibility level: Specify the latest version of SQL Server that the database supports. Possible values are SQL Server 2014 (120), SQL Server 2012 (110), and SQL Server 2008 /2008R2(100). Make sure to be set proper compatibility level as per the SQL version.

6.     Recovery Mode: Set the Recovery mode for user defined databases depends on the requirement.

Ex: For Production DB, the recovery mode should be in FULL

      For Dev/Test/UAT the recovery mode can be set either Full or Simple

Note: If we set Full Recovery mode then must be configured the T.LOG backups, if we set    Simple Recover mode then need not to configure T.LOG backups.

7.     Auto Shrink:  Should be disable. Make sure the Auto shrink option must be FALSE.

8.     Auto update statistics: Should be in Enable. Make sure the Auto update statistics option must be TRUE.

a.       Auto growth:

        i.      Small database (<10 GB = 128MB)

       ii.      Medium database (10 - 100 GB = 256 MB)

       iii.     Large Database (100 - 500 GB = 512 MB)

       iv.     Very Large Database (>500 GB = 1024 MB)

       v.     Set log files to grow appropriately – start with 128 MB and increase as necessary. (Keep an eye on the log file. At some point, it should level off.)

b.      Set databases to standards for:

     i.     Check Auto close - Off

     ii.    Check Auto shrink - Off

         iii.    Check Auto Create Statistics - On

         iv.    Check Auto Update Statistics - On

         v.     Check for appropriate level of recovery model (Simple, Full)

         vi.    Check for appropriate level of recovery (Checksum - 2005/2008, Torn Page - 2000)

         vii.   Check for appropriate level of compatibility. (80 - 2000,90 - 2005,100 - 2008/2008R2, 110-                        2012, 120-2014)