SQL Server ships with a default configuration that will absolutely get you up and running and capable of running many workloads. However, there are some default settings that just don’t make sense for most modern servers. In this post, we’ll discuss four default settings that you’ll likely want to change.
Backup Compression
A simple checkbox is all it takes to see backups that take less space on disk, run faster, and save time when sending the files to the storage server. This is a quick win on most instances, however, there are some exceptions such as when saving the file on a SAN that may not compress the file already compressed with native SQL Server compression as it would the raw file. As with anything, you’ll want to consider your environment when making this change.
Cost Threshold for Parallelism
Cost threshold for parallelism defines the query cost where the optimizer should start evaluating parallel execution plans. The SQL Server default is 5. It has been 5 for a long time. On most servers I’ve seen, this is not a good number. While it can benefit queries to use multiple threads, some require so little effort that the additional overhead of managing multiple threads is not worthwhile. In addition, CPU threads are a finite resource. The best setting for your server will depend on its workload. A better default to start with would be 50 and adjust it as needed. To further tune this setting, finding the median cost of queries from your plan cache will help guide you to a number that may work better for your workload.
MAXDOP
Max degree of parallelism goes hand in hand with the cost threshold for parallelism. When a query is allowed to use multiple cores, MAXDOP will limit how many cores a single query can use. The SQL Server default is 0. This means that a single query can run across all cores. This can result in a single runaway query spiking the CPU usage on your server. Additionally, that query may have even run faster with less cores. A more sensible default would be half of your available cores. There are certain applications that require parallelism to be effectively stopped by setting MAXDOP to 1 so you’ll want to keep these applications in mind when adjusting this setting. Check the documentation for vendor applications to see if this may apply.
Remote Admin Connections
Things may go wrong, and resources lock up the server. The dedicated admin connection provides dedicated resources for use during this time, but you cannot connect to it remotely by default. This leaves you trying to get into an already exhausted server via RDP or another method to access the instance and attempt to troubleshoot. Turning on the remote admin connection will allow you to access the instance directly from another computer that isn’t bogged down.
Contact the House of Brick Team for more information and how we can help.
