Why does TarWriter buffer SQL streams and consume excessive memory in C#?

I previously asked how to create a tar file in memory from several VARBINARY(MAX) columns, and that solution helped initially. However, I now need to generate very large tar files (up to ~20GB total with individual files around 2GB), and using MemoryStream isn't feasible on the server due to memory constraints.

I investigated this answer for large tar packing which suggests that writing to disk should avoid high memory usage. Based on that, I modified my approach to read varbinary columns from SQL Server, write them to a temporary file on disk, and then stream the file back to the client.

Below is my current code:

CSHARPCopied
var tempFileName = Path.GetTempFileName();
await using var tarFileStream = File.Create(tempFileName);

await using (var tarWriter = new TarWriter(tarFileStream, leaveOpen: true))
{
    await using var connection = new SqlConnection("YourConnectionString");
    const string query = @"
SELECT
  FileName,
  FileData
FROM FilesTable;
";
    await using var command = new SqlCommand(query, connection);
    await connection.OpenAsync();
    await using var reader = command.ExecuteReaderAsync(CommandBehavior.SequentialAccess);

    while (await reader.ReadAsync())
    {                            
        var fileName = reader.GetString(0);
        await using var fileDataStream = reader.GetStream(1);

        var entry = new PaxTarEntry(TarEntryType.RegularFile, fileName)
        {
            DataStream = fileDataStream,
        };
        await tarWriter.WriteEntryAsync(entry);
    }
}

tarFileStream.Position = 0;

// Stream tarFileStream to response body..

When running this code, during the while-loop I observed that about 8GB is allocated on the LOH for a couple of 1.7GB files fetched from SQL Server (FILESTREAM). DotMemory indicates that this memory is allocated in an underlying MemoryStream within TarWriter.

In contrast, when I read a file from disk the memory usage remains low:

CSHARPCopied
await using var tempFileStream = File.Create(Path.GetTempFileName());
await using (var tarWriter = new TarWriter(tempFileStream, leaveOpen: true))
{
    await using var file = File.Open("C:\\Users\\xyz\\Desktop\\BigFile.txt", FileMode.Open);
    await tarWriter.WriteEntryAsync(new PaxTarEntry(TarEntryType.RegularFile, Guid.NewGuid().ToString())
    {
        DataStream = file
    });
}

This suggests that the issue arises when setting the DataStream property with a stream from SqlClient. To further debug, I created a reproducible example by inserting and reading an 836MB text file into/from the database (without FILESTREAM) and writing the stream to TarWriter:

CSHARPCopied
using System.Data;
using System.Formats.Tar;
using Microsoft.Data.SqlClient;

const string connectionString = "Integrated Security=true;Data Source=localhost;Initial Catalog=MyTestDatabase;" +
                                "User Id=username;Password=xy;Max pool size=200;Min pool size=10;Connection Timeout=30;" +
                                "Encrypt=false";

await using var connection = new SqlConnection(connectionString);
await using var largeFile = File.Open(@"C:\\Users\\xyz\\Desktop\\BigFile.txt", FileMode.Open);

await using var createAndInsertCommand = new SqlCommand(
    """
    CREATE TABLE [dbo].[Temp] ([Id] INT NOT NULL, [Data] VARBINARY (MAX) NOT NULL);
    INSERT INTO dbo.Temp VALUES (@Id, @Data);
    """,
    connection);
createAndInsertCommand.Parameters.Add("@Id", SqlDbType.Int).Value = 1;
createAndInsertCommand.Parameters.Add("@Data", SqlDbType.VarBinary, size: -1).Value = largeFile;
await createAndInsertCommand.Connection.OpenAsync();
await createAndInsertCommand.ExecuteNonQueryAsync();

// Read from database
await using var command = new SqlCommand("SELECT TOP 1 Data FROM dbo.Temp", connection);
await using var reader = await command.ExecuteReaderAsync(CommandBehavior.SequentialAccess);

if (await reader.ReadAsync())
{
    await using var fileData = reader.GetStream(0);
    
    await using var tempFileStream = File.Create(Path.GetTempFileName());
    await using var tarWriter = new TarWriter(tempFileStream, leaveOpen: true, format: TarEntryFormat.Pax);


    tarWriter.WriteEntry(new PaxTarEntry(TarEntryType.RegularFile, Guid.NewGuid().ToString())
    {
        DataStream = fileData
    });
}

DotMemory snapshots confirm high memory usage when processing the SQL stream:

What might be causing this extensive memory allocation? Jon Skeets’ answer indicated that there should be a lower memory consumption. Am I overlooking something, or is there an alternative approach to avoid buffering large SQL streams in TarWriter?