TEMPDB Enhancements in SQL Server 2016

Question

TEMPDB Enhancements in SQL Server 2016

dharmendra.keshari

SSC Veteran

Points: 214
More actions
April 12, 2016 at 9:43 pm

#313284

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

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

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

Yet Another DBA SSCarpal Tunnel Points: 4299 More actions · Answer 1

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 More actions · Answer 2

akljfhnlaflkj

SSC Guru

Points: 76202

April 13, 2016 at 7:18 am

#1871671

Thanks for the info.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 3

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: 95460 More actions · Answer 4

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 More actions · Answer 5

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.