SqlBulkCopy executes metadata queries multiple times per operation despite providing column mappings

[paullpra]

SqlBulkCopy executes metadata queries multiple times per operation despite providing column mappings

Summary

SqlBulkCopy executes metadata queries 5 times for each bulk insert operation, even when column mappings and data table schema are explicitly provided. This causes significant performance degradation in high-frequency bulk insert scenarios.

Problem Description

When using SqlBulkCopy with explicit column mappings and a properly structured DataTable, the following metadata query is still executed 5 times per bulk insert operation:

select @@trancount; SET FMTONLY ON select * from [TableName] SET FMTONLY OFF exec ..sp_tablecollations_100 N'.[TableName]'

Expected Behavior: When metadata (column mappings, DataTable schema) is provided explicitly, these metadata discovery queries should be skipped.

Actual Behavior: Metadata queries are executed 5 times for every single bulk insert operation, regardless of provided metadata.

Performance Impact

• Each metadata query adds network round-trip latency
• In high-frequency scenarios (continuous bulk inserts), this creates significant overhead
• Database server experiences unnecessary query load

Code Example

using var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction);
bulkCopy.DestinationTableName = "Users";

// Explicit column mappings provided
bulkCopy.ColumnMappings.Add("FirstName", "FirstName");
bulkCopy.ColumnMappings.Add("LastName", "LastName");
bulkCopy.ColumnMappings.Add("Email", "Email");
bulkCopy.ColumnMappings.Add("Age", "Age");
// ... additional mappings for all columns

// DataTable with proper schema that matches target table
var dataTable = new DataTable();
dataTable.Columns.Add("FirstName", typeof(string));
dataTable.Columns.Add("LastName", typeof(string));
dataTable.Columns.Add("Email", typeof(string));
dataTable.Columns.Add("Age", typeof(int));
// ... additional columns match target table exactly

// Additional configuration attempts
bulkCopy.EnableStreaming = true;
bulkCopy.BatchSize = 1000;

await bulkCopy.WriteToServerAsync(dataTable, cancellationToken);
// ↑ This still triggers 5 metadata queries despite explicit mappings

Reproduction

A complete reproduction case is available at: https://github.com/paullpra/SqlBulkCopyMetaDataQueryIssue/tree/main/BulkInsertIssue

The sample application demonstrates:

• Explicit column mappings for all columns
• Proper DataTable schema configuration
• Extended event sessions showing repeated metadata queries

Environment

• .NET Version: net8.0
• Microsoft.Data.SqlClient version: 5.2.2

Attempted Workarounds

We have tried the following configurations without success in preventing the metadata queries:

1. Explicit Column Mappings: Added mappings for every single column
2. SqlBulkCopyOptions combinations:
   • SqlBulkCopyOptions.TableLock
   • SqlBulkCopyOptions.Default
   • SqlBulkCopyOptions.KeepIdentity
3. DataTable Schema: Pre-defined schema to exactly match target table structure
4. Streaming: EnableStreaming = true
5. Batch Configuration: Various batch sizes

None of these approaches prevent the metadata discovery queries from being executed repeatedly.

Expected Solution

1. Skip metadata queries when explicit column mappings are provided for all columns

Business Impact

This issue significantly affects applications that:

• Perform high-frequency bulk inserts (logging, telemetry, ETL)
• Process data in real-time with continuous inserts
• Require low-latency bulk operations
• Handle large volumes of data where every millisecond counts

Logs/Traces

When running SQL Profiler or Extended Events, the following pattern is observed for each WriteToServerAsync call:

This pattern repeats for every single bulk insert operation, even when performed in rapid succession on the same table.

[edwardneal]

I've worked with SqlBulkCopy recently, and wanted to go into a little more detail on this particular point:

Metadata queries are executed 5 times for every single bulk insert operation, regardless of provided metadata

That's not exactly what the XEvents trace is showing. If you check the line_number field, you'll see this increment in each record. The trace isn't showing you the same command being executed five times (with five roundtrips, etc.), it's showing you five SQL statements in the same command:

1. select @@trancount
2. SET FMTONLY ON
3. select * from [...]
4. SET FMTONLY OFF
5. exec ..sp_tablecollations_100 N'.[...]'

This is one command, executed in a single roundtrip to the server; a packet capture verifies as much. It has to run at least once for two reasons:

1. One of the purposes of the roundtrip is to get the Always Encrypted metadata for the destination table. Without that metadata, the client can't perform encrypted bulk copies.
2. Although SqlBulkCopy is able to discover that a mapping exists which covers every column in a source DataTable, it's got no way to discover that those mappings are sufficient to cover every column expecting a value in the destination SQL Server table. The only way to know whether they're sufficient is to get the destination's metadata.

[paullpra]

Hi @edwardneal,

Thank you for the clarification.

We have confirmed our analysis: there is indeed only one network roundtrip and the metadata command batch is executed only once per SqlBulkCopy.WriteToServer call. The previous confusion arose because the Extended Event log shows traces at the individual SQL statement level (5 statements), not the command batch level (1 command).

Performance Issue: Unnecessary Validation Overhead
Even with a single roundtrip, this repeated server-side metadata validation (select @@trancount; SET FMTONLY ON...) causes measurable performance degradation in high-frequency bulk insert scenarios.

Redundancy and Confusion: We provide explicit column mappings (via SqlBulkCopyColumnMapping) and data types (via IDataReader), but the metadata query is still fired for validation. This creates confusion: if we explicitly define the schema, why is the client-side provision not trusted, and how is it even being used if the server is queried every time? This makes our explicit mapping effort seem redundant.

Questions and Proposal
To achieve maximum throughput, we have two key requests:

1. Existing Alternatives: Are there any existing configuration settings, workarounds, or alternative methods within SqlClient that we can use today to completely bypass this mandatory metadata query batch?

2. Feature Proposal: If no such alternative exists, we strongly propose adding a new property to bypass this mandatory validation entirely for advanced users in controlled environments.

We suggest an opt-in property like:
bulkCopy.TrustColumnMappings = true; // or SqlBulkCopyMetadataValidation.None;

This feature would unlock significant performance gains for high-volume, low-latency bulk operations. We would appreciate your feedback on any existing alternatives or the feasibility of implementing this flag.