CODEWITHSUNDEEP
sql-server-2012filetable
bastos.sergio
bastos.sergio

Reputation: 6764

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

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)

Upvotes: 0

Views: 155

Answers (1)

Dbloch
Dbloch

Reputation: 2366

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

Upvotes: 1

Related Questions