Skip to main content
Our Tech Ideas

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

--Change your database name accordingly
USE [ourtechideas]
GO
--Change your sa login name as per your organization standard login availble in SQL security
ALTER AUTHORIZATION ON DATABASE::[ourtechideas] TO [sa]
GO

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

USE [master]
GO
--Change your database and logical file name accordingly
ALTER DATABASE [ourtechideas] MODIFY FILE ( NAME = N'ourtechideas', MAXSIZE = UNLIMITED, FILEGROWTH = 512MB )
GO
--Change your database and logical file name accordingly
ALTER DATABASE [ourtechideas] MODIFY FILE ( NAME = N'ourtechideas_log', MAXSIZE = UNLIMITED, FILEGROWTH = 512MB )
GO

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.

ALTER DATABASE dbName SET RECOVERY recoveryOption
GO

-- Example: change AdventureWorks database to "Simple" recovery model

ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE
GO

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

USE [master]
GO
--Change your database name accordingly

--This will set database Recovery Model to FULL
ALTER DATABASE [ourtechideas] SET RECOVERY FULL WITH NO_WAIT
GO

--Set the COMPATIBILITY_LEVEL as per your SQL Database engine version
ALTER DATABASE [ourtechideas] SET COMPATIBILITY_LEVEL = 140
GO
--This will set the Auto Create Statistics to TRUE
ALTER DATABASE [ourtechideas] SET AUTO_CREATE_STATISTICS ON
GO

--This will set the Auto Update Statistics to TRUE
ALTER DATABASE [ourtechideas] SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT
GO

--This will set the Auto Update Statistics Asynchronously to TRUE
ALTER DATABASE [ourtechideas] SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT
GO

--This will set the Page Verify Option to CHECKSUM
ALTER DATABASE [ourtechideas] SET PAGE_VERIFY CHECKSUM  WITH NO_WAIT
GO
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]

--This will set the Page Verify Option to CHECKSUM
--Change the database name accordingly
ALTER DATABASE [ourtechideas] SET PAGE_VERIFY CHECKSUM WITH NO_WAIT GO
SQL Database Page Verify
SQL Database Page Verify