Best Practices for SQL Database setting

1. Database Owner: ‘sa’

As per my understanding, the Database Owner always should be ‘sa’. We can change ‘sa’ login name as per our organization standard login available in SQL security. While creating a database ‘sa’ should be assigned as Database Owner rather than the logging using to create the database. While creating a database we can use SSMS GUI to assign database owner or by TSQL also we can. If the database is already created then also we can change the owner by both GUI and TSQL.

TSQL Script to change Database  Owner

SSMS GUI to change Database  Owner

Database Owner
Database Owner

2. Database Growth

This depends on your database size and growth. But you can keep database growth to 512 in MB and set the max limit as “UNLIMITED”

TSQL Script to change these values

SSMS GUI to change these values

Database File Growth
Database File Growth

3. Recovery Model: Full

When should I use the full recovery model and when should I use the simple recovery model for databases? I always used the full recovery model because it is the default. As per my understanding, we should use the full recovery model when we require point-in-time recovery of your database. And we should use simple recovery model when we don’t need a point-in-time recovery of your database if the last full or differential backup is sufficient as a recovery point.

Set SQL Server Simple Recovery Model using Management Studio

  • Right click on database name and select Properties
  • Go to the Options page
  • Under Recovery model select “Simple”
  • Click “OK” to save

4. Compatibility level

Depends on your current database engine version:

For

  • SQL 2005: SQL Server 2005 (90)
  • SQL 2008: SQL Server 2008 (100)
  • SQL 2012: SQL Server 2012 (110)
  • SQL 2014: SQL Server 2014 (120)
  • SQL 2016: SQL Server 2016 (130)
  • SQL 2017: SQL Server 2017 (140)
  • SQL 2019: SQL Server 2019 (150)

5.  Auto Create Statistics

True

The Query Optimizer creates statistics on individual columns in the query predicate, as necessary, to improve cardinality estimates for the query plan. These single-column statistics are created on columns that do not already have a histogram in an existing statistics object. When the Query Optimizer creates statistics as a result of using the AUTO_CREATE_STATISTICS option, the statistics name starts with _WA. [Definition from Microsoft]

6. Auto Update Statistics

True

When the automatic update statistics option, AUTO_UPDATE_STATISTICS is ON, the Query Optimizer determines when statistics might be out-of-date and then updates them when they are used by a query. Statistics become out-of-date after insert, update, delete, or merge operations change the data distribution in the table or indexed view. [Definition from Microsoft]

7. Auto Update Statistics Asynchronously

True

TSQL Script to change the above values

Database Options
Database Options

8. Page Verify

CHECKSUM

SQL Server Database Engine calculates a checksum over the contents of the whole page, and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value that is stored in the page header. This helps provide a high level of data-file integrity. [Definition from Microsoft]

SQL Database Page Verify
SQL Database Page Verify

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *