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_BlockList [list] on [list].BlobId = [blob].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,
   (select count(pvi.PackageVersionId) from [Feed].[tbl_PackageVersionIndex] [pvi]
     where pvi.FeedId = f.FeedId and pvi.PackageId = p.PackageId) as Versions
from
   BlobStore.tbl_Blob [blob]
   join BlobStore.tbl_BlockList [list] on [list].BlobId = [blob].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, 
   [p].PackageName,
   f.FeedId,
   p.PackageId

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.

From here further exploration should be a piece of cake!

Photo used under creative commons.