TEMPDB Enhancements in SQL Server 2016

  • dharmendra.keshari

    SSC Enthusiast

    Points: 114

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

  • Yet Another DBA

    SSCarpal Tunnel

    Points: 4299

    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.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the info.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Yet Another DBA (4/13/2016)


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

    I find this to be true too.

  • WayneS

    SSC Guru

    Points: 95341

    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.

    Wayne
    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!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • sc_mummert

    SSC Rookie

    Points: 36

    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 6 (of 6 total)

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