When creating a new SQL Server database I always use this checklist:
Name: Use as less non-alpha numeric characters in the database name as possible (do not use the ‘-‘, it will mess up your scripts). Use pascal casing for readability (use OrdersDb, do not use Orders-db or Orders_db)
Set Page verify to Checksum
Pre allocate data and log size to a reasonable size, do not rely on autogrowth.
Set a reasonable Autogrow size in MB (not a percentage) for data and log files
Enabling auto create/update statistics, is fine for most situations.
Set the right recovery model.
If your using ‘full’ or ‘bulk recovery’, schedule regular log backups (see this post )
Try to create at least two –equally sized!- device files for the data devices.
Not only reduces this locking issues but also when a database is regular under a heavy load,files can easily be moved to additional physical volumes (see this post )
Create one file for the Log
Use fn_virtualfilestats() to measure I/O load per device file (see this post )
Set the .MDF, .NDF and .LDF files in their own folders with the same name as the database. Don’t store all your db’s in one folder
Reorganize indexes/statistics on a regular basis (see this post )
Run DBCC CHECKDB on a regular basis (see this post )
Do not schedule any shrink operations
Configure instant file initialization (see this post )
Check out my post on auto index management and tools for performance, to setup more databases on the same instance and save costs
