Dogfooding Team Foundation Build: By The Numbers

In the spirit of GrantH’s and BHarry’s posts outlining various dogfooding statistics it seems fitting to start my blog post series with some Team Foundation Build specific statistics. To put these numbers in perspective our first end-to-end build completed on July 21st 2010.

We currently have 38 controllers, 14 of these are owned by the build lab and are used for producing official nightly builds of Main, PU branches, and a couple of feature branches. Another 23 controllers are used by PU branches and feature branches for producing unofficial builds, including gated check-in queues. The remaining controller is a drop controller which manages a number of drop machines that have SAN disks attached to them where completed builds are dropped.

Official Build Controllers Official Drop Controllers Unofficial Controllers Total Controllers
14 1 23 38

Each official build typically consumes 4 build machines (for different architecture/flavor pairs) and 1 drop machine, unofficial builds typically consume 1 or 2 build machines (again for different architecture/flavor pairs).

Official Build Machines Official Drop Machines Unofficial Build Machines Total Build Agents
281 23 56 360

These build resources are consumed by 93 definitions, each branch typically has 2 or 3 build definitions associated with it (English, localized, code coverage, etc.). Since introducing Team Foundation Build we’ve launched nearly 5,000 builds producing over 12 million information nodes and over 42 million fields associated with them. These information nodes contain information such as log entries but we also leverage them to store strongly-typed information against builds which I’ll discuss in more detail in a future post.

Total Build Definitions Total Builds Total Build Information Nodes Total Build Information Node Fields
93 4,984 12,211,767 42,468,718

The largest Team Foundation Build tables in our database are:

Table Data Size Index Size
tbl_Build 12,152 KB 1,688 KB
tbl_BuildInformation 1,070,032 KB 1,054,168 KB
tbl_BuildInformationField 5,941,648 KB 75,736 KB

The query I used to produce these statistics is:

SELECT SUM(CASE WHEN BSH.DisplayName LIKE 'DDBLD%' THEN 1 ELSE 0 END) AS 'Official Build Controllers',

SUM(CASE WHEN BSH.DisplayName LIKE 'DDROP%' THEN 1 ELSE 0 END) AS 'Official Drop Controllers',

SUM(CASE WHEN BSH.DisplayName NOT LIKE 'DDBLD%' AND BSH.DisplayName NOT LIKE 'DDROP%' THEN 1 ELSE 0 END) AS 'Unofficial Controllers',

COUNT(*) AS 'Total Controllers'

FROM tbl_BuildController BC (NOLOCK)

INNER JOIN tbl_BuildServiceHost BSH (NOLOCK) ON BC.ServiceHostId = BSH.ServiceHostId

 

SELECT SUM(CASE WHEN BSH.DisplayName LIKE 'DDBLD%' THEN 1 ELSE 0 END) AS 'Official Build Machines',

SUM(CASE WHEN BSH.DisplayName LIKE 'DDROP%' THEN 1 ELSE 0 END) AS 'Official Drop Machines',

SUM(CASE WHEN BSH.DisplayName NOT LIKE 'DDBLD%' AND BSH.DisplayName NOT LIKE 'DDROP%' THEN 1 ELSE 0 END) AS 'Unofficial Build Machines',

COUNT(*) AS 'Total Build Agents'

FROM tbl_BuildAgent BA (NOLOCK)

INNER JOIN tbl_BuildServiceHost BSH (NOLOCK) ON BA.ServiceHostId = BSH.ServiceHostId

 

SELECT COUNT(*) AS 'Total Build Definitions'

FROM tbl_BuildDefinition BD (NOLOCK)

 

SELECT COUNT(*) AS 'Total Builds'

FROM tbl_Build B (NOLOCK)

 

SELECT COUNT(*) AS 'Total Build Information Nodes'

FROM tbl_BuildInformation BI (NOLOCK)

 

SELECT COUNT(*) AS 'Total Build Information Node Fields'

FROM tbl_BuildInformationField BIF (NOLOCK)

 

EXEC sp_spaceused 'tbl_Build'

EXEC sp_spaceused 'tbl_BuildInformation'

EXEC sp_spaceused 'tbl_BuildInformationField'