TEMPDB Enhancements in SQL Server 2016

  • Comments posted to this topic are about the item TEMPDB Enhancements in SQL Server 2016

  • The single vs many tempdb files is for me a distraction of where the performance issues tend to be ie the query or the actual design of the database. Most of the systems I have worked on over the years have not been the monsters pushing the hardware to its limit, instead they tend to be badly designed schema with poor queries.

    No doubt we will see a flurry of articles saying that the new default is wrong with various people stating their edge case as the reason to change the new default to something else.

  • Thanks for the info.

  • Yet Another DBA (4/13/2016)

    ... instead they tend to be badly designed schema with poor queries.

    I find this to be true too.

  • Trace Flag 1117 – The below screen shot shows that the is_autogrow_all_files value is 1 for only tempdb. This indicates that it is the only system database that supports Autogrow_All_Files ON option.

    The functionality from Trace Flags 1117 and 1118 are now both controlled with changes to the ALTER DATABASE command (and thus, just setting the TF has no effect)... and both work on all databases, not just system databases - the feature just needs to be enabled on each database. See my blog post [/url] for more details. Because both of these features are so valuable to have enabled in tempdb, they are enabled by default for tempdb.

    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • This is great at a point in time. However in the cloud things get a little different. We are AWS not Azure and BYOL (Bring your own license) , so when we up or downsize and instance it needs to re-adjust itself.  I hate baby sitting so I have a startup script that resets DOP, Cost and memory at startup.  If I resize a VM I really do not want to do things afterwards, hence the script.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply