Configuring TempDB for Optimal TFS Performance

As any complex database application would Team Foundation Server uses TempDB both explicitly and implicitly. In addition the Version Control component will use Read Committed Snapshot Isolation (RCSI) in RTM for improved concurrency. RCSI is a new feature to Yukon and provides a mechanism for readers to read committed changes without having to take a shared lock on the data. In doing so however it stores changed rows for active transactions in its version store (TempDB). This further emphasizes the need for an optimally configured TempDB. So here are some pointers found during our testing:

1. Manually grow TempDB - file growth can be expensive and time consuming. SQL Server reverts back to the original TempDB size upon re-start - thereby you incur the cost again after every restart. I suggest manually growing the data file to approx. 20% of all the TFS databases. This also will prevent file fragmentation if there are multiple files on the same drive.

2. Use multiple equal sized data files - if you are using a multi-processor data tier, use N equal sized data files (when N is the # of processors, the sum of all the data files can equal 20% of all your databases). This will allow SQL Server to allocate extents in a round robin fashion and reduces contention. If you have a dedicated drive for tempdb with more than adequate space assigned -  turn off auto-growth to prevent the data files from unevenly growing and interfering with the round robin allocation. If you do choose to turn off auto-growth make sure that you have allocated enough space to tempdb to prevent it from running out of space.

Ofcourse -  dedicated (or faster) drives for tempdb will help too....