Team Foundation Server Performance - SQL Server Configuration Settings

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: https://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 @ https://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

GO

RECONFIGURE

EXEC

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

GO

RECONFIGURE

GO