Azure DevOps Sluggish? Performance Dips in Team Foundation Server

Table of Contents

Hey folks! Ever upgraded to SQL Server 2014 and noticed your Azure DevOps or Team Foundation Server 2013 Update 2 suddenly feeling…sluggish? Like trying to run in quicksand? You’re not alone. This article dives into why this happens and, more importantly, how to fix it.

image just illustration

image just illustration

The Problem: Performance Dips After SQL Server 2014 Upgrade¶

So, you’ve bitten the bullet and upgraded to SQL Server 2014. Everything should be faster, right? But then, BAM! Team Foundation Server 2013 Update 2 is slower than ever. Work item tracking is dragging, and if you have loads of team project collections, the whole system feels like it’s crawling. Specifically, you might notice:

• Work Item Tracking Slowdown: Opening and changing work items takes forever, especially if you have many fields set with syncnamechanges=true.
• Throughput Plummets: Performance counters like Batch requests/sec, Team Foundation Server Services/Current Reqs/sec, and Web Services/Total Method Requests/sec all take a nosedive.
• SQL Server Struggles: Counters like SQL Compilations/sec, SQL Re-compilations/sec, and %Processor Time are through the roof.

Why is This Happening? The SQL Server 2014 Conundrum¶

The root of the problem is actually pretty simple: SQL Server 2014 is a resource hog compared to its predecessors. It needs more horsepower to run smoothly.

Work Item Tracking Woes: The slowdown here boils down to how work item tracking uses views with joins to the Constants table for fields with the syncnamechanges attribute. SQL Server 2014’s new cardinality estimator (which basically guesses how many rows a query will return) gets confused by these joins. It often overestimates the returned rows, leading to overly complex and slow query plans.

The Fix: Getting Your Speed Back¶

Don’t despair! There are ways to whip your Team Foundation Server back into shape.

General Performance Boosters¶

• More RAM: If you have a large setup (500+ users), your SQL Server machine needs more RAM. A good rule of thumb is 0.4 GB per collection database. Seriously, RAM is your friend.
• Fine-Tune SQL Server: If more RAM doesn’t cut it, dive into these SQL Server tweaks:
  • Memory Settings: Explicitly set minimum and maximum memory settings, leaving at least 2 GB of free physical memory for the system. Don’t let SQL Server gobble it all up.
  • Locked Pages: Enable locked pages for the SQL Server service account. This prevents Windows from paging out the SQL Server’s memory, keeping things zippy.
  • Trace Flag 8032: Enable this trace flag. It tells SQL Server to be a bit smarter about how it caches query plans.

Work Item Tracking Specific Fix¶

• Compatibility Level: If work item tracking is your biggest headache, set your database compatibility level to 110. This forces SQL Server 2014 to use the older cardinality estimator, which is better at handling those pesky joins.

More Information: Digging Deeper¶

Here’s some extra reading for those who want to get their hands dirty:

• Trace Flag 8032: Learn more about this magic trace flag here: DBCC TRACEON - Trace Flags (Transact-SQL) (This link might refer to SQL Server 2012, but the information is still relevant for 2014).
• syncnamechanges Attribute: Want to know more about this attribute? Check out Enable Synchronization of Person-Name Custom Fields.
• Database Compatibility Level: Need help adjusting the compatibility level? View or Change the Compatibility Level of a Database will walk you through it.

Wrapping Up¶

So, there you have it! Don’t let a slow Team Foundation Server ruin your day. With a little tweaking, you can get things running smoothly again. If you’ve tried these tips or have other solutions, share them in the comments below! Let’s help each other out and conquer those performance woes. Come back anytime for more helpful tips and tricks!