Free SQL Help for Devs, DBAs, and the Curious

As a database administrator, one of the challenges you may encounter is TempDB contention in SQL Server. This can notably impact the performance of your databases. TempDB contention happens when multiple tasks are trying to create or delete temporary objects at the same time. In this post, we'll discuss ways to minimize TempDB contention, focusing on technologies from SQL Server 2016, SQL Server 2017, MySQL 5.7, DB2 11.1, and Azure SQL.

In SQL Server 2016 and 2017, Microsoft introduced enhancements to reduce TempDB contention. One way to minimize contention is to increase the number of TempDB data files. SQL Server 2016 and 2017 have a configuration option where you can set the number of TempDB files during installation. However, you should not create more files than there are logical cores.

-- T-SQL
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, SIZE = 10MB);
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, SIZE = 5MB);

Another approach to reduce contention is to use the version store in TempDB, which is a storage area for data that is replaced by a transaction. This can be done by enabling Read Committed Snapshot Isolation (RCSI) or Snapshot Isolation (SI).

-- T-SQL
ALTER DATABASE YourDatabaseName SET READ_COMMITTED_SNAPSHOT ON; 
ALTER DATABASE YourDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

In MySQL 5.7, one way to minimize TempDB contention is by increasing the size of the InnoDB buffer pool. This pool is used to cache data and indexes of its tables. By increasing its size, you can store more data and reduce I/O operations on TempDB.

-- MySQL
SET GLOBAL innodb_buffer_pool_size = size_in_bytes;

In DB2 11.1, you can reduce the contention of the temporary tablespace (similar to TempDB in SQL Server) by creating multiple smaller tablespaces instead of a single large one. This can be done using the CREATE TABLESPACE command.

-- DB2
CREATE TABLESPACE temp_space1 IN DATABASE 
PAGESIZE 4096 MANAGED BY SYSTEM USING ('/DB2/temp_space1') 
BUFFERPOOL IBMDEFAULTBP

In Azure SQL, you can minimize TempDB contention by scaling up the database. With Azure SQL, you can easily change the service tier, the compute size, and the storage size of your database, which can help you manage the load on TempDB.

In conclusion, TempDB contention can significantly affect the performance of your SQL Server. However, you can use the methods discussed in this post to minimize the impact of contention and ensure your database runs smoothly. Always remember to monitor your database performance regularly to identify and solve any issues as soon as they arise.