The installation process of SQL Server is continually evolving.
Recently the setup allowed tempdb file adjustments with a default of 4 files and the ability to grant the Perform Volume Maintenance Task privilege.
Such a focus on tempdb was a surprise to me as in the 13+ years I’ve worked with SQL Server (tackling literally tens of thousands of instances) there was only once when more than 1 data file was required. To put this in perspective, the SQL Server was accessed by all students at a large university and the tempdb GAM contention was obviously clear. Adding another single data file (2 in total) to this high spec server resolved the issue.
Adding additional tempdb files, to me is an advanced feature and should be carefully considered so as to not waste disk space, ensure even file growths, minimize complexity etc.
In my view it would have been more productive for Microsoft to have focused the setup on the following:
- set memory limits (this has to be done 100% of the time) and don’t stop with the engine, include SSAS and SSRS
- set max degree of parallelism (I believe the default of 0 is incorrect, rather it should be 1 as most instances are not dedicated to a hosting a single application due to licensing costs,consolidation etc)
- set backup compression (should be defaulted to 1)
- set optimize for ad hoc workloads (should be defaulted to 1)
- allow setting Perform Volume Maintenance Tasks (tick)
- allow setting Lock Pages in Memory
- allow enabling remote admin connections
- allow setting the listening port and adding the appropriate firewall entry (this would likely save thousands of can’t connect issues)