I would like to preface this article by saying that for the most part SQL Server requires few changes to the default configuration. However below is a list of potential SQL Server configuration changes, which I have seen help performance on large TFS installations (including our very own devdiv server). I can't guarantee that all / any of them might be applicable to your environment - so the general guidline is if it ain't broke, don't fix it :)

Memory

32 bit servers

If you have a dedicated SQL Server with more than 4gb of RAM - you want to enable AWE. AWE allows a 32 bit OS to address more than 4GB of ram. Instructions to enable AWE can be found at: http://msdn.microsoft.com/en-us/library/ms190673.aspx

64 bit servers

If you are experiencing frequent paging of SQL Server's buffer pool by the OS - you want to enable lock pages in memory. Details on how to detect this is happening and correct it can be found @ http://support.microsoft.com/KB/918483/EN-US

TempDB

I had covered TempDB configuration in a previous blog post of mine, these changes will help almost any TFS installation

Lock Escalation

The general symptom here is, one user has a large checkin (over 5000 items) running - while this is occurring, other users are blocked from checking out files, and doing other version control operations. This is due to a feature called lock escalation where SQL converts finer grain locks into a more coarse lock to curb the memory utilized by locks. If large checkins are a frequent part of your process and you have a 64 bit server you can disable lock escalation. If you have a 32 bit server and want to utilize this option trace flag 1224 (rather than trace flag 1211) is a safer option.

CPU Parallelism

a. You have a multiprocessor server 

b. Run the following query - if you notice CXPACKET high on the result list - it is generally indicative of waits due to parallelism (queries utilizing multiple cpus). Keep in mind the value is in milliseconds and is cumulative since server restart:

SELECT top 10 wait_type, wait_time_ms - signal_wait_time_ms as wait_time

FROM sys.dm_os_wait_stats

WHERE wait_time_ms > 0

ORDER BY wait_time_ms - signal_wait_time_ms DESC

If you frequently see CXPACKET as a wait type, you might want to consider reducing the max degree of parallelism on your server. There isn't really a fixed # you should set this to, and you would need to experiment a bit with your workload to get the right mix. If you do choose to change this setting, my general advice would be to start higher rather than lower.

EXEC sp_configure 'show advanced options', 1

EXEC sp_configure 'max degree of parallelism', <replace with max # of cpus per query>

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....