MSSQL: reduce the tempdb usage

READ COMMITTED SNAPSHOT can reduce the usage of tempdb .

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms188277(v%3dsql.105)Default and query to find out what I have now

2 Types

• A new implementation of read committed isolation that uses row versioning when the READ_COMMITTED_SNAPSHOT database option is ON.
• A new isolation level, snapshot, that is enabled when the ALLOW_SNAPSHOT_ISOLATION database option is ON.

 ALTER DATABASE VCDB_2016 SET READ_COMMITTED_SNAPSHOT ON;  

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms188277(v=sql.105)

Occasionally, vpx_text_array flushing can cause tempdb to grow exponentially.  Setting READ_COMMITTED_SNAPSHOT to ON may be the only option if DB recovery already in SIMPLE mode.