Tips & Tricks

TFS 2010 Build Performance Report

For a customer I am analyzing the performance of the builds. They have Gated Check-in builds which take more than 30 minutes. The amount of Visual Studio Solutions built varies from 8 to 12. I need to know which part of the Build takes most time.Using the standard Build log in Visual Studio makes this task a hard one, having to scroll up and down, finding the parts of the process taking most time. Also the hierarchy in the log is hardly visible when scrolling through an endless list of build messages.

Visual Studio Build Report

To work my way around this, I came up with the idea to create a custom SQL Server Reporting Services report based on top of the data in the Collection database. Although I do not recommend to report based on the operational database of TFS, sometimes there is no other way. The build results are available in the reporting databases, but not at the level of detail I need, so I took a dive into the collection database. I also wanted the hierarchy which TFS creates in the build report to be collapsible/expandable for easy navigation and drilldown experience. The final result is downloadable at the bottom of this post.

First off, a datasource on the report server is needed pointing to the collection database. The name of the collection database depends on the name of the collection. If the name is “DefaultCollection” (which it is by default) then the name of the database is Tfs_DefaultCollection. In my case, the datasource uses the default Report Services user used by TFS;

Datasource

Then some simple queries were created to fill the parameters which would be the Build Definition to use and the actual Build to show;

List of all Build Definitions (named “BuildDefinitions”):

SELECT   DefinitionId, 
  REPLACE(REPLACE(DefinitionName, '>', '_'), '\', '') DefinitionName
FROM  tbl_BuildDefinition
ORDER BY DefinitionName

As you can see, TFS replaces the underscore in the Build (Definition) name with a bigger-than sign and all of them are ending with a backslash.

List of all Builds of the selected Build Definition (named “Builds”):

SELECT  BuildId,
  REPLACE(REPLACE(BuildNumber, '>', '_'), '\', '') BuildNumber
FROM  tbl_Build
WHERE  DefinitionId = @BuildDefinitionID
       AND Deleted = 0
ORDER BY FinishTime DESC

When the second dataset is added to the report, it automatically creates a parameter, whose settings need to be updated in order to show the list;

Parameter settings

Then the last and most important query must be written (named “BuildDetails”); the one retrieving the data based on the two parameters;

SELECT bi.NodeId, 
       bi.ParentId, 
       bity.TypeName, 
       bi.LastModifiedDate, 
       bi.LastModifiedBy, 
       dpt.FieldValue DisplayText, 
       CONVERT(DATETIME2, st.FieldValue) StartTime, 
       CONVERT(DATETIME2, ft.FieldValue) FinishTime,
       sp.FieldValue ServerPath
FROM tbl_BuildInformation bi
       INNER JOIN tbl_BuildInformationType bity on bi.NodeType = bity.NodeType
       LEFT OUTER JOIN (SELECT NodeId, 
                               FieldValue
                        FROM tbl_BuildInformationField
                        WHERE FieldName = 'DisplayText') dpt ON bi.NodeId = dpt.NodeId
       LEFT OUTER JOIN (SELECT NodeId, 
                               FieldValue
                        FROM tbl_BuildInformationField
                        WHERE FieldName = 'StartTime') st ON bi.NodeId = st.NodeId
       LEFT OUTER JOIN (SELECT NodeId,
                               FieldValue
                        FROM tbl_BuildInformationField
                        WHERE FieldName = 'FinishTime') ft ON bi.NodeId = ft.NodeId
       LEFT OUTER JOIN (SELECT NodeId, 
                               FieldValue
                        FROM tbl_BuildInformationField
                        WHERE FieldName = 'ServerPath') sp ON bi.NodeId = sp.NodeId
WHERE BuildId = @BuildID
      AND bity.TypeName NOT IN ('BuildMessage', 
                                'BuildWarning',
                                'BuildError', 
                                'ExternalLink', 
                                'ConfigurationSummary')
ORDER BY NodeId

Some explanation;

  • tbl_BuildInformation is a table with a relationship to itself in order to create a hierarchy of build log entries. It contains the same amount of rows as displayed in the Visual Studio Build Report (except for the first and last one which are metadata)
  • tbl_BuildInformationType contains the types of rows which can appear in the Build Log, e.g. ActivityTracking or BuildError
  • tbl_BuildInformationField contains the information fields per row in tbl_BuildInformation. The amount of fields varies per BuildInformationType

After saving this dataset, another parameter is created which should use the “Builds” dataset as the list source;

Parameter settings

Then it is time to create the report itself:

  • Insert a table on the report
  • Add the necessary fields
  • Edit the row group properties and add “NodeId” as the “group on” field
  • Choose the visibility tab of the row group properties and set the initial visibility to “Hide”. Also set the display to be toggle by the name of the first textbox of the detail row
  • Use a DateDiff to calculate the time between the start and finish date/time of the row

Then, upload the report using ReportBuilder, SQL Server Business Intelligence Studio, the web interface of SQL Server Reporting Services and set the DataSource correctly. This should result in a report like this:

Custom Build Report

Disclaimer:

  • The report which can be downloaded below is only designed for SQL Server 2008 or SQL Server 2008 R2 in combination with TFS 2010. Other combinations have not been tested. I am planning to publish a TFS 2012 version as well soon!
  • Of course much more is possible, by applying some gold-plating, but the essence is a working report giving essential information

The report can be downloaded by using this link: http://1drv.ms/1kBsI1m

Advertisements

2 thoughts on “TFS 2010 Build Performance Report

  1. This report is great! I need to do some quick analysis of our build servers and build agents and this will be extremely useful!

    Is it possible to add the actual agent that the build ran on to the report? I’ve looked in the TFS database, but I don’t see this information. I know it’s got to be there since it’s in the Build View Log.

    For example in the Build View Log, there’s an entry for “Run On Agent (reserved build agent BuildServer1 – Agent2). I’m looking to add this information to the report.

    Thanks!

    1. Hi Ray,
      Thanks for your comment. In order to retrieve the agent name, please consider the following query:

      SELECT *
      FROM tbl_BuildInformation bi
      INNER JOIN tbl_BuildInformationField bif ON bi.NodeId = bif.NodeId
      WHERE BuildId = 1
      AND bif.FieldName = 'ReservedAgentName'

      Don’t forget to replace the BuildId (1) with the Id you want to see information from.
      Remember that the structure of the operational database of TFS is not static; it might change when a new update or version of TFS is released and installed.

      Good luck!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s