Tempdb is a system database in SQL Server that is used to store temporary objects, such as temporary tables and table variables, as well as other temporary data generated during query execution. It is important to properly configure and manage tempdb to ensure optimal performance of your SQL Server instance.

One best practice for tempdb is to have one data file per processor (core) and all data files should have the same initial size and growth. This helps to evenly distribute the workload across multiple processors and prevent performance bottlenecks.

In some scenarios, it may be necessary to resize the data files of tempdb. For example, if the data files are too small for the actual usage, it is recommended to increase their size to something more appropriate. This is because having too many extents in the data files can lead to fragmentation and slow I/O performance.

In other cases, the data files may be incorrectly sized or have different initial sizes in a multiprocessor instance. For example, you may have four data files for tempdb on a 4-processor machine, but three of them have an initial size of 2MB and one has an initial size of 5MB. In this case, it is recommended to resize the data files so that they all have the same initial size, such as 3MB.

To resize the data files of tempdb, you can use the following steps:

Increasing the Size of Data Files

1. Identify the current sizes of the data files by running the following query:

  SELECT name, size / 128.0 AS "Initial Size (MB)", (fileproperty(name, 'spaceused')) / 128.0 AS "Used (MB)"
  FROM sys.master_files
  WHERE database_id = 2
  

2. Increase the size of each data file to the target initial size by running the following command for each data file:

  ALTER DATABASE tempdb MODIFY FILE (name = 'tempdev', size = 3MB, filegrowth = 1MB)
  

3. Verify that the file size and growth are the same for all data files by running the following query:

  USE tempdb
  SELECT name, size / 128.0 AS "Initial Size (MB)", is_percent_growth, growth * 8 AS "Growth (KB)"
  FROM sys.master_files
  WHERE database_id = 2
  

4. Restart SQL Server using SQL Server Configuration Manager.

Decreasing the Size of Data Files

1. Stop the SQL Server service.
2. Delete all tempdb data files except for tempdev.mdf.
3. Start the SQL Server service with the /f switch to start a minimally configured instance of SQL Server with a small tempdb.
4. Start SQL Server Management Studio (SSMS) and connect to the instance.
5. Change the file size of the remaining data file to the new size by running the following command:

  ALTER DATABASE tempdb MODIFY FILE (name = 'tempdev', size = 3MB, filegrowth = 1MB)
  

6. Add additional data files with the new initial size by running the following commands:

  ALTER DATABASE tempdb ADD FILE (name = 'tempdev2', filename = 'c:\program files\microsoft sql server\mssql.1\mssql\data\tempdev2.ndf', size = 3MB, filegrowth = 1MB)
  ALTER DATABASE tempdb ADD FILE (name = 'tempdev3', filename = 'c:\program files\microsoft sql server\mssql.1\mssql\data\tempdev3.ndf', size = 3MB, filegrowth = 1MB)
  ALTER DATABASE tempdb ADD FILE (name = 'tempdev4', filename = 'c:\program files\microsoft sql server\mssql.1\mssql\data\tempdev4.ndf', size = 3MB, filegrowth = 1MB)
  

7. Restart SQL Server.

By following these best practices for resizing tempdb data files, you can ensure optimal performance and avoid potential issues related to fragmentation and slow I/O. It is important to regularly monitor the usage and size of tempdb to make necessary adjustments as needed.

For more information on working with tempdb and troubleshooting performance issues, you can refer to the official documentation provided by Microsoft:

• Working with tempdb
• Troubleshooting performance

Remember, proper configuration and management of tempdb is crucial for maintaining the performance and stability of your SQL Server environment.