SQL Server Tempdb Improvements from 2014 to 2022 with demos

By watching this you will be able to answer the below
1) Why do we need to turn on Trace flag 1117 & 1118?
2) Do we need to enable 1117 & 1118 even on the latest Versions?
3)What are the different types of contentions inside Tempdb?
4) What is PFS/GAM/SGAM contention?
5) What is Metadata contention?
6) From which Versions of SQL can we avoid SGAM contention(not completely though)?
7)From which Versions of SQL can we avoid PFS contention(not completely though)?
8)From which Versions of SQL can we avoid GAM contention(not completely though)?
9) What are the differences between Object Allocation contention & Metadata contention?
10) Can we overcome Metadata contention if So how?
11) What are the algorithmic changes that were made on the recent versions of SQL Server?

[@ssk3532]

Thanks for sharing

[@shoaibsalmani1187]

As always wonderfully explained

[@vamsychiranjeevi9619]

Glad you liked it

[@babban2020]

Hello Brother, you had mentioned at the of the vid on how to fund out tempdb..contentions...could you please make a vid or tell the queries you said are available on the net??

[@brijeshbind4832]

nicely explained..

[@vamsychiranjeevi9619]

Thanks for liking

[@rahulmistry3777]

Hi do you take online class ??SQL DBA and Performance tuning

[@vamsychiranjeevi9619]

yes I do you can reach me on my WhatsApp [phone]

[@ravi204834]

1117 &1118 is not applicable for only tempdb . It is applicable for user databases as well

[@vamsychiranjeevi9619]

Yes it is applicable but in a slightly different manner though. By default Mixed Extent allocation is OFF. you can turn it on using ALTER DATABASE DBname SET MIXED_PAGE_ALLOCATION ON;

[@Greatest.Everrr]

How about pre-size tempdb files and do not let any user run inefficient queries period.

[@vamsychiranjeevi9619]

Well....the query will fail due to 1105 error as it will reach its max size

[@Greatest.Everrr]

houston v have a problem 😁

[@miguelsanchez10]

I think you’re wrong with some concepts dude

[@vamsychiranjeevi9619]

Kindly correct me where I went wrong. I am happy to correct myself.