How do I capture the data passed in SqlBulkCopy using the Sql Profiler?

bulkinsert sqlbulkcopy sql-server sql-server-2012 sql-server-profiler

Question

I often use SQL Profiler to record SQL statements and replay troublesome ones. Very useful.

However, I have no clue how to capture code that utilizes the SqlBulkCopy API. Temp tables are being created, but nothing is populating them. It seems that SqlBulkCopy skips SQL Profiler or that I am not capturing the appropriate events.

1
9
4/23/2018 6:43:45 PM

Popular Answer

Event information capture for bulk insert procedures (BCP.EXE , SqlBulkCopy , and I supposeBULK INSERT , andOPENROWSET(BULK... (but you won't be able to view the specific rows and columns.)

Bulk Insert actions appear as one DML statement (well, one per batch, as the default is to process all rows in a single batch) of:

INSERT BULK <destination_table_name> (
      <column1_name> <column1_datatype> [ COLLATE <column1_collation> ], ...
      ) [ WITH (<1 or more hints>) ]

<hints> := KEEP_NULLS, TABLOCK, ORDER(...), ROWS_PER_BATCH=, etc

The MSDN page for the Utility BCP has a complete collection of "hints" that you may get there. Please be aware that just a portion of those tips (such asKEEP_NULLS , TABLOCK , among others), however not does not supportORDER(...) or ROWS_PER_BATCH= ** (this is really rather awful given theORDER() hint is required to prevent a sort from occurring in tempdb, enabling the operation to be minimally reported (assuming the other conditions for such an operation have also been satisfied).

You must record any of the following events in SQL Server Profiler in order to view this statement:

SQL:BatchStarting
SQL:BatchCompleted
SQL:StmtStarting
SQL:StmtCompleted

In SQL Server Profiler, you need additionally choose at least the following columns:

TextData
CPU
Reads
Writes
Duration
SPID
StartTime
EndTime
RowCounts

The fact that a user cannot submit anINSERT BULK If you just want to view these occurrences and nothing else, you can possibly filter on that via Column Filters.

If you wish to see the formal announcement that aBULK INSERT When an operation starts, stops, or changes, you must record the subsequent event:

SQLTransaction

then include the following Profiler columns:

EventSubClass
ObjectName

For ObjectName Events with "BULK INSERT" will always be shown to you; whether they are starting or finishing depends on the value inEventSubClass either "0 - Begin" or "1 - Commit," respectively (and I suppose if it fails you should see "2 - Rollback").

If theORDER() hint was not provided (again, it should be provided when usingSqlBulkCopy ), you will also see "sort init" in the "SQLTransaction" event.ObjectName column. There are additional "0 - Begin" and "1 - Commit" events for this event, as seen in theEventSubClass column).

In spite of not being able to access particular rows, you can still observe activities against the Transaction Log (such as insert row, alter IAM row, modify PFS row, etc.) if you record the following event:

TransactionLog

and include the next Profiler column:

ObjectID

The most relevant information will be in theEventSubClass Sadly, the column contains just ID values, and I was unable to locate any translation of those data in the MSDN documentation. However, I did come upon Jonathan Kehayias's blog article at In SQL Server Denali CTP1, the TransactionLog SQL Trace Event EventSubClass Values are mapped using Extended Events..

@RBarryYoung noted that the values and names for EventSubClass may be found in thesys.trace_subclass_values view of the catalog, however a query of that view reveals that there are no records for theTransactionLog event:

SELECT * FROM sys.trace_categories -- 12 = Transactions
SELECT * FROM sys.trace_events WHERE category_id = 12 -- 54 = TransactionLog
SELECT * FROM sys.trace_subclass_values WHERE trace_event_id = 54 -- nothing :(

** Remember that theSqlBulkCopy.BatchSize setting the property is equal to-b option for the operational setting BCP.EXE, which determines how each command will divide the rows into sets. The two are not equivalent.ROWS_PER_BATCH= a tip that does not physically alter how the rows are divided into sets but instead enables SQL Server to more effectively plan how it will allocate pages, reducing the volume of Transaction Log entries (sometimes by quite a bit). However, my research revealed that:

  • specifying -b because BCP.EXE set theROWS_PER_BATCH= suggest the same value.
  • describing theSqlBulkCopy.BatchSize not attribute set theROWS_PER_BATCH= clue, BUT the advantage of less Transaction Log activity was unquestionably there (magic?). When I remarked that it was terrible that the overall impact is to still profit, I did not mention it until I got closer to the top.ORDER() hint was not backed up bySqlBulkCopy .
11
2/22/2016 8:08:50 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow