Need to figure out how much FileTable is using up in space

Question

I need to figure out how many files have been inserted on a MS Sql Server FileTable along with their average size.

The FileTable is already quite large, it' housing around 27GBs of data.

This is the SQL i'm using, but it's extremely slow. It's been running for over an hour, and it still hasn't finish. Is there any way to speed up the query?

SELECT round(datalength([file_stream]) / 1048576.0, 2) As FileSizeMB, COUNT(*)
FROM [dbo].[Document] WITH(NOLOCK)
GROUP BY round(datalength([file_stream]) / 1048576.0, 2)
ORDER BY round(datalength([file_stream]) / 1048576.0, 2)

Answer 1

Not sure if this will work, I don't have FileTable implemented, but this will get size of tables.

SELECT  
s.Name AS SchemaName,
        t.NAME AS TableName,
        p.rows AS RowCounts,
        SUM(a.total_pages) * 8 AS TotalSpaceKB,
        ( SUM(a.total_pages) * 8 ) / 1024.0 AS TotalSpaceMB,
        (( SUM(a.total_pages) * 8 ) / 1024.0)/1024.0 AS TotalSpaceGB,
        SUM(a.used_pages) * 8 AS UsedSpaceKB,
        ( SUM(a.used_pages) * 8 ) / 1024.0 AS UsedSpaceMB,
        (( SUM(a.used_pages) * 8 ) / 1024.0) /1024.0 AS UsedSpaceGB,
        ( SUM(a.total_pages) - SUM(a.used_pages) ) * 8 AS UnusedSpaceKB,
        ( ( SUM(a.total_pages) - SUM(a.used_pages) ) * 8 ) / 1024.0 AS UnusedSpaceMB,
        (( ( SUM(a.total_pages) - SUM(a.used_pages) ) * 8 ) / 1024.0)/1024.0 AS UnusedSpaceGB,
       GROUPING(t.Name)
FROM    sys.tables t
        INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
        INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
        INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID
                                       AND i.index_id = p.index_id
        INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE   t.NAME NOT LIKE 'dt%'
        AND t.is_ms_shipped = 0
        AND i.OBJECT_ID > 255
GROUP BY s.Name,
         t.Name,
        p.Rows
       WITH ROLLUP
       ORDER BY s.Name,
        t.Name