ScheuNZ ScheuNZ - 1 year ago 148
SQL Question

TFS 2015 slow to populate Incoming Requests after update

My organisation recently applied an update to TFS 2015 (14.102.25423.0 according to the 'About' page of the web interface) that resulted in the 'My Work' tab in Visual Studio 2015 taking up to one minute to populate. I played around with the queries and managed to narrow the problem down to population of the 'Incoming Requests' section of that tab. Under the hood, this is executing the following WIQL query.

SELECT [System.Id], [System.Links.LinkType], [System.Title], [System.State], [System.Reason], [System.AssignedTo]
FROM WorkItemLinks
WHERE (Source.[System.TeamProject] = @project and Source.[System.WorkItemType] in group 'Microsoft.CodeReviewRequestCategory' and Source.[System.AssignedTo] <> @me and Source.[Microsoft.VSTS.Common.StateCode] <> '1')
and ([System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Forward')
and (Target.[System.WorkItemType] in group 'Microsoft.CodeReviewResponseCategory' and (Target.[System.AssignedTo] = @me or Target.[Microsoft.VSTS.Common.ReviewedBy] = @me) and Target.[Microsoft.VSTS.Common.StateCode] <> '2')
ORDER BY [System.CreatedDate] desc, [System.Id] mode(MustContain)

  • I've reproduced the slowness using the TFS REST API described in (passing the WIQL query above in the body of the POST request).

  • The following code review selectors are slow to populate: My Code Reviews & Requests, Incoming Requests.

  • The following code review selectors are fast to populate: My Code Reviews, Recently Finished, Recently Closed.

  • The problem is occurring for all users, not just my user.

  • No one on the team has more than a few code reviews open at any one time.

  • The problem started occurring practically overnight i.e. on Friday the queries were completing in a second or so, on Monday the queries were taking up to a minute.

  • Our TFS environment is hosted on Windows Server 2012 (non-R2).

  • Our TFS environment is backed by SQL Server 2012, SP3 (11.0.6020).

  • The upgrade to TFS2015.3 was completed as per Microsoft instructions and no issues were encountered and there are no messages in the logs to indicate anything is wrong.

Does anybody have any suggestions about what might be causing this slowness and what can be checked in order to narrow the performance problem down further?

Answer Source

Answering my own question here... My organisation ended up escalating this through Microsoft and eventually found that there was an issue with out of date statistics causing bad query plan generation. The query that was used to retrieve code review details was taking more than 60 seconds each time it was run.

The queries below will most likely make a significant different to performance if you encounter the same problem.

use <collection db name>;

use <collection db name>;
UPDATE STATISTICS [dbo].[tbl_WorkItemCustomLatest] WITH FULLSCAN

For reference, there's a duplicate of my original post on Microsoft Connect here: The comments from Microsoft in this post indicate a number of people were seeing similar behaviour.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download