2

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.

1

Don't know if its the fastest but I grouped the Filestream column into batches of 5000 rows then calculated each batch size before creating a running total:

CREATE PROCEDURE CalculateFSSize @RowCnt
AS

BEGIN

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'FSSizeBatch' AND TABLE_SCHEMA = 'dbo')
DROP TABLE dbo.FSSizeBatch

CREATE TABLE FSSizeBatch(BatchID INT
                                ,StartID INT
                                ,EndID INT
                                ,BatchCnt INT
                                ,BatchSize DECIMAL(18,4)
                                ,RunningTotal DECIMAL(18,4))

DECLARE @BatchID INT
    ,@StartID INT
    ,@EndID INT
    ,@BatchCnt INT

DECLARE cur_1 CURSOR FOR   

    SELECT ((Rank - 1) / @RowCnt) + 1 AS BatchID
            ,MIN(ID) AS StartID
            ,MAX(ID) AS EndID
            ,COUNT(*) AS BatchCnt
    FROM (
        SELECT RANK() OVER (ORDER BY ID) Rank
            ,ID
        FROM [dbo].[FS]
        ) T
    GROUP BY ((Rank - 1) / @RowCnt) + 1
    ORDER BY BatchID

OPEN cur_1  
FETCH NEXT FROM cur_1 INTO @BatchID, @StartID, @EndID, @BatchCnt  

WHILE @@FETCH_STATUS = 0  
BEGIN  

INSERT INTO FSSizeBatch(BatchID, StartID, EndID, BatchCnt, BatchSize)

SELECT @BatchID
        ,@StartID
        ,@EndID
        ,@BatchCnt
        ,SUM(CAST(DATALENGTH([JSONDATA]) + DATALENGTH([ZIPDATA]) AS DECIMAL)/1024/1014 )
FROM [dbo].[FS]
WHERE ID BETWEEN @StartID AND @EndID       

  FETCH NEXT FROM cur_1 INTO @BatchID, @StartID, @EndID, @BatchCnt   
END 

CLOSE cur_1  
DEALLOCATE cur_1  

;WITH a AS (
        SELECT BatchID, RunningTotal, SUM(BatchSize) OVER(ORDER BY BatchID ROWS UNBOUNDED PRECEDING) AS RowTotal
        FROM FSSizeBatch
        )
UPDATE a
SET RunningTotal = RowTotal

SELECT *
FROM FSSizeBatch

END

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.