Tempdb best practices
When SQL Server is installed the setup program creates tempdb database. The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server. Tempdb database provides temporary space for various operations and is recreated every time SQL Server is restarted so always there is a clean copy of tempdb database when its restarted. Tempdb is a temporary database used by SQL Server to store temporary tables, table variables, cursors, work tables, row versioning. Operations within tempdb are minimally logged. This enables transactions to be rolled back. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb. Leaving the default configuration of tempDB is not ideal in many environments. Proper optimization of the tempdb database increases the performance of your SQL Server.
Some of the best Practices for tempdb are:
Tempdb Best Practices #1:
Set the recovery model to simple.
Tempdb Best Practices #2:
Allow the tempdb files to automatically grow.
Tempdb Best Practices #3:
Don’t change collation from the SQL Server instance collation.
Tempdb Best Practices #4:
Don’t change the database owner from sa.
Tempdb Best Practices #5:
Don’t drop the TempDB database.
Tempdb Best Practices #6:
Don’t drop the guest user from the database.
Tempdb Best Practices #7:
Set the file growth option to the reasonable size. If the file growth is too less compared to the data then the SQL Server had to increase the size of the tempdb continiously, which will even decrease the performance of the SQL Server.
Tempdb Best Practices #8:
Increase the number of physical file. There should be one physical file per CPU core in the server. So, if you have a dual-core, dual-chip server, you should have four physical database files for the tempdb database. If you have large number of files then you can increase the number of physical I/O operations that SQL Server can push to the disk at any time. The more I/O that SQL Server can push down to the disk level, the faster the database will run. Always Separate the tempDB database from system partition to another set of disks.
Tempdb Best Practices #9:
Make each file of the same size.
Tempdb Best Practices #10:
Most database are high read and low write. So database files are placed on RAID 5 arrays, which give good disk performance at the reasonable cost. Since Tempdb is a very high-write database, a RAID 5 array isn’t the proper option. So for Tempdb, RAID 1 or RAID 10 is the best. Always remember that if TempDB is not available then SQL Server cannot operate.
Tempdb Best Practices #11:
Put the tempdb database on disks other than used by user database.
Tempdb Best Practices #12:
Disabling the auto update statistics will reduce some work for tempdb, so the tempdb performance increases. Usually objects created in the tempdb are fairly small and the statistics will not reach the threshold that causes the statistics to update automatically. By disabling the setting, the SQL Server will stop to check the statistics for updating. But you have to manually update the statistics if you occasionally use large temporary objects.
Tempdb Best Practices #13:
Set the auto create statistics option to false. If you disable this option then SQL Server will stop producing the statistics when the object is first created.
Tempdb Best Practices #14:
Index temporary database object. Just like indexing the normal database improves the performance of the normal database, indexing the temporary tables will increase the tempdb performance.
