Mandatory Sql Server properties to configure on a new instance

After installing a SQL Server instance, it’s good that you check the instance settings and change some of them. Next are the top 3 settings that have become a best practice for configuring a fresh instance, unless there are some very special reasons to not touch them. However, relevant documentation is given to check-up for such cases.

Backup compression. This feature is available for set up from version 2008. By enabling this feature the backup process becomes faster and the disk space and the device I/O transfer for the backups is significantly smaller. For more information, check the official documentation here

You can set it with the following commands:

Next is the size of the backups (18.8 GB) on my local instance before enabling the feature:

Next is the size (2.48 GB) of the backups on my local instance after enabling the feature:

Cost threshold for parallelism. This setting has a default value of 5 which is very low for a testing or production instance. It’s used by the optimizer to determine whether to evaluate parallel plans. It’s not good to have over-parallelism which will increase the CXPACKET wait type significantly. The setting is possible from version 2008 and onward. Official documentation is here.

“Best” value to set it is 50. It’s not the right or wrong value, but is much better than the default value of 5. You can set it with these commands:

Max server memory. As a general base configuration, for a dedicated SQL Server machine, reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM. This means that, for a server with 64 GB RAM, the starting point for max server memory should be in the 54 GB range, and then tuned higher based on monitoring the MemoryAvailable MBytes performance counter.

You can find this recommendation in book “Troubleshooting SQL Server A Guide for the Accidental DBA” by Jonathan Kehayias and Tedd Krueger. You can set the max server memory with the following commands:

I do regularly change these settings after installing a fresh instance of SQL Server. They are especially very good if you have multiple instances on a server machine.

I additionally check the instance collation, but it’s a matter that could not always be recommended to change. However, it’s good to make a short look at.


Leave a Reply

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


This site uses Akismet to reduce spam. Learn how your comment data is processed.