TempDB Configuration Best Practices

1. Do not change collation of TempDB from the SQL Server instance collation.

2. Do not change the TempDB database owner from sa.

3. Do not drop the TempDB database.

4. Do not drop or revoke the guest user from the database.

5. Do not change the recovery model from SIMPLE.

6. Allow for tempdb files to automatically grow as required.

7. Ensure the disk drives TempDB resides on have RAID protection in order to prevent a single disk failure from shutting down SQL Server. Keep in mind that if TempDB is not available then SQL Server cannot operate.

8. Move the TempDB database to seprate set of disks.

9. Size the TempDB database appropriately.

10. Configure no. of TempDB database data files as per available C.P.U. Cores

✔ If no of cores < 8 then use the same number of data files as logical processors
✔ if no. of cores between 8 to 32 inclusive then 1/2 data files as logical processors
✔ if no. of cores > 32 then 1/4 data files as logical processors

11. Make each data file the same size; this allows for optimal proportional-fill performance.

12. Put the tempdb database on a fast I/O subsystem. Use disk striping if there are many directly attached disks.

13. Set the file growth increment to a reasonable size to avoid the tempdb database files from growing by too small a value. If the file growth is too small, compared to the amount of data that is being written to tempdb, tempdb may have to constantly expand. This will affect performance.

14. General guidelines for setting the FILEGROWTH increment for tempdb files

✔ If TempDB file size > 0 and < 100 MB then the recommended filegroup increment can be 10 MB
✔ If TempDB file size > 100 and < 200 MB then the recommended filegroup increment can be 20 MB
✔ If TempDB file size >= 200 MB then the recommended filegroup increment can be 10% OR any fix value depending on requirement or on basis of I/O system capabilities

15. Avoid shrinking TempDB (or any database) files unless you are very certain you will never need the space again.

16. Keep auto create statistics & auto update statistics OFF. It will create tempDB objects faster.

17. Keep auto close OFF

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx