Performance decreases in TFS 2013 Update 2 after you upgrade to SQL Server 2014
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:
- SQL Compilations/sec
- SQL Re-compilations/sec
- %Processor Time
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.
- 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.
- 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.
Note This information relates to SQL Server 2012 but also applies to SQL Server 2014.
For more information about the Work Item Tracking syncnamechanges attribute, see the following MSDN article:
For more information about how to view or change the compatibility Level of a database, see the following TechNet topic:
Article ID: 2953452 - Last Review: 04/05/2014 02:03:00 - Revision: 1.0