SQL 2016 – TempDB Optimizations are enabled by default Woot!!

Last year I had spent a decent amount of time writing about tempdb. With SQL 2016 a lot of those point are validated and enabled by default going forward. Starting with SQL 2016 additional tempdb files will be created during install time and the DBA has the option of preventing this if needed. Additionally there are only up to 8 files that will be created as per the most common recommendations out there. However I think the most important aspect of this feature is not the fact that there are additional files created but that MS has enabled the trace flag 1117 and 1118 by default. The enhancements combined will reduce the tempdb maintenance overhead for DBAs and make post install checklists a lot simpler.

There is still one thing that the DBA is expected to perform manually and that is the sizing of the MDF files that are created, since the default size is8MB initial size and 64MB for autogrowth which I am sure we call agree is too little.

You can find the previous articles on tempdb below.

http://enabledbusinesssolutions.com/blog/proof-that-tempdb-mdf-and-ldf-files-are-not-dropped-at-the-os-level-when-sql-server-is-restarted/

http://enabledbusinesssolutions.com/blog/video-on-performance-tuning-memory-allocation-and-spill-issues/

http://enabledbusinesssolutions.com/blog/some-new-screens-you-will-likely-encounter-when-installing-ms-sql-2016/

http://enabledbusinesssolutions.com/blog/the-tempdb-why-shrinking-doesnt-always-work/

http://enabledbusinesssolutions.com/blog/trace-flag-1118-proportional-filling-and-the-tempdb/