SQL Server default settings that you might want to change
One very common complaint I receive from SQL Server administrators is:
“Why is not SQLServer performing fine out of the box, with the default settings?”
My answer is that SQL Server is like a car. With the default settings it will run fine for most people. But if you want a racecar you will have to make adjustments and tweaks ;)
So let’s see which default settings we usually want to change:
Database File Autogrow
The default autogrow settings are 1 MB for the data files and 10% for the transaction log files. These values are inherited to your user databases from the model system database.
Change them to a fixed MB value ASAP (e.g. 100 MB). And while you are at it, change these values at the model database too so that you never have to look back at this issue again.
Database Recovery model
The default recovery model is FULL. The typical support scenario related to this default value is that after a few weeks/months, the database administrator calls you and mentions that his data file is 1 GB, but his transaction log file is 100 GB. And he is using FULL recovery model for his databases. And he is not taking any transaction log backups. Hmmm…
So change the recovery model to SIMPLE for your low-risk databases and keep using FULL recovery model only for your high-risk production databases. And change this value at the model database too so that you never have to look back at this issue again.
Max Server Memory
The default value is 2 TB. This means that the SQL Server process will allocate all the memory it can from the OS and if the OS needs to get some memory back, the SQL Server process will have to page out its memory, which leads to a nice variety of performance issues.
Use a custom Max Server Memory setting. Allocate as much memory as possible to the SQL Server process, but leave some memory for the OS too. e.g. if the OS installed memory is 24 GB, you could set the max server memory to 20 GB.
Max Degree of Parallelism
The default valueis 0, so SQL Server can use any value he likes. I don’t like to give such liberties to any of my processes :D
There is a nice article that explains how to configure this setting to a good, fixed value: https://support.microsoft.com/kb/2806535
Also, don't forget that some applications, like Sharepoint, strongly recommend that you set the value of this option to '1'.
TEMPDB data files
By default, TEMPDB has one data file. This usually does not suffice and you need to add more data files. If you want to know how many data files your TEMPDB needs and how you should configure them, take a look at this nice blog post: https://blogs.msdn.com/b/cindygross/archive/2009/11/20/compilation-of-sql-server-tempdb-io-best-practices.aspx
Backup Compression
Backup Compression is a nice feature that first appeared on SQL Server 2008 and allows your database backup operations to finish faster and for the backup files to have a smaller size. By default it is turned off and that’s a shame. You want this feature to be turned on.
Instant File Initialization
By default, the SQL Server setup does not provide the ‘Perform volume maintenance tasks ‘ privilege to the SQL Server service account. Without this privilege, the data file autogrow operations will be slower, as the SQL Server process has to zero out the new space when you create or expand a data file. Consider giving this privilege to your SQL Server service account manually: https://blogs.msdn.com/b/sql_pfe_blog/archive/2009/12/23/how-and-why-to-enable-instant-file-initialization.aspx
That’s all for now, hope you enjoyed this post! See you next week! :)