Database Configuration Option

AUTO CLOSE OPTION SHOULD BE OFF

Auto Close deallocates all objects from memory for a specified database.

Query:
SELECT name, is_auto_close_on FROM Sys.Databases

Alter database DB set AUTO_CLOSE OFF;

AUTO SHRINK OPTION SHOULD BE OFF

Auto Shrink automatically shrinks DB every 30 minutes , but can cause severe fragmentation.

Query:
SELECT name, is_auto_shrink_on FROM Sys.Databases

Alter database DB set AUTO_SHRINK OFF;

RECOVERY MODELS

Query:
SELECT name, recovery_model_desc FROM Sys.Databases

Alter database DB set recovery SIMPLE;

There are 3 types of Recovery Model:
1. SIMPLE - No Log Backups, Full Only.
2. BULK_LOGGED - Logs and Differentials, But Not PiT*.
3. FULL - Adds PiT*.
* PiT - Point in Time.


STATISTICS

Statistics used by SQL Server to decide which index to use and needs to be maintained to be effective. (Statistics rebuild after 500 records and then table growth by 20%).

There are 2 types of Statistics:

1. Synchronous - forces query to wait when stats are out of date.
2. Asynchronous - allow query to continue with old stats and build new ones in the background.

So, Asynchronous  Statistics should be ON.

Query:
SELECT name, is_auto_update_stats_on,is_auto_update_stats_async_on FROM Sys.Databases

Alter database DB set AUTO_UPDATE_STATISTICS_ASYNC ON;