First, we need to understand "What Goes in temp DB?"
· Internal temporary objects needed by SQL Server in the midst of other complex operations. For example, worktables created by a hash aggregate will be stored in TempDB or interim tables uses in hash joins (almost anything that shows as "hash" something in your query plan output is likely to go to tempdb).
· User objects created with either #
· Local temp objects are objects accessible ONLY in the session that created it. These objects are also removed automatically when the session that created it ends (unless manually dropped).
· Global temporary objects are objects that are accessible to ANYONE who can login to your SQL Server.
· User-defined Table VariablesPerformance
The SQL Server uses TempDB to store intermediate results as part of executing a query, for example to create a hash table or to sort as a result of order by. More often than not, customer may not realize that the cause of the slowdown in the workload is because the TempDB is on the slower device. This can happen as workload changes over time or the TempDB was configured on a slower device. The performance of your TempDB is critical to your workload.
SQL Server recommends that you create as many files as there are COREs or CPUs to minimize allocation bottlenecks
In one of my projects, I faced a lot of temDB utilization. The size was increased to almost 10 GB where the normal size of the temp DB before this specific implementation was 1-2 GB. On investigation, I came to know that I was using a lot of queries to de-normalize data from to OLTP database for my reporting database. I, then broke down the process into smaller pieces / batches and the tempdb size came back to normal. So, batch size is also important especially when you are saving you query data into temp tables.