Everything you want to know about Visual Studio ALM and Farming
Brian Harry is a Microsoft Technical Fellow working as the Product Unit Manager for Team Foundation Server. Learn more about Brian.
More videos »
You know how it goes - somehow there's just never enough days in the week to get everything done you want to get done. A couple of weeks ago I promised to post the queries I use to collecting statistics on our server - and other than feeling bad about it, I haven't done much.
I'm at Tech Ed this week and looking for any excuse to avoid prepping for my presentation on Wed. If you are at Tech Ed, stop by the VS TLC (Technical Learning Center) Tue morning and say hi.
We'll here they are... but first a few important caveats.
Users with assigned work items:select count(distinct [System.AssignedTo]) from WorkItemsAreUsed with (nolock)
# of work items:select count(*) from WorkItemsAreUsed with (nolock)
Work Item versions:select count(*) from (select [System.Id] from WorkItemsAreUsed with (nolock) union all select [System.Id] from WorkItemsWereUsed with (nolock)) x
Attached files:select count(*) from WorkItemFiles with (nolock) where FldID = 50
# of queries:select count(*) from StoredQueries with (nolock)
# of version control users:select count(*) from tbl_Identity with (nolock) where IsGroupIdentity = 0
# of files(item type != 1)/folders(item type == 1):select ItemType, count(*) from tbl_VersionedItem group by ItemType
Compresed file size:select sum(convert(bigint,OffsetTo - OffsetFrom + 1)) from tbl_Content with (nolock)
Uncompress file sizes:select sum(FileLength) from tbl_File with (nolock)
Total # of checkins:select max(ChangeSetId) from tbl_ChangeSet with (nolock)
Pending changes:select count(*) from tbl_PendingChange pc with (nolock) join tbl_Workspace w with (nolock) on pc.WorkspaceId = w.WorkspaceId where w.Type = 0
Workspaces(type != 1)/Shelvesets(type == 1):select type, count(*) from tbl_Workspace with (nolock) group by type
Local copiessp_spaceused tbl_LocalVersion
Areas & Iterations:select count(*) from tbl_nodes with (nolock)
Requests by user:SELECT sum(ExecutionTime)/1000 AS ExecutionTime, count(*) AS [Count], IdentityName AS [User]FROM tbl_Command with (nolock)GROUP BY IdentityName ORDER BY sum(ExecutionTime) DESC
Requests by command:SELECT sum(ExecutionTime)/1000 AS ExecutionTime, count(*) AS [Count], Application, CommandFROM tbl_Command with (nolock)GROUP BY Application, Command ORDER BY sum(ExecutionTime) DESC