Brian has been posting TFS statistics from the Developer Division dogfood TFS server for many years. I’ve updated the queries from TFS2008 to work with Team Foundation Server 2010 Beta 2. Here are the statistics for the TFS2010 Beta 2 server that we have been using internally since August 2009.
- VSTS Pioneer TFS2010 Dogfood Server: Statistics August 2009
- VSTS Pioneer TFS2010 Dogfood Server: Statistics September 2009
- Other Pioneer TFS2010 blog posts
With the default permissions in SQL, mere mortals will not have enough access to run these queries. In fact, only TFS administrators should be able to even connect to SQL at all. To run these queries, you essentially need db_datareader rights on the Tfs_Configration and any Tfs_Collection* databases that you want statistics on.
In general, you do NOT want to query the TFS operational databases. The only supported API is the Microsoft.TeamFoundation.* Object Model. If you build applications that read the SQL store directly, you are likely to impact TFS performance and it is likely to break in the next version if the database schema changes. You’ve been warned! (Running these queries are OK periodically though :)
— Change this to the name of your collection DB. You’ll need to run these queries for each of your collection DBs.
— Recent Users
select count(distinct IdentityName) as [Recent Users] from tbl_Command with (nolock)
— Users with Assigned Work Items
select count(distinct [System.AssignedTo]) AS [Users with Assigned Work Items] from WorkItemsAreUsed with (nolock)
— Version Control Users
select COUNT(*) AS [Version Control Users] from [Tfs_Configuration].[dbo].tbl_security_identity_cache as ic JOIN tbl_Identity as i ON i.TeamFoundationId=ic.tf_id where ic.is_group = 0
— Total Work Items
select count(*) AS [Total Work Items] from WorkItemsAreUsed with (nolock)
— Areas and Iterations
select count(*) AS [Areas and Iterations] from tbl_nodes with (nolock)
— Work Item Versions
select count(*) AS [Work Item Versions] from (select [System.Id] from WorkItemsAreUsed with (nolock) union all select [System.Id] from WorkItemsWereUsed with (nolock)) x
— Work Item Attachments
select count(*) AS [Work Item Attachments] from WorkItemFiles with (nolock) where FldID = 50
— Work Item Queries
select count(*) AS [Work Item Queries] from QueryItems with (nolock)
select count(*) as [Files] from tbl_VersionedItem vi with (nolock) join tbl_Version v with (nolock) on v.ItemId = vi.ItemId where VersionTo = 2147483647
— Compressed File Sizes
select (sum(convert(bigint,OffsetTo – OffsetFrom + 1)) / (1024 * 1024)) AS [Compressed File Sizes] from tbl_Content with (nolock)
— Uncompressed File Sizes
select (sum(FileLength) / (1024 * 1024)) AS [Uncompressed File Sizes] from tbl_File with (nolock)
select max(ChangeSetId) AS [Checkins] from tbl_ChangeSet with (nolock)
select COUNT(*) AS [Shelvesets] from tbl_Workspace with (nolock) where type=’1′
— Merge History
select SUM(st.row_count) AS [Merge History] from sys.dm_db_partition_stats st WHERE object_name(object_id) = ‘tbl_MergeHistory’ AND (index_id < 2)
— Pending Changes
select count(*) AS [Pending Changes] from tbl_PendingChange pc with (nolock) join tbl_Workspace w with (nolock) on pc.WorkspaceId = w.WorkspaceId where w.Type = 0
select COUNT(*) AS [Workspaces] from tbl_Workspace with (nolock) where type=’0′
— Local Copies
select SUM(st.row_count) AS [Local Copies] from sys.dm_db_partition_stats st WHERE object_name(object_id) = ‘tbl_LocalVersion’ AND (index_id < 2)
— Command Counts
select Command, count(*) as [Execution Count] from tbl_Command with (nolock) WHERE Command IN (‘QueryWorkitems’, ‘Update’, ‘GetWorkItem’, ‘Get’, ‘VCDownloadHandler’, ‘Checkin’, ‘Upload’, ‘Shelve’) GROUP BY Command, Application ORDER BY [Application],[Command]
These are the core statistics to get you started. I need to add in some more on Team Build, Web Access, Excel & Project usage, etc. Let me know what other statistics you think would be useful.
If you’d like to share your statistics with me and/or the TFS team, send me a link or email them privately. We’d love to know what sort of usage patterns people are using the product in.
I’m also working on a plugin for the forthcoming TFS Best Practices Analyzer that will allow you to run these queries on a schedule and publish your own TFS statistics. By running it on a schedule, you will be able to track growth in size and usage over time and help you do some capacity planning. Stay tuned for that.