Two excellent articles by Rodibdably.
The first article cover maintenance of the TempDB.
The second describe the use of database trigger to audit DDL operations.
Not only at systems with very high usage of TempDB the correct planing is useful.
Do you exactly know how much workload your system must manage in the future ?
From my experience it could be a difficult task to change system settings during production when no downtime is available.
Therefore here are some rules to prevent issues with impact on the availability and/or performance.
First consider to place TempDB on his own physical disk array (Better more smaller disks as fewer big disk, Keyword: I/O).
Use RAID 1+0 or RAID-1.
As reported by Microsoft create a single data file for every CPU. Note: Dual Core count as 2 CPU’s, Hyperthreading not.
Equal sizing of the data files are required. Note: This apply only to the data files, not to the log files.
About the sizing of TempDB. There is no golden rule. During my research i found various opinions about sizing TempDB.
For my systems i use follwing setup:
40 % of the size of all production databases for the TempDB data files, 40% of the size of TempDB for the log file.
This values i use for the preallocation of the the TempDB data files size.
If you use the autogrowth option take sure to use equal increments for every data file.
Note: Use a reasonable value for the increments.
If the value is to small you will receive a performance hit every time the system expand your files.
This is workload on your server you don’t need.
