Why would SqlBulkCopy be extremely slow sometimes?

171 Views Asked by At

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:

Results from RetrieveStatistics

0

There are 0 best solutions below