Heavy Input Output Contentions on TEMPDB Files
Heavy input/output contention on temp database was one of the bottlenecks that appeared in the analysis.
Optimum TEMPDB configuration is important for any solution that has heavy data access as in the case of TIBCO MDM. If you observe heavy TEMPDB input/outptut contention due to huge spikes on the Microsoft SQL server CPU, check the TEMPDB file. The contention can occur even if there is only one TEMPDB file.
If this is the case, you should consider increasing the number of TEMPDB files according to the initial data access requirements and server configuration.
Although there is no convention on the number of files or size, TIBCO recommends having one TEMPDB file per two CPUs with identical sizes.
Another factor to consider while configuring the TEMPDB files is the growth of each file. You can set the growth to auto-growth with a percentage of growth factor or to no auto-growth limit. A Microsoft best practice is to allow no auto-growth but you can also set an auto-growth of 300MB.