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.
- These queries use the operational store. Officially the schema of the operational store is not documented and subject to change at any time. You should interpret that to mean that it WILL change in the next version and that these queries won't work any more (but by then I'll have new ones :)). Many of these could be written against the warehouse (a documented, more schema stable store) but since not all of them could, I didn't use it for this).
- The files/folders query is not the one I used to use. It is only an approximation as the table it uses can build up some cruft in it over time. 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. 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. However, it's probably close enough for our purposes here. When I first ran it against our server it was only off by 10-20%.
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 copies
sp_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, Command
FROM tbl_Command with (nolock)
GROUP BY Application, Command ORDER BY sum(ExecutionTime) DESC
Brian
