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


GO


RECONFIGURE


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


GO


RECONFIGURE


GO


 


 


 


 

Comments (0)