loading...
Xpirit

Package Feeds consuming most data in Azure DevOps Server

jessehouwing profile image Jesse Houwing Originally published at jessehouwing.net on ・2 min read

The Collection database of a client saw some explosive growth and I was asked to figure out where the storage had gone to.

Package Feeds consuming most data in Azure DevOps Server

You'll find a few SQL statements floating around that can help you with these types of questions, but these don't list the Package Feed data usage. After some spelunking in the my local server installation I cobbled together the following statement to dig a little bit deeper:

select 
   [f].FeedName, 
   sum(cast([list].BlockFileLength as decimal(38)))/1024.0/1024.0 AS SizeInMb
from
   BlobStore.tbl_Blob [blob]
   join BlobStore.tbl_Block [block] on [blob].BlobId = [block].BlobId
   join BlobStore.tbl_BlockList [list] on [list].BlobId = [block].BlobId
   join [Feed].[tbl_PackageVersionIndex] [fd] on '0x'+[fd].StorageId = CONVERT(varchar(max),blob.BlobId ,1) 
   join [Feed].[tbl_Feed] [f] on [fd].FeedId = [f].FeedId
   join [Feed].[tbl_PackageIndex] [p] on [p].PackageId = [fd].PackageId
group by
  [f].FeedName
order by 
  SizeInMb desc

select 
   [f].FeedName, 
   [p].PackageName, 
   sum(cast([list].BlockFileLength as decimal(38)))/1024.0/1024.0 AS SizeInMb,
   count(distinct [pvi].PackageVersionId) as [Versions]
from
   BlobStore.tbl_Blob [blob]
   join BlobStore.tbl_Block [block] on [blob].BlobId = [block].BlobId
   join BlobStore.tbl_BlockList [list] on [list].BlobId = [block].BlobId
   join [Feed].[tbl_PackageVersionIndex] [fd] on '0x'+[fd].StorageId = CONVERT(varchar(max),blob.BlobId ,1) 
   join [Feed].[tbl_Feed] [f] on [fd].FeedId = [f].FeedId
   join [Feed].[tbl_PackageIndex] [p] on [p].PackageId = [fd].PackageId
   join [Feed].[tbl_PackageVersionIndex] [pvi] on [pvi].PackageId = [p].PackageId
group by
  [f].FeedName, [p].PackageName
order by 
  SizeInMb desc

The outcome is a list of feeds and their total consumption as well as a list of feeds decomposed by the different packages in that feed.

Package Feeds consuming most data in Azure DevOps Server
Data usage per feed and per package

From here further exploration should be a piece of cake!

Photo used under creative commons.

Posted on by:

jessehouwing profile

Jesse Houwing

@jessehouwing

I love good coffee, meaning strong, dark espresso! Regular Expressions are like word-puzzles to me, as are LINQ queries. I take pictures everywhere I go. Husband of 1, father of 2.

Xpirit

Xpirit helps you solve your IT problems. We act in multiple roles, from boardroom advisor, coding architect and lead developer to trainer and coach.

Discussion

markdown guide