How SQL Server stores files

What the SQL Server provider does with your files, and what that means for performance and the database's size.

How content is stored

Content is stored in chunks of up to 40,200 bytes. An upload stores its version staged, then inserts the chunks about a MiB at a time, each batch committing on its own, so no transaction is held while the content arrives. A staged version isn't visible to any read until all of its content is stored, and the name is locked only at the end, to make the new version current. See Uploads, connections and the log. A download reads a few chunks at a time as its stream is read, so memory use stays flat whatever the file size.

Performance

• Content takes about its own size in the database. SQL Server stores large values on 8 KB pages that hold 8,040 bytes each, and a chunk fills five of them exactly. Files stored before 4.0, in 64 KB chunks, take about 13% more space, since each of those chunks also needs a page of its own pointing to its content. They stay readable as they are; uploading one again stores it in the smaller chunks, while a copy keeps its source's chunks.
• Memory doesn't grow with file size. An upload or a download holds about 1 MiB of content at a time (27 chunks), in buffers it reuses, whatever the file's size; a download of a file stored before 4.0, in larger chunks, holds about 1.7 MiB. Eight uploads at once hold about 8 MiB of content between them.
• About one round trip per MiB. An upload inserts 27 chunks per round trip and a download reads 27 per query, plus a few round trips per operation to find the version, lock the name and make the version current. Reading a resource's attributes, listing a container and listing a name's versions are one query each.
• Copies and moves stay in the database. A copy is one INSERT … SELECT of the source's chunks, so none of the content goes through the application: copying 16 MiB takes about half the round trips of uploading it and a fraction of the memory. A move changes only the versions' container and name, whatever their size.
• A range costs what its own bytes cost. A range download finds its first chunk from the chunks' lengths, without reading content, and reads only the chunks the range covers, so the last MiB of a 64 MiB file costs about what a 1 MiB file does.
• Throughput is the server's. With the client and SQL Server on the same machine, uploads of large files run at roughly 60 to 120 MiB/s and downloads at 150 to 375 MiB/s. On a network, latency and bandwidth set the pace, and the round trips above are what latency multiplies.
• The transaction log takes about as much as the content. An upload writes about 1.09 times its size to the log, and deleting a version about 1.1 times its size, since SQL Server logs the content it frees. Size the log for the largest uploads and deletes you expect to run at once, and back it up regularly under the full recovery model.
• Concurrent writers don't wait for each other's content. Uploads to different names run fully in parallel, and uploads to the same name wait for each other only while making their versions current.

These figures come from the performance tests the repository runs on every change (see CONTRIBUTING.md), which fail the build when an operation gets more expensive. There's nothing to tune: the chunk and page sizes are fixed.