Tempdb contention is a performance issue which occurs when multiple sessions try to access same tempdb resources simultaneously. It creates blocking which returns the result slowly & creates slow query performance, timeouts, deadlocks, and other issues.
Few common reasons of tempdb contention are given below:
1. If multiple big/heavy queries are running on the server simultaneously, that can create TempDB contention.
2. TempDB contention occurs if we use multiple temporary tables in our queries or SP. If the query is using multiple indexes, it'll also help to raise TempDB contention.
3. If multiple transactions are using snapshot isolation or performing online index operations simultaneously, one of reason to create TempDB contention.
How to address TempDB contention issue:-
1. Monitor TempDB usage using profiler, perfmon tool, DMVs etc.
2. Work on query optimisation to enhance performance of query. Use CTE in plan of Temporary Tables.
3. If possible, increase no. of tempdb data files as well as size of all files should be same. Also increasing file size will help to avoid this issue.
4. Another point is to use snapshot isolation when necessary and needed.
