{"id":8311,"date":"2007-12-02T12:39:00","date_gmt":"2007-12-02T12:39:00","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/bharry\/2007\/12\/02\/tfs-statistics-update\/"},"modified":"2018-08-14T00:27:42","modified_gmt":"2018-08-14T00:27:42","slug":"tfs-statistics-update","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/bharry\/tfs-statistics-update\/","title":{"rendered":"TFS Statistics update"},"content":{"rendered":"

Long ago I published a post<\/a> on some of the queries I use to monitor data on our internal servers.  Someone asked me to update it for TFS 2008 and I went back and tried them.  Only a few needed tweaks.  Here’s an update on the queries.<\/p>\n

Here they are…<\/p>\n

<\/p>\n

use<\/p>\n

<\/font> TfsWorkItemTracking<\/font> <\/p>\n

— Users with assigned work items:<\/p>\n

<\/font> <\/p>\n

select<\/p>\n

<\/font> <\/font>count<\/font>(<\/font>distinct<\/font> [System.AssignedTo]<\/font>)<\/font> <\/font>from<\/font> WorkItemsAreUsed <\/font>with<\/font> <\/font>(<\/font>nolock<\/font>)<\/font> <\/p>\n

— # of work items:<\/p>\n

<\/font> <\/p>\n

select<\/p>\n

<\/font> <\/font>count<\/font>(*)<\/font> <\/font>from<\/font> WorkItemsAreUsed <\/font>with<\/font> <\/font>(<\/font>nolock<\/font>)<\/font> <\/p>\n

— Work Item versions:<\/p>\n

<\/font> <\/p>\n

select<\/p>\n

<\/font> <\/font>count<\/font>(*)<\/font> <\/font>from<\/font> <\/font>(<\/font>select<\/font> [System.Id] <\/font>from<\/font> WorkItemsAreUsed <\/font>with<\/font> <\/font>(<\/font>nolock<\/font>)<\/font> <\/font>union all<\/font> <\/font>select<\/font> [System.Id] <\/font>from<\/font> WorkItemsWereUsed <\/font>with<\/font> <\/font>(<\/font>nolock<\/font>))<\/font> x<\/font> <\/p>\n

— Attached files:<\/p>\n

<\/font> <\/p>\n

select<\/p>\n

<\/font> <\/font>count<\/font>(*)<\/font> <\/font>from<\/font> WorkItemFiles <\/font>with<\/font> <\/font>(<\/font>nolock<\/font>)<\/font> <\/font>where<\/font> FldID <\/font>=<\/font> 50<\/font> <\/p>\n

— # of queries:<\/p>\n

<\/font> <\/p>\n

select<\/p>\n

<\/font> <\/font>count<\/font>(*)<\/font> <\/font>from<\/font> StoredQueries <\/font>with<\/font> <\/font>(<\/font>nolock<\/font>)<\/font> <\/p>\n

use<\/p>\n

<\/font> TfsVersionControl<\/font> <\/p>\n

— # of version control users:<\/p>\n

<\/font> <\/p>\n

select<\/p>\n

<\/font> <\/font>count<\/font>(*)<\/font> <\/font>from<\/font> tbl_Identity <\/font>with<\/font> <\/font>(<\/font>nolock<\/font>)<\/font> <\/font>where<\/font> IsGroupIdentity <\/font>=<\/font> 0<\/font> <\/p>\n

— # of files(item type != 1)\/folders(item type == 1):<\/p>\n

<\/font> <\/p>\n

select<\/p>\n

<\/font> ItemType<\/font>,<\/font> <\/font>count<\/font>(*)<\/font> <\/font>from<\/font> tbl_VersionedItem <\/font>group<\/font> <\/font>by<\/font> ItemType<\/font> <\/p>\n

— Compresed file size:<\/p>\n

<\/font> <\/p>\n

select<\/p>\n

<\/font> <\/font>sum<\/font>(<\/font>convert<\/font>(<\/font>bigint<\/font>,<\/font>OffsetTo <\/font>–<\/font> OffsetFrom <\/font>+<\/font> 1<\/font>))<\/font> <\/font>from<\/font> tbl_Content <\/font>with<\/font> <\/font>(<\/font>nolock<\/font>)<\/font> <\/p>\n

— Uncompressed file sizes:<\/p>\n

<\/font> <\/p>\n

select<\/p>\n

<\/font> <\/font>sum<\/font>(<\/font>FileLength<\/font>)<\/font> <\/font>from<\/font> tbl_File <\/font>with<\/font> <\/font>(<\/font>nolock<\/font>)<\/font> <\/p>\n

— Total # of checkins:<\/p>\n

<\/font> <\/p>\n

select<\/p>\n

<\/font> <\/font>max<\/font>(<\/font>ChangeSetId<\/font>)<\/font> <\/font>from<\/font> tbl_ChangeSet <\/font>with<\/font> <\/font>(<\/font>nolock<\/font>)<\/font> <\/p>\n

— Pending changes:<\/p>\n

<\/font> <\/p>\n

select<\/p>\n

<\/font> <\/font>count<\/font>(*)<\/font> <\/font>from<\/font> tbl_PendingChange pc <\/font>with<\/font> <\/font>(<\/font>nolock<\/font>)<\/font> <\/font>join<\/font> tbl_Workspace w <\/font>with<\/font> <\/font>(<\/font>nolock<\/font>)<\/font> <\/font>on<\/font> pc<\/font>.<\/font>WorkspaceId <\/font>=<\/font> w<\/font>.<\/font>WorkspaceId <\/font>where<\/font> w<\/font>.<\/font>Type <\/font>=<\/font> 0<\/font> <\/p>\n

— Workspaces(type != 1)\/Shelvesets(type == 1):<\/p>\n

<\/font> <\/p>\n

select<\/p>\n

<\/font> <\/font>type<\/font>,<\/font> <\/font>count<\/font>(*)<\/font> <\/font>from<\/font> tbl_Workspace <\/font>with<\/font> <\/font>(<\/font>nolock<\/font>)<\/font> <\/font>group<\/font> <\/font>by<\/font> <\/font>type<\/font> <\/p>\n

— Local copies<\/p>\n

<\/font> <\/p>\n

sp_spaceused<\/p>\n

<\/font> tbl_LocalVersion<\/font> <\/p>\n

use<\/p>\n

<\/font> TfsIntegration<\/font> <\/p>\n

— Areas & Iterations:<\/p>\n

<\/font> <\/p>\n

select<\/p>\n

<\/font> <\/font>count<\/font>(*)<\/font> <\/font>from<\/font> tbl_nodes <\/font>with<\/font> <\/font>(<\/font>nolock<\/font>)<\/font> <\/p>\n

use<\/p>\n

<\/font> TfsActivityLogging<\/font> <\/p>\n

— Requests by user:<\/p>\n

<\/font> <\/p>\n

SELECT<\/p>\n

<\/font> <\/font>sum<\/font>(<\/font>ExecutionTime<\/font>)\/<\/font>1000 <\/font>AS<\/font> ExecutionTime<\/font>,<\/font> <\/font>sum<\/font>(<\/font>ExecutionCount<\/font>)<\/font> <\/font>AS<\/font> [Count]<\/font>,<\/font> IdentityName <\/font>AS<\/font> [User]<\/font> <\/p>\n

FROM<\/p>\n

<\/font> tbl_Command <\/font>with<\/font> <\/font>(<\/font>nolock<\/font>)<\/font> <\/p>\n

GROUP<\/p>\n

<\/font> <\/font>BY<\/font> IdentityName <\/font>ORDER<\/font> <\/font>BY<\/font> <\/font>sum<\/font>(<\/font>ExecutionTime<\/font>)<\/font> <\/font>DESC<\/font> <\/p>\n

— Requests by command:<\/p>\n

<\/font> <\/p>\n

SELECT<\/p>\n

<\/font> <\/font>sum<\/font>(<\/font>ExecutionTime<\/font>)\/<\/font>1000 <\/font>AS<\/font> ExecutionTime<\/font>,<\/font> <\/font>sum<\/font>(<\/font>ExecutionCount<\/font>)<\/font> <\/font>AS<\/font> [Count]<\/font>,<\/font> <\/font>Application<\/font>,<\/font> Command<\/font> <\/p>\n

FROM<\/p>\n

<\/font> tbl_Command <\/font>with<\/font> <\/font>(<\/font>nolock<\/font>)<\/font> <\/p>\n

GROUP<\/p>\n

<\/font> <\/font>BY<\/font> <\/font>Application<\/font>,<\/font> Command <\/font>ORDER<\/font> <\/font>BY<\/font> <\/font>sum<\/font>(<\/font>ExecutionTime<\/font>)<\/font> <\/font>DESC<\/font> <\/p>\n

And yes, I realize I still owe you the TFSServerManager tool that does all of this for you.  And yes, I know it’s inexusable that it’s taken so long.  I feel terrible about it and I could waste your time with all the reasons I haven’t published it yet.  I’m working on it today.  I’m not making any promises about when – I’ve broken too many already.<\/p>\n

 Brian<\/p>\n

<\/font><\/p>\n","protected":false},"excerpt":{"rendered":"

Long ago I published a post on some of the queries I use to monitor data on our internal servers.  Someone asked me to update it for TFS 2008 and I went back and tried them.  Only a few needed tweaks.  Here’s an update on the queries. Here they are… use TfsWorkItemTracking — Users with […]<\/p>\n","protected":false},"author":244,"featured_media":14617,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[5],"class_list":["post-8311","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized","tag-tfs"],"acf":[],"blog_post_summary":"

Long ago I published a post on some of the queries I use to monitor data on our internal servers.  Someone asked me to update it for TFS 2008 and I went back and tried them.  Only a few needed tweaks.  Here’s an update on the queries. Here they are… use TfsWorkItemTracking — Users with […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/bharry\/wp-json\/wp\/v2\/posts\/8311","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/bharry\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/bharry\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/bharry\/wp-json\/wp\/v2\/users\/244"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/bharry\/wp-json\/wp\/v2\/comments?post=8311"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/bharry\/wp-json\/wp\/v2\/posts\/8311\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/bharry\/wp-json\/wp\/v2\/media\/14617"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/bharry\/wp-json\/wp\/v2\/media?parent=8311"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/bharry\/wp-json\/wp\/v2\/categories?post=8311"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/bharry\/wp-json\/wp\/v2\/tags?post=8311"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}