Team Foundation: What’s my server doing? (Part 2)


In my last post, I wrote about the mechanism within Team Foundation that records web method activity. You can mine the database to and learn much about how Team Foundation is being used. Recall that the web method information is written to a database so the full power of SQL queries is available.


List all Team Foundation activity


Combine the command and parameter table (the left join is necessary since some web method rows in the command table may not record parameter information — more on this later)



select *
from tbl_command c
left join tbl_parameter p
on c.commandid = p.commandid


List all Team Foundation Version Control activity


Same as the previous query with the addition of filtering on the Application column (command table).



select *
from tbl_command c
left join tbl_parameter p
on c.commandid = p.commandid
— Just Version Control (other choices: Data Warehouse, Integration, Work Item Tracking, Proxy)
where c.Application = ‘Version Control’


Show the web methods with the longest execution time


The ExecutionTime column contains the execution time, in microseconds, of the web method.



select *
from tbl_command c
left join tbl_parameter p
on c.commandid = p.commandid
order by c.ExecutionTime desc


Show the web methods called the most



select command, count (command) as TimesCalled
from tbl_command c
group by command
order by TimesCalled desc


Show the web method calls made by userN


The IdentityName column contains the Windows account name of the caller.



select *
from tbl_command c
left join tbl_parameter p
on c.commandid = p.commandid
— IdentityName contains the account name of the user (domain\account)
where c.IdentityName like ‘%userN%’


Show the web method calls made from Visual Studio


The UserAgent column contains the name of the executable from which the web method call originates.



select *
from tbl_command c
left join tbl_parameter p
on c.commandid = p.commandid
— UserAgent contains the executable name
where c.Useragent like ‘%devenv.exe%’


Other tidbits




  • Rows in the parameter table contain the commandId value of the corresponding row in the command table. Note that there may be multiple rows in the parameter table for a single command


  • Parameters are recorded when (1) An error occurs during web method execution (indicated by the Status column of the command table set to -1); (2) The web method took longer than 30 seconds to execute (recall that the ExecutionTime values are in microseconds) or (3) the Web method logging level is set to All in the web.config file.


Comments (4)

  1. We already saw how we could use the QueryServerRequests web method to tell the calls that are actively…

  2. Buck Hodges says:

    You may have read about the Team Foundation activity log, which is a database table containing the web…

  3. Buck Hodges says:

    I wrote posts when we hit 1,000,000 files (Nov. 18, 2005) and 10,000 changesets (Sept. 26, 2005) on the

  4. Here’s a list of how to enable logging for the various client and server components of VIsual Studio