Troubleshoot TFS SQL Server Slowdowns

By: Tim Smith   |   Updated: 2016-07-11   |   Comments   |   Related: More > Database Administration

Problem

We've had developers recently complain about slowed access, builds, and checks-ins to our team foundation server (TFS), which we use for managing our source control. Some developers have even experienced time-outs and we're looking to avoid disruptions, especially to our builds. The server has some SQL Server databases along with network applications. What are some best practices for managing the SQL Servers for TFS?

Solution

It can be easy to overlook the TFS SQL Server databases, as a slowdown may feel like a network issue instead of a database issue. However, we can optimize the TFS SQL Server databases to reduce performance problems or to prevent them entirely. Since different teams use TFS differently, we may not experience any issues in an environment where we only use TFS for code history, while we experience more issues in another environment where we use TFS for automated builds, continuous integration, and reporting, along with check-ins.

1. Document how you're using TFS. How are you using TFS? Are you only checking-in code? Are you automating builds? Are you creating reports? Has the use increased? Has anything changed? These questions may answer some of why performance has slowed. For an example, if your team just created an application that had never been checked in or built and suddenly did so, that increases the load. If a new team switches to TFS and checks-in their code and begins using it, that will also have an impact.

2. Obtain what's happening, preferably when many developers are using TFS. In the case of TFS, I want to know what carries the most costs to the servers that causes contention. The keys I pay attention to are frequency, read/write mix and wait types. In the latter case, be careful about the false-positive wait types (noise) versus the meaningful wait types. For a quick example, a ASYNC_NETWORK_IO indicates that the source of the problems are not related to SQL Server - once that has been corrected, do developers notice faster speeds? Depending on how someone uses TFS, the TFS warehouse portion may operate more like an OLTP engine; I've tended to fine-tune it much differently than I would a normal OLAP engine. I use the below query to get some of this information when evaluating what's currently on the server:

SELECT [session_id] AS SessionID
    , [text] AS QueryText
 , [blocking_session_id] AS BlockingID
 , [wait_resource] AS WaitResource
 , [wait_type] AS Wait
 , [last_wait_type] AS LastWait
 , [status] AS QueryStatus
 , ([reads] + [writes]) AS RWIO
 --, *
FROM sys.dm_exec_requests  
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) 

SQL Server also provides helpful standard reports for looking at historic information, if your server is not currently being monitored by any custom or third party tool.

3. Use statistics over indexing. One of the most reactive activities of DBAs is to add indexes to columns without them, leading to over-indexing. They also tend to rebuild indexes more than is needed. While TFS may have highly fragmented indexes from time to time, most of my successful tweaks have been on the statistics level. I look for the frequent and expensive queries and the objects involved, then update statistics frequently on those objects. Updating statistics and indexing increase costs either way; for OLTP purposes, I prefer the former because I've observed that in those environments it carries a stronger impact. In two different TFS environments, this was the 80-20 rule in action - this alone solved 80% of the problem. However, the objects differed based on the queries, so look for the queries that are running frequently, shifting sets of data, and using many resources (example: any operation with a write is one to consider because of how it affects the underlying numeric metadata of the data).

4. Optimize data and log file growth. Pre-build your log files when there's little use to avoid growth pauses and make sure that the settings for growth on data files are sufficient for the environment (and use instant file initialization). TFS heavily uses tempdb which you'll see when you monitor it, so make sure to scale out tempdb in the appraise manner for your server. Keeping tempdb on its own drive is a must and make sure to give it enough space while remaining on top of its growth. I've seen a lot of tempdb contention when looking at TFS, so these will reduce most issues with it.

5. Run CHECKDB daily. During maintenance windows, check integrity. I've seen corrupt databases cause slowdowns in general, even when the corruption didn't take the databases offline or cause absolute failure to the database (or even backups). We don't want to lose what we have stored in TFS, so we need to verify the integrity of our TFS databases. In addition, we should verify that our databases are backed up, and that the backups follow a passed integrity check - we don't want to be backing up corrupt databases.

6. Rebuild indexes compatible to their use. I user a higher fragmentation threshold for TFS - 50 over the standard 30 since I use statistics fine-tuning mentioned in point three. For TFS SQL Server databases, this is appropriate in most cases. If you are in an environment that operates by a standard schedule - for an example, you tend to do the most frequent check-ins and builds on Fridays, consider rebuilding indexes that matches this schedule, such as the evening before the day of frequent use.

7. Consider what else is running on the server. Consider that if we use TFS frequently during the day for builds, reports, continuous integration, etc., we might want to avoid using the server for anything else, especially if that also will be used during those hours. Mixing a server can be useful if the busy times of each don't overlap - for an example, a server that has frequent TFS use during the day, and one that does overnight monitoring.

Next Steps

• Verify what maintenance plans and (or) setup you have running on your TFS SQL Servers.
• Document how you're using TFS to help the DBA(s) identify what could be the issue, or to minimize troubleshooting time.
• Generally, a few queries are the culprit, so optimizing them will reduce most headaches.

About the author

Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips