SqlBulkCopy.WriteToServer executes automatically a Select * on the table

c# sqlbulkcopy


I have a C# 4.5 application, that receives records from the network and writes them into a Sql Server table.

I am using a SqlBulkCopy to fast insert a DataTable object into a SQL table.

This is the code I am using:

public bool BulkCopy(string tableName, DataTable dataToInsert)
        using (SqlBulkCopy tmpBulkCopy = new SqlBulkCopy(dbaseConnection))
           // Set the destination table.
           tmpBulkCopy.DestinationTableName = tableName;

           // Define column mappings 
           foreach (DataColumn dc in dataToInsert.Columns)
              tmpBulkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);

           // Perform the Bulk Copy
      catch (Exception)
         return false;

      return true;

Usually the DataTable object contains 1,000 records, while the SQL Table could have ~500M entries.

I was performing some debug, to identify possible bottlenecks in our application, and I have discovered that each time the WriteToServer is invoked, a Select * on the sql table is performed.

I am using this query to get the list of latest 50 queries run on the database:

(SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 ,
  ( (CASE WHEN statement_end_offset = -1
 ELSE statement_end_offset END)- statement_start_offset) / 2+1)) AS sql_statement,
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x
WHERE sql_statement NOT like 'SELECT TOP 50 * FROM(SELECT %'
--and OBJECTPROPERTYEX(x.objectid,'IsProcedure') = 1
ORDER BY last_execution_time DESC

Is this a normal behavior of SqlBulkCopy object? Due to the fact that the SQL Table is pretty big, my concerns are now about the impact of that Select query on the overall performances.

Accepted Answer

The select * is executed with SET FMTONLY ON. So no row is processed. This statement must be executed to return information about the destination table.

No rows are processed or sent to the client because of the request when SET FMTONLY is turned ON.

Ref: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-fmtonly-transact-sql

Here is an example:

select @@trancount; SET FMTONLY ON select * FROM tag SET FMTONLY OFF exec tempdb..sp_tablecollations_100 N'.[#ZZZProjects_f384e3cc_1428_459d_8853_a50ed818ccca]'

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why