TempDB system database plays as important role on SQL Server performance.
When you install SQL Server by default SQL Server will create one small TempDB data file and one log file in the default location for TempDB on your SQL Server instance. You should change the default file locations of TempDB, otherwise default file locations, the TempDB files will be in a sub-directory on the same drive where your SQL Server binary files are located. This is most likely your C: drive on your database server, which is not a good place. If this the case and your TempDB is in C: drive, good practice is to move it to fast, dedicated logical drive.
You also need to create some additional TempDB data files, which should all be the same size. If all of the TempDB data files are the same size, SQL Server will use all of them equally. The reason to have multiple data files is to reduce possible allocation contention, as objects are created and destroyed in TempDB.
Microsoft suggest to create one TempDB data file per physical process core. But now a days there is four,eight and more than that core processors. So I think this is unnecessary. In general consent you should start with four or more TempDB data files and be sure to make all of the same size and later look for allocation contention and make appropriate decision for additional TempDB data files.
No comments:
Post a Comment