TFS2010: SQL Queries for TFS Statistics

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. 

The queries

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.
USE Tfs_DefaultCollection

— 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)

— Files
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)

— Checkins
select max(ChangeSetId) AS [Checkins] from tbl_ChangeSet with (nolock)

— Shelvesets
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
— Workspaces
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.

Comments (9)
  1. Hkaur says:

    It’ll be great if there was a way to query long running queries for a chosen timeframe. That would be helpfull in performance fine tuning using the connection timeout parameter

  2. IC says:

    Any idea what the new table name is for counting files in TFS 2012 RC / RTM?

    tbl_VersionedItem doesn't seem to exist in my TFS 2012 RC.

  3. Any guidance on above queries chnages for TFS 2012 RTM?

  4. MB says:

    Any guidance on queries for TFS 2012 RTM? any specific change need to understand?

  5. kp says:

    love to see this for TFS 2012 as well.

  6. Shawn says:

    Does anyone know how to get a report on how many shelvesets each user has?

  7. Greg R says:

    Anyone know where this information would be stored in TFS2012 Update 1 in the TFSConfiguration database:

    — 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

    It appears that the schema has changed with TFS2012 and there is no longer a Tfs_Configuration].[dbo].tbl_security_identity_cache table.


  8. mask says:

    how to i determine how many users TFS currently has?

  9. Nicolas says:

    Greg R asked some time ago how to get the 2012 VC users. I do it like this, but I'm not sure if this is right. Would be cool to get some feedback!


    FROM [dbo].[tbl_VCIdentityMap] i WITH(NOLOCK)

    INNER JOIN [Tfs_Configuration].[dbo].[tbl_Identity] ic WITH(NOLOCK) ON i.[TeamFoundationId]=ic.[Id]

    WHERE ic.[IsGroup] = 0

Comments are closed.