Sql-server – Calculating the FileStream Data Columns Length matching used FS Drive used space

blobfilestreamsql serversql server 2014

Kindly advice me on below :

I am process of calculating the total size of LOB objects with below data types (includes Views and user schema tables) .

select * from information_schema.columns where data_type in 
    ('TEXT', 'NTEXT','IMAGE' ,'XML', 'VARBINARY')
    or 
    (data_type = 'VARCHAR' and character_maximum_length = -1)
    OR
    (data_type = 'NVARCHAR' and character_maximum_length = -1)

Once I get the output of below query , I am using DATALENGTH function to get the size of each column .

SELECT SUM(DATALENGTH('ColumnName')) / 1048576.0 AS ColumnName  FROM DatabaseName.schema.Tablename

NOTE : The output will be Megabytes as I am using / 1048576.0

After all the above , my total drive size meant for storing File Stream Data is 1 TB . But post calculating the length of each LOB Column with DATALENGTH function , it does not match up to the current used space on Drive .

For example :

Current Drive size is 1 TB out of which 951 GB is free space 0.99 TB . which means 50 GB is used by File Stream data type columns . But when I check all columns from above T-SQL I could not get exact size matching used space (50 GB) . Not even close size . they show very minimal usage .

What else could be using the drive apart from the data types mentioned above ?

How to calculate the size of columns using DATALENGTH function which will match up to used space in File Stream Drive ..

Best Answer

Space for each file on the file stream volume is allocated in units of the file system allocation unit (blocksize). When the actual data size is not an exact multiple of the allocation unit size, allocated space is rounded up to the next allocation unit size multiple.

The PowerShell command below will show allocation unit size for each volume on the server:

Get-WmiObject `
    -Query "SELECT Name, Label, Blocksize FROM Win32_Volume WHERE FileSystem='NTFS'" `
    | Select-Object Name, Label, Blocksize;

Using the returned blocksize value from the above command, you can determine the actual space allocated for each file stream file with a query like:

DECLARE @AllocationUnitSize decimal(10,0) = 65536;
SELECT
      CEILING(DATALENGTH(file_stream)/@AllocationUnitSize)*@AllocationUnitSize AS AllocatedSpaceBytes
    , DATALENGTH(file_stream) AS UsedSpaceBytes
FROM dbo.YourFileStreamTable;

The amount of unused allocated space will be significant for small files and a large allocation unit size. For example, consider that an actual file size of 1K will require 64K with that AU size, wasting 63K of storage per file.

Related Solutions

Sql-server – should we keep the log file or the data file on the fastest drive

In my opinion putting the tempdb in the SSD drive without looking at the performance stats is the wrong way to go. SSD drives have high cost/GB and are a premium resource that should be used in the best possible way.

You will probably get better performance moving the most used database files to the SSD, given that you already have broken your database into multiple files and isolated the most problematic objects in their separate filegroup.

That said, SSDs are great for random reads and writes compared to traditional disks, which is the pattern you're seeing mostly in data files. Log files are more subject to sequential reads and writes, which mechanical disks perform just as well as SSDs.

It's true that tempdb is a critical resource for the instance and should be placed in a fast drive, however you're probably going to see a bigger performance gain if you added more RAM (much cheaper than SSDs).

You mileage may vary, but a generic question deserves a generic answer.

Sql-server – How to get the Total disk space in ms sql 2008 or below

Since you mentioned you are already using xp_fixeddrives,following code will give you total size. Many shops will not allow this for security reason. I copied the code from here.

There are few more version of this code and example of psinfo.

SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20) 
DECLARE @MB Numeric ; 
SET @MB = 1048576
CREATE TABLE #drives 
    (drive char(1) PRIMARY KEY, 
     FreeSpace int NULL,
     TotalSize int NULL) 

INSERT #drives(drive,FreeSpace) 

EXEC master.dbo.xp_fixeddrives 

EXEC @hr=sp_OACreate 'Scripting.FileSystemObject', @fso OUT 
IF @hr <> 0 
EXEC sp_OAGetErrorInfo @fso

DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives ORDER by drive

OPEN dcur FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive

IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso EXEC @hr =
sp_OAGetProperty
@odrive,'TotalSize', 
@TotalSize OUT IF @hr <> 0 

EXEC sp_OAGetErrorInfo @odrive 

UPDATE #drives SET TotalSize=@TotalSize/@MB 
WHERE  drive=@drive 
FETCH NEXT FROM dcur INTO @drive
End
Close dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

SELECT
 drive, 
 TotalSize as 'Total(MB)', 
 FreeSpace as 'Free(MB)' 
FROM #drives
ORDER BY drive 
DROP TABLE #drives 
GO

Best Answer

Related Solutions

Sql-server – should we keep the log file or the data file on the fastest drive

Sql-server – How to get the Total disk space in ms sql 2008 or below

Related Question