Once of the things I’ve been looking into at work lately is the performance of the TFS server. The two questions that I’m trying to answer are:

• Is TFS slow today compared to yesterday or last week?
• Is the current hardware up to the job?

One of the great things that changed in TFS2008 is that the Web Service Method / Command Logging was enabled in the Global web.config File Settings by default. Every web service call that TFS processes is logged in the tbl_Command table of the TfsActivityLogging database on your data tier.

This table includes some useful fields like:

• Command - e.g. QueryLabel, CheckIn, etc.
• StartTime - The UTC time when TFS started to process the command
• ExecutionTime - The time it took to process the command (in millionths of a second)
• ExecutionCount - The number of times the command was executed within a logical operation
• UserAgent - e.g. DevEnv.exe, TFSBuildService.exe, etc.
• IPAddress - The IP Address of the client

By default, 14 days worth of activity logging data is kept. This is set in the SQL Agent job: TfsActivityLogging Administration Job which is scheduled to run at midnight each night:

• EXEC prc_PruneCommands @maxAgeDays = 14

Creating a PivotTable

By using Excel 2007 and connecting to the TfsActivityLogging table as a data source, we can apply some conditional formatting rules and get a fancy heat map.

About this particular Report:

1. You should read each line by itself. The colours are per-line instead of for the whole table.
2. As you can see performance has been a little worse since Friday. This is because we did a few fairly large operations which have fragmented the indexes.
3. The QueryLabels command is consistently taking a long time. I need to investigate what would cause this.

You will need to do this as a user that has access to query the TfsActityLogging database. By default a TFS Administrator has access.

• Open a new Excel 2007 worksheet
• Select Data | From Other Sources… | From SQL Server (shortcut: ALT-A-FO-Enter)

• On the Connect to Database Server screen, enter your TFS server name

• On the Select Database and Table screen, Select TfsActivityLogging and tbl_Command.

• Select the defaults to save the file and finish the wizard.
• On the Import Data dialog, select PivotTable Report

• You should now have a blank PivotTable Report that lets you select the different table fields.

Before we select fields to display, we want to specify our own SQL query to do some of the hard work.

• Select PivotTable Options | Change Data Source | Connection Properties… (shortcut: ALT-JT-I-O)
• Select the Definition tab
• Change the Command type to: SQL
• Paste in the following SQL query and replace the 4x “11″ with your GMT Timezone Offset (Australia is +11 hours in daylight saving)

SELECT 

    Command, 

    AVG(CAST(Duration AS float) / 1000000) as AvgDuration, 

    DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(hh, 11, StartTime))) AS Day 

FROM ( 

    SELECT 

        Command, 

       (SUM(ExecutionTime) / SUM(ExecutionCount)) AS Duration, 

       DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(hh, 11, StartTime))) AS StartTime 

    FROM 

       tbl_Command 

    GROUP BY 

       Command, 

       DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(hh, 11, StartTime))) 

) AS sub 

GROUP BY 

    Command, 

    DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(hh, 11, StartTime))) 

ORDER BY 

    Day DESC, 

    AvgDuration DESC

About the query:

1. Nested: The reason this is a nested query is because the TFS team included a column called ‘ExecutionCount’. If a command is executed multiple times as part of a single logical operation, the ExecutionCount will reflect this. For averages to work correctly, we can’t just ignore the ExecutionCount and we must SUM the values.
2. DATEADD() functions: The reason for these functions is twofold. 1) To GROUP BY each day we have to calculate the date (without the time). 2) Because TFS stores the StartTime as GMT we have to add our timezone offset to the time, otherwise it looks like the server was busy on the weekends.
3. Divide by 1000000: The reason for this is that Duration field is stored in millionths of a second. We divide it to get the result in seconds which is easier to relate to.

• Save the options, and add the fields so that your PivotTable Field List looks like this:

And that’s it! You now have all the data in Excel ready to analyse to your heart’s content.

Creating a Heat Map

To get a useful heat map, we need to do a few things to prepare the data:

• Select all the cells and reduce the number of digits shown after the decimal point. (shortcut: ALT-H-9)
• Select a cell in the right-most column labelled “Grand Total”
• Select the filter dropdown labelled “Column Labels” and untick dates that you don’t want included. e.g. Weekends, Today, etc.
• Select Sort & Filter | Sort Largest to Smallest (shortcut: ALT-H-S-O). This will sort the longest running commands to the top
• Select all rows except the top 10 and choose “Hide” (shortcut: ALT-H-O-U-R)
• Change the label “Grand Total” to “Average Duration”

Then you will need to select the values in each row (except the last column) and choose conditional formatting. (shortcut: ALT-H-L-S-Right Arrow-Enter)

• Save the workbook

Now whenever you want to view the latest performance metrics, you can select Refresh (shortcut: ALT-A-R-A) and Excel will execute the SQL query again and update the PivotTable.

Other Statistics and monitoring

If you’re interested in more general statistics about your server, these posts should help:

• Brian Harry - TFS 2008 Statistics - e.g. # of users, # of files / folders, Compressed / Uncompressed file sizes
• Brian Harry - TFS 2005 Statistics - same as above, but for TFS 2005
• James Manning - tail -f on the TFS Activity Log - watch activity in real-time on your server
• James Manning - Show currently executing TFS requests

Future Enhancements

Of course this is a pretty Ad-Hoc solution, but it requires no special tools. Some other implementations that might be useful are:

• Build it as a SQL Server Report that anybody can view
• Build it as a System Center Operations Manager 2007 (SCOM) Management Pack

If you find this useful please leave a comment and share any enhancements that you make with others.