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.
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;
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;
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;
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:
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://sdrv.ms/14qAFmS
