After you upgrade to Microsoft SQL Server 2014, you notice a decrease in performance in Microsoft Team Foundation Server (TFS) 2013 Update 2.
For example, you notice that Work Item Tracking slows down. When this occurs, it takes significantly longer to open and change work items if you have many fields that are set to syncnamechanges=true.
Additionally, if you have many Team Project collections in the affected TFS instance, you notice the following issues:
Throughput drops significantly, as measured by the following performance counters:
Batch requests/sec (DT)
TFS Services/Current Reqs/sec (AT)
Web Services/Total Method Requests/sec (AT)
The following DT performance counters increase on average:
These issues occur because the hardware requirements for SQL Server 2014 are greater than those for ealier versions of SQL Server.
The Work Item Tracking performance issue occurs because several views that are used by Work Item Tracking contain one or more joins to the Constants table for each field that includes this attribute. The new cardinality estimator in SQL Server 2014 sometimes determines incorrectly that these joins will return more than one row. When there are many of these joins, SQL Server can miscalculate the total number of rows that are returned by the views as a very large number. Therefore, the program dtermines incorrectly that it must spend lots of time to optimize the query plans that involve the views.
To work around the overall performance issue, use the following methods:
If you have a large configuration (500 or more users), increase the RAM on the computer that is hosting SQL Server. A good standard to follow is 0.4 gigabytes (GB) per collection database.
If the performance issues persist, try the next method.
Perform the following tasks:
Make sure that the SQL minimum and maximum memory settings are set explicitly to leave at least 2 GB of free physical memory for the system.
Enable locked pages for the account that is running the SQL service.
Enable SQL trace flag 8032.
To specifically work around the Work Item Tracking performance issue, use the following method:
If you see a significant decrease in performance when you open or edit work items after you upgrade to SQL Server 2014, change the Compatibility Level setting of your databases to 110. This causes SQL Server to use the earlier cardinality estimator tool. This tool more accurately estimates the number of rows that are returned by the joins.
For a description of trace flag 8032, see the following Microsoft TechNet topic: