I just read an article titled The 3 Configuration Options that you always have to change in SQL Server! by Klaus Aschenbrenner (@Aschenbrenner).  I went and looked over my SOP script that I keep for all of my SQL servers.  The full version can be found here.

My script does several things:

1. Sets 3 of the 4 most important config settings I need in my environment (more on these later).
2. Sets a standard size for the Model data and log files (1024MB with 1024MB growth).
3. Sets all database owners to sa.  This is so we’re not reliant on Windows Authentication if someone leaves the company.
4. Sets all databases to PAGE_VERIFY CHECKSUM.  This is vital to make sure data is valid within the page when reading and writing from disk (See Kendra Little’s blog here).
5. Computes the value of max server memory (#4 of 4 most important config settings) based on Jonathan Kehayias’ blog post.

So I only do one of three that Klaus recommends.  Well, let me rephrase that.  I only script out one of three.  The other two, Max DOP and Cost Threshold of Parallelism, I tend to leave alone unless I see actual performance issues.

The configuration options I do set, lines 25-33 of my script are:  backup compression default, optimize for ad hoc workloads, and remote admin connections.

Backup compression is needed because we do a lot of network shipping of backup files, both at creation and for secondary backup purposes (testing restores, dev environments, and offsite tape backups.)   I know this has been a lifesaver when needing to restore some 600GB monster database, but the backup file is less than 30GB.  In SQL Server 2014, this feature is enabled from Standard Edition on up.  All versions can restore a compressed backup.  So use it often.

Optimize for adhoc workloads won’t ever hurt you, and most likely will only help, as it’s more about storage of the compiled query plan that anything else.  I found this made a big difference in several of my OLTP workloads.

Remote Admin Connections.  I can’t tell you the number of times this has saved my bacon when I can’t log in to the Windows server but need to perform some random admin function to get data back.  The DAC is a lifesaver.

What are your mandatory configuration options?