Tip of the Day – Check your TempDB data file count and sizes
Have you checked your TempDB data file count and sizes?
And moreover, are you monitoring its performance?
A general rule of thumb for most DBAs, for best TempDB performance in SQL Server, you should have one TempDB data file for each processor core in your SQL Server and each of those data files should be the exact same size and Autogrowth turned off. The latter point is very important for correct and balanced use of the data files allocated to to the database in that filegroup, but the former point is kind of a generalized practice to make it easier on yourself when setting up a new SQL Server. We even once had an onsite Microsoft Server Analysis rep tell us we should allocate one data file per CPU, but experience tells me this isn’t always the best solution. You really need to monitor your TempDB utilization and determine what you need. If this wasn’t TempDB, how would you determine if you needed more data files for your user databases? You’d monitor performance, right? Looking for certain wait types, right? Sure you would.
See a fantastic write up by Paul Randal (SQL Server MVP and Microsoft Regional Director) below.
