We have a sql FILESTREAM enabled table, and are wondering what the fastest way is to determine the size grouped by a common criteria, as well as the sum of all objects.
To date, we have been using queries like
select ClientID, sum(datalength(FilestreamColumn)) from dbo.tblFilestream group by ClientID; however, this takes a very long time to complete on a large dataset, and I imagine it is fairly hostile to the buffer cache. I can perform a directory listing, parse the entries in Excel, and get the results back faster than SQL does. However, that's because the dir command isn't reading the contents into memory.
We could add a new column to store this info and maintain it each time we perform an operation, but we'd prefer not to. That being said, we may have no other choice.
Regarding the 'all objects' part of the question - this question indicates that sys.database_files can be used, but it is always 0 for us, even in SQL 2008 R2 SP2.