SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Some 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 on my local instance after enabling the feature:
Next is the size of the backups on my local instance before 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 Memory\Available 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.

SQL Mighty

I'm a passionate SQL Server professional with 10+ years of experience with databases. Developer, Administrator and Architect with 10+ years of expertise in data analysis, design, programming, performance tuning, upgrades, migrations, high availability solutions implementation, backup & recovery strategies and database capacity planning expertise.

Comments

Leave a comment on the original post [www.sqlmighty.com, opens in a new window]

Loading comments...