I'm trying to diagnose a performance issue with SqlBulkCopy.
I have a point in my code where most of the time, one row is being inserted, but sometimes hundreds of rows can be inserted, most of the time it runs fine and completes in less than a second even for the large cases, but occasionally this code takes 10 seconds to insert a single row, and we suspect it's timing out on our test environment where the server is under heavy load (90%+ cpu usage) causing tests to randomly fail.
I can't imagine what would cause the bulk insert to take 10 sec to insert a single row, how should I approach to diagnose what's causing this?
Using bulk As New SqlBulkCopy(CType(Me._connection, SqlConnection), SqlBulkCopyOptions.Default, trans)
bulk.BulkCopyTimeout = Me.commandTimeout
For Each column As DataColumn In insertTable.Columns
bulk.ColumnMappings.Add(column.ColumnName, column.ColumnName)
Next
bulk.DestinationTableName = destinationTable
bulk.WriteToServer(insertTable)
Dim statistics = CType(_connection, SqlConnection).RetrieveStatistics()
End Using
These are the statistics I get from RetrieveStatistics:
