How do I fix tempdb contention?
Increase the number of data files in tempdb to maximize disk bandwidth and reduce contention in allocation structures. As a rule, if the number of logical processors is less than or equal to eight (8), use the same number of data files as logical processors.
How do I monitor tempdb contention?
There are three things you can do to alleviate this kind of contention and increase the throughput of the overall workload:
- Stop using temp tables.
- Enable trace flag 1118 as a start-up trace flag.
- Create multiple tempdb data files.
Why does SQL Server have contention with tempdb?
When the tempdb database is heavily used, SQL Server may experience contention when it tries to allocate pages. Depending on the degree of contention, this may cause queries and requests that involve tempdb to be briefly unresponsive.
What’s the optimal number of tempdb data files?
The optimal number of tempdb data files depends on the degree of contention seen in tempdb. As a starting point, you can configure tempdb to be at least equal to the number of logical processors that are assigned for SQL Server. For higher-end systems, the starting number could be eight (8).
How can I improve the concurrency of tempdb?
To improve the concurrency of tempdb, try the following methods: Increase the number of data files in tempdb to maximize disk bandwidth and reduce contention in allocation structures. As a rule, if the number of logical processors is less than or equal to eight (8), use the same number of data files as logical processors.
How is metadata contention managed in SQL Server?
This synchronization operation is managed by the SQL Server internally. TempDB metadata contention occurs when many sessions try to access the SQL Server TempDB’s system tables at the same time during the creation of the temp tables.