Tempdb is a system database in SQL Server that is used for temporary storage of data during query processing and other operations. It is important to optimize Tempdb for better performance and to avoid performance issues. Here are some tips to optimize Tempdb in SQL Server:

1. Configure Tempdb for optimal performance: Configure tempdb to use multiple data files (the recommended number is 1 data file per CPU core), preallocate the data files to a fixed size, and place the data files on separate physical disks to reduce contention.
2. Monitor Tempdb usage: Monitor the size and usage of Tempdb regularly to identify any issues. Use DMVs (Dynamic Management Views) to monitor the size, file growth, and space usage of Tempdb.
3. Reduce contention: Tempdb is a shared resource, and contention can occur when multiple sessions try to access it simultaneously. Avoid creating temporary tables with the same name in different sessions, avoid creating too many indexes on temporary tables, and use table variables instead of temporary tables whenever possible.
4. Avoid large sort operations: Large sort operations can consume a lot of Tempdb space and can cause performance issues. Use appropriate indexing and query optimization techniques to avoid large sort operations.
5. Minimize transaction log usage: When Tempdb is used for sorting or other operations, SQL Server may use the transaction log to record the changes. This can cause the transaction log to grow quickly and can impact performance. Use minimally logged operations, such as bulk loading, to reduce the usage of the transaction log.
6. Consider using SSDs: If possible, consider using solid-state drives (SSDs) for Tempdb. SSDs offer faster read/write speeds and can significantly improve the performance of Tempdb.
7. Use trace flags: There are some trace flags that can be used to optimize Tempdb performance. Trace flag 1117 can be used to ensure that all data files in a filegroup grow at the same rate, while trace flag 1118 can be used to reduce contention on allocation pages.

By following these tips, you can optimize Tempdb in SQL Server for better performance and avoid potential issues.

###########Script to check TempDB Contention #############

SELECT wait_type, waiting_tasks_count, wait_time_ms / 1000.0 AS wait_time_sec, signal_wait_time_ms / 1000.0 AS signal_wait_time_sec
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGEIOLATCH_%'
  OR wait_type LIKE 'PAGELATCH_%'
  OR wait_type LIKE 'CXPACKET%'
ORDER BY wait_time_ms DESC;

###########Script to check identify which stored procedures are causing Tempdb #############

SELECT TOP 50
    st.[text] AS [Script],
    qs.total_worker_time AS [TotalWorkerTime],
    qs.total_worker_time / qs.execution_count AS [AvgWorkerTime],
    qs.execution_count AS [ExecutionCount],
    qs.total_logical_reads AS [TotalLogicalReads],
    qs.total_logical_reads / qs.execution_count AS [AvgLogicalReads],
    qs.total_logical_writes AS [TotalLogicalWrites],
    qs.total_logical_writes / qs.execution_count AS [AvgLogicalWrites],
    qs.total_physical_reads AS [TotalPhysicalReads],
    qs.total_physical_reads / qs.execution_count AS [AvgPhysicalReads],
    qs.creation_time AS [CreationTime],
    qs.last_execution_time AS [LastExecutionTime],
    qp.query_plan AS [QueryPlan]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE st.[dbid] = DB_ID('tempdb')
ORDER BY qs.total_logical_reads DESC;

If you are on older version of SQL Server, Consider upgrading to SQL Server 2019 as SQL Server 2019 introduced several enhancements for Tempdb, including:

1. Accelerated database recovery: SQL Server 2019 introduces a new feature called “Accelerated Database Recovery” (ADR) that helps reduce the time required to recover a database after a crash. ADR uses a new log format that allows the recovery process to skip the undo phase for transactions that were active at the time of the crash, which can significantly reduce the amount of work required to recover Tempdb,.
2. Multiple Tempdb, data files at installation: SQL Server 2019 allows you to specify the number of Tempdb, data files to create during installation, which can help optimize Tempdb, performance from the start.
3. Improved Tempdb, configuration: SQL Server 2019 introduces several improvements to tempdb configuration. For example, it includes a new recommendation to allocate at least 50% of the available storage space to tempdb, and it introduces a new trace flag that can help reduce tempdb contention by using a round-robin allocation strategy for new objects.
4. Automatic sizing: SQL Server 2019 introduces a new feature called “Automatic Sizing” for Tempdb,, which allows SQL Server to automatically adjust the size of Tempdb, data files based on usage patterns. This can help reduce the need for manual monitoring and adjustments to Tempdb,,.
5. Tempdb spill to memory-optimized table: SQL Server 2019 introduces the ability to spill Tempdb, data to memory-optimized tables instead of disk, which can improve performance for certain types of workloads.

These enhancements can help improve Tempdb, performance and reduce the risk of Tempdb related issues in SQL Server 2019.

https://learn.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-ver16