{"id":10621,"date":"2006-06-12T16:11:00","date_gmt":"2006-06-12T16:11:00","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/bharry\/2006\/06\/12\/tfs-statistics-queries\/"},"modified":"2018-08-14T00:35:04","modified_gmt":"2018-08-14T00:35:04","slug":"tfs-statistics-queries","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/bharry\/tfs-statistics-queries\/","title":{"rendered":"TFS statistics queries"},"content":{"rendered":"<p><font size=\"2\">\n<font face=\"Arial\">You know how it goes &#8211; somehow there&#8217;s just never enough days in the week to get everything done you want to get done.&nbsp; A couple of weeks ago I promised to post the queries I use to collecting statistics on our server &#8211; and other than feeling bad about it, I haven&#8217;t done much.<\/font>\n<font face=\"Arial\">I&#8217;m at Tech Ed this week and looking for any excuse to avoid prepping for my presentation on Wed.&nbsp; If you are at Tech Ed, stop by the VS TLC (Technical Learning Center) Tue morning and say hi.<\/font>\n<font face=\"Arial\">We&#8217;ll here they are&#8230; but first a few important caveats.<\/font><\/p>\n<ul>\n<li><font face=\"Arial\">These queries use the operational store.&nbsp; Officially the schema of the operational store is not documented and subject to change at any time.&nbsp; You should interpret that to mean that it WILL change in the next version and that these queries won&#8217;t work any more (but by then I&#8217;ll have new ones :)).&nbsp; Many of these could be written against the warehouse (a documented, more schema stable store) but since not all of them could, I didn&#8217;t use it for this).<\/font><\/li>\n<li><font face=\"Arial\">The files\/folders query is not the one I used to use.&nbsp; It is only an approximation as the table it uses can build up some cruft in it over time.&nbsp; I use this one now because it runs much faster than the precise one and as our file count got above 10,000,000 the old one was just too slow.&nbsp; In the future we will add scrubbing it to our nightly clean up task and it will be a more accurate reflection of the count.&nbsp; However, it&#8217;s probably close enough for our purposes here.&nbsp; When I first ran it against our server it was only off by 10-20%.<\/font><\/li>\n<\/ul>\n<p><font face=\"Arial\">Users with assigned work items:<br \/>select count(distinct [System.AssignedTo]) from WorkItemsAreUsed with (nolock)<\/font>\n<font face=\"Arial\"># of work items:<br \/>select count(*) from WorkItemsAreUsed with (nolock)<\/font>\n<font face=\"Arial\">Work Item versions:<br \/>select count(*) from (select [System.Id] from WorkItemsAreUsed with (nolock) union all select [System.Id] from WorkItemsWereUsed with (nolock)) x<\/font>\n<font face=\"Arial\">Attached files:<br \/>select count(*) from WorkItemFiles with (nolock) where FldID = 50<\/font>\n<font face=\"Arial\"># of queries:<br \/>select count(*) from StoredQueries with (nolock)<\/font>\n<font face=\"Arial\"># of version control users:<br \/>select count(*) from tbl_Identity with (nolock) where IsGroupIdentity = 0<\/font>\n<font face=\"Arial\"># of files(item type != 1)\/folders(item type == 1):<br \/>select ItemType, count(*) from tbl_VersionedItem group by ItemType<\/font>\n<font face=\"Arial\">Compresed file size:<br \/>select sum(convert(bigint,OffsetTo &#8211; OffsetFrom + 1)) from tbl_Content with (nolock)<\/font>\n<font face=\"Arial\">Uncompress file sizes:<br \/>select sum(FileLength) from tbl_File with (nolock)<\/font>\n<font face=\"Arial\">Total # of checkins:<br \/>select max(ChangeSetId) from tbl_ChangeSet with (nolock)<\/font>\n<font face=\"Arial\">Pending changes:<br \/>select count(*) from tbl_PendingChange pc with (nolock) join tbl_Workspace w with (nolock) on pc.WorkspaceId = w.WorkspaceId where w.Type = 0<\/font>\n<font face=\"Arial\">Workspaces(type != 1)\/Shelvesets(type == 1):<br \/>select type, count(*) from tbl_Workspace with (nolock) group by type<\/font>\n<font face=\"Arial\">Local copies<br \/>sp_spaceused tbl_LocalVersion<\/font>\n<font face=\"Arial\">Areas &amp; Iterations:<br \/>select count(*) from tbl_nodes with (nolock)<\/font>\n<font face=\"Arial\">Requests by user:<br \/>SELECT sum(ExecutionTime)\/1000 AS ExecutionTime, count(*) AS [Count], IdentityName AS [User]<br \/>FROM tbl_Command with (nolock)<br \/>GROUP BY IdentityName ORDER BY sum(ExecutionTime) DESC<\/font>\n<font face=\"Arial\">Requests by command:<br \/>SELECT sum(ExecutionTime)\/1000 AS ExecutionTime, count(*) AS [Count], Application, Command<br \/>FROM tbl_Command with (nolock)<br \/>GROUP BY Application, Command ORDER BY sum(ExecutionTime) DESC<br \/><\/font><font face=\"Arial\"><\/font>\n<font face=\"Arial\">Brian<\/font>\n<\/font><\/p>\n","protected":false},"excerpt":{"rendered":"<p>You know how it goes &#8211; somehow there&#8217;s just never enough days in the week to get everything done you want to get done.&nbsp; A couple of weeks ago I promised to post the queries I use to collecting statistics on our server &#8211; and other than feeling bad about it, I haven&#8217;t done much. [&hellip;]<\/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":[3],"class_list":["post-10621","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized","tag-tfs-dogfood-statistics"],"acf":[],"blog_post_summary":"<p>You know how it goes &#8211; somehow there&#8217;s just never enough days in the week to get everything done you want to get done.&nbsp; A couple of weeks ago I promised to post the queries I use to collecting statistics on our server &#8211; and other than feeling bad about it, I haven&#8217;t done much. [&hellip;]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/bharry\/wp-json\/wp\/v2\/posts\/10621","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=10621"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/bharry\/wp-json\/wp\/v2\/posts\/10621\/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=10621"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/bharry\/wp-json\/wp\/v2\/categories?post=10621"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/bharry\/wp-json\/wp\/v2\/tags?post=10621"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}