使用FS驱动空间计算FileStream数据列长度匹配

Question

恳请向我提供以下建议：

我是用以下数据类型(包括视图和用户模式表)计算LOB对象的总大小的过程。

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)

获得以下查询的输出后，我将使用DATALENGTH函数来获取每个列的大小。

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

注意:当我使用/ 1048576.0

时，输出将是兆字节

在所有这些之后，我存储文件流数据的总驱动器大小是1TB。但是，使用DATALENGTH函数计算每个LOB列的长度后，它与驱动器上当前使用的空间不匹配。

例如，

:

当前驱动器大小为1TB，其中951 GB为空闲空间0.99TB。这意味着文件流数据类型列使用了50 GB。但是，当我检查上面T中的所有列时，我无法得到精确的大小匹配使用的空间(50 GB)。甚至不是很大的尺寸。它们的使用率极低。

，除了上面提到的数据类型之外，还可以使用什么驱动器?

如何使用DATALENGTH函数计算文件流驱动器中与已使用空间匹配的列的大小。

Answer 1

文件流卷上的每个文件的空间以文件系统分配单元(块大小)的单位分配。当实际数据大小不是分配单元大小的确切倍数时，分配的空间被舍入到下一个分配单元大小倍数。

下面的PowerShell命令将显示服务器上每个卷的分配单元大小：

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

使用上述命令返回的块大小值，您可以使用如下查询确定为每个文件流文件分配的实际空间：

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

对于小文件和较大的分配单元大小，未使用的分配空间的数量将很大。例如，考虑到实际文件大小1K将需要64K与该AU大小，浪费63K的存储每个文件。