I’m writing an article covering common SQL Server configuration issues and as I write about how Microsoft have addressed some of these in SQL Server 2016, I started thinking about what they’ve missed or what they should include in the next release, be it SQL Server 2018 or version 14.
RCSI as the default isolation level
Read Committed Snapshot Isolation Level should really be the default for all new SQL Server databases and Microsoft should encourage this by changing the default database option for Is Read Committed Snapshot On to true. Upgraded databases would continue to have this set as false and new databases would also have the option to use the old version if required.
Built-in maintenance
In the old days we had our own database maintenance scripts and now most people just use Ola Hallengren scripts (because they’re awesome) but these should have been shipping with SQL Server for years. Even if it’s a maintenance plan (rather not), SQL Server should ship with cross database jobs that handle index maintenance and integrity checks automatically. Either using a default schedule or a configured schedule during the installation.
Increase Cost Threshold for Parallelism
The cost threshold for parallelism should be updated to reflect modern hardware. I’m not sure how it came to be 5, but I understand it’s a number based on late nineties hardware. I generally set this to a minimum of 30, sometimes much higher but 30 would be a much better default than 5. A better solution would be to change this figure to represent seconds or milliseconds as it once did.
Format drives during install
Most SQL Server installations have dedicated drives for the data, logs and tempdb but they’re not always formatted correctly, with many systems still using an allocation unit size of 4KB, rather than 64KB. It would be useful if the installer formatted drives but a simple check of the allocation unit size and a warning would be helpful.
Server memory as a percentage
I work with the rule that a SQL Server instance should use 90% of the available RAM, unless it’s on a small system with not much to play with. On shared servers you may reduce this percentage further, but in most cases the figures your inputting are based on a percentage.
This will also fix the problem where the available RAM has changed but SQL Sever hasn’t been updated. This often happens after infrastructure upgrades where available and much needed RAM is going to waste or virtual machines that have been resized to 32GB but SQL Server is still configured to use 58GB.
Compress backups as default
It’s very rare that people would not want to compress their backups, therefore this option should be enabled by default. Saving space and time for those that backup over the network.
Data Boffin 