Export large SQL Server results without exhausting the client

A query that returns millions of rows is not finished from the user's perspective when SQL Server finds the first row. Data still has to cross the connection, be decoded, formatted, written, and delivered. A fast execution plan can coexist with a slow export and an application process that runs out of memory.

Measure the entire result path

Record time to first row, time to finish reading, time to finish writing, row count, output bytes, and peak application memory. These measurements distinguish server computation from transfer and formatting. A stopwatch around command creation or the first Read call does not measure the whole export.

During a slow run, observe the active request with the following read-only query using the appropriate server diagnostic permissions.

SELECT
    r.session_id, r.request_id,
    r.status, r.wait_type, r.wait_time,
    r.total_elapsed_time, r.cpu_time,
    r.logical_reads, r.reads, r.row_count,
    s.program_name, s.host_name
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
WHERE s.is_user_process = 1
  AND r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;

Repeated ASYNC_NETWORK_IO observations mean the server is waiting for result consumption or related network progress. They do not automatically identify a broken network. A client that compresses, logs, or makes an HTTP call after every row can consume too slowly even on a healthy connection. Compare client CPU, output-device throughput, and network evidence before choosing a remedy.

Reduce unnecessary data at the source. Select only required columns and push valid filters or aggregations into SQL. Exporting a large description column just to discard it later wastes transfer and decoding effort. Preserve the requested semantics; an export of detail cannot be replaced with totals simply because totals are smaller.

Bound memory without hiding the slow consumer

A forward-only reader can avoid building a collection containing the entire result. For large binary or text columns in SqlClient, SequentialAccess and the appropriate stream or text-reader APIs allow incremental consumption. Respect column-access order and finish consuming a streamed value before moving to later fields as required by that access pattern.

Asynchronous I/O does not itself bound memory. A loop that starts one task per row and stores every task or row can still grow with the dataset. Use a bounded channel or a limited batch between extraction and transformation, with a fixed worker count. When the downstream stage slows, the buffer should stop growing rather than exhausting the process.

That backpressure has a cost: the database reader and connection remain open while the consumer catches up. Depending on the query and isolation model, long execution can prolong locks, retain version history, or occupy a memory grant. Moving all data to memory releases the reader sooner but trades database occupancy for client memory pressure. Neither extreme is universally correct.

For slow end-user downloads, a background export job can write a temporary artifact to controlled storage and close the database reader before the user downloads it. Make the final artifact available only after successful completion, with recorded row count, size, and preferably a checksum. An interrupted temporary file should not be presented as a complete export.

Define consistency, cancellation, and completion

Decide what point in time the export represents. Multiple independent chunks under ordinary read committed isolation do not automatically form one consistent snapshot. Rows can change between chunks. A long snapshot transaction offers different semantics but can retain versions for its duration. Choose a documented consistency contract and measure its operational cost.

Use a stable ordering when the file contract requires deterministic output or resumable chunks. An ORDER BY can add server work, so include that work in testing. A high-water key alone does not prove that the selected rows' values remain unchanged throughout extraction.

Propagate cancellation and dispose of the reader, command, and owned connection on every exit path. If a transaction belongs to the job, end it explicitly. Test a consumer that disconnects halfway, a full output disk, a conversion failure after many rows, and a retry of the same export request.

Finally, distinguish job completion from successful download. The job may have produced a valid artifact even if the user's connection failed. Reusing that completed artifact can be safer and cheaper than rerunning the database query. A reliable large export has bounded resource use and an explicit completeness signal, not just a loop that eventually stops returning rows.

Technical references: Microsoft Learn: ASYNC_NETWORK_IO · Microsoft Learn: SqlClient streaming.