Archive
TFS 2010 : Troubleshooting high processor usage by data tier
As the development teams grow, load on TFS also grows. Being a comprehensive ALM solution, it acts as single point of service for all stake holders in your organization. Just like heart TFS pumps in/out information into the veins of organization. So, data tier is most vulnerable to become a bottle neck in case load increases beyond limit.
One fine day in our organization we saw that SQL server started consuming >95% avg processor time. Which was started hitting our whole ALM implementation. Developers were not able to checkin, Builds were damn slow, reports were too slow to fetch data. We were totally clue less about what was happening there. We shot arrows in dark, in all directions. We were also looking into scaling up data tier at one point of time. But after two weeks of trouble shooting we were able to nail the issue, which was primarily due to one of our custom solution we build around TFS.
During this trouble shooting I came across lot of interesting ways to probe in to performance issue with TFS. Here are few very important i would like to share.
Monitoring TFS in coming requests
This can be an initial point one must look at as soon as performance issue strikes. Interestingly, TFS keeps a record of all in-coming requests. This can give a quick input in case issue is due to the increase in requests to TFS due to the increase in number of clients using tfs. But there is a job which runs fortnightly and deletes all request logs older than 14 days. So, that is why I said “as soon as you hit the performance issue”. TFS SQL database table “tbl_Command” keeps log of in-coming tfs requests. You can query it any time to check those interesting details.
SQL Server Performance Dash board
This is a life saver tool, I must say. It can answer all your questions on which query/Stored procedure is the main accuse of creating the performance issue with SQL server. This is from Microsoft. It installs a set of reports on database machine which run on database’s system tables or Database Management Views. Microsoft did not released this for SQL server 2005. So it will not work directly after installation. You have to do some steps to make it work for SQL Server 2008. Just refer to following url. Its easy.
Apart form these two methods we tried lot of other options, as I said “shot arrows in dark”. But, I am very sure that that methods mentioned above will give you enough inputs to resolve performance issue with TFS.
