Chad Callihan has a two-parter for us. First up is a look at tempdb usage:
The tempdb database in SQL Server holds a little bit of everything over its existence. There can be the temporary tables that you created, work tables like spools that SQL Server creates, version store data, and much more in tempdb at any time.
Is your tempdb having a hard time keeping up with the workload on your SQL Server? How can you find out what is using tempdb the most?
Chad also recommends picking the right number of files for tempdb:
We just made it through Thanksgiving dinner and there’s another big Christmas dinner coming up in few weeks. Sometimes you might find that one plate is not enough to hold all of that food. Just as multiple plates can make big dinners easier to manage, multiple tempdb data files can make the SQL Server workload easier to manage.
Read on to see how to track and modify this setting.