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

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:

EXEC sys.sp_configure 'show advanced options',1;
GO
RECONFIGURE

EXEC sys.sp_configure N'backup compression default',N'1'
GO
RECONFIGURE WITH OVERRIDE

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:

EXEC sys.sp_configure 'show advanced options',1;
GO
RECONFIGURE

EXEC sys.sp_configure N'cost threshold for parallelism',N'50'
GO
RECONFIGURE WITH OVERRIDE
GO

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:

EXEC sys.sp_configure 'show advanced options',1;
GO
RECONFIGURE

EXEC sys.sp_configure N'max server memory (MB)',N'55296'
GO
RECONFIGURE WITH OVERRIDE
GO

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.

 

Si vis pacem, para sql

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 [igormicev.com, opens in a new window]

Loading comments...