如何使用Sql Profiler捕获在SqlBulkCopy中传递的数据？

I am using Sql Profiler all the time to capture the SQL statements and rerun problematic ones. Very useful.

我一直在使用Sql Profiler来捕获SQL语句并重新运行有问题的语句。很有用。

However, some code uses the SqlBulkCopy API and I have no idea how to capture those. I see creation of temp tables, but nothing that populates them. Seems like SqlBulkCopy bypasses Sql Profiler or I do not capture the right events.

但是,有些代码使用SqlBulkCopy API,我不知道如何捕获它们。我看到临时表的创建,但没有任何填充它们。看起来像SqlBulkCopy绕过Sql Profiler或者我没有捕获正确的事件。

2 个解决方案

#1

Capturing event info for bulk insert operations ( BCP.EXE, SqlBulkCopy, and I assume BULK INSERT, and OPENROWSET(BULK... ) is possible, but you won't be able to see the individual rows and columns.

捕获批量插入操作的事件信息(BCP.EXE,SqlBulkCopy,我假设BULK INSERT和OPENROWSET(BULK ...)是可能的,但您将无法看到各个行和列。

Bulk Insert operations show up as a single (well, one per batch, and the default is to do all rows in a single batch) DML statement of:

批量插入操作显示为单个(好,每个批次一个,默认是在一个批次中执行所有行)DML语句:

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

You can find the full list of "hints" on the MSDN page for the BCP Utility. Please note that SqlBulkCopy only supports a subset of those hints (e.g. KEEP_NULLS, TABLOCK, and a few others) but does not support ORDER(...) or ROWS_PER_BATCH= ^** (which is quite unfortunate, actually, as the ORDER() hint is needed in order to avoid a sort that happens in tempdb in order to allow the operation to be minimally logged (assuming the other conditions for such an operation have also been satisfied).

您可以在MSDN页面上找到BCP实用程序的完整“提示”列表。请注意,使用SqlBulkCopy只支持那些提示(如KEEP_NULLS,TABLOCK,和其他几个人)的一个子集,但不支持ORDER(...)或ROWS_PER_BATCH = **(这是很不幸的,实际上,作为ORDER()需要提示以避免在tempdb中发生的排序,以便允许最小程度地记录操作(假设已经满足此类操作的其他条件)。

In order to see this statement, you need to capture any of the following events in SQL Server Profiler:

要查看此语句,您需要捕获SQL Server Profiler中的以下任何事件:

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

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

You will also want to select, at least, the following columns (in SQL Server Profiler):

您还需要至少选择以下列(在SQL Server Profiler中):

TextData
CPU
Reads
Writes
Duration
SPID
StartTime
EndTime
RowCounts

TextData CPU读取写入持续时间SPID StartTime EndTime RowCounts

And, since a user cannot submit an INSERT BULK statement directly, you can probably filter on that in Column Filters if you merely want to see these events and nothing else.

并且,由于用户无法直接提交INSERT BULK语句,如果您只想查看这些事件而不是其他内容,则可以在列过滤器中对其进行过滤。

If you want to see the official notification that a BULK INSERT operation is beginning and/or ending, then you need to capture the following event:

如果要查看BULK INSERT操作开始和/或结束的正式通知,则需要捕获以下事件:

SQLTransaction

and then add the following Profiler columns:

然后添加以下Profiler列:

EventSubClass
ObjectName

For ObjectName you will always get events showing "BULK INSERT" and whether that is beginning or ending is determined by the value in EventSubClass, which is either "0 - Begin" or "1 - Commit" (and I suppose if it fails you should see "2 - Rollback").

对于ObjectName,您将始终获得显示“BULK INSERT”的事件,以及它是开始还是结束是由EventSubClass中的值确定的,该值是“0 - 开始”或“1 - 提交”(我想如果它失败了你应该见“2 - 回滚”)。

If the ORDER() hint was not specified (and again, it cannot be specified when using SqlBulkCopy), then you will also get a "SQLTransaction" event showing "sort_init" in the ObjectName column. This event also has "0 - Begin" and "1 - Commit" events (as shown in the EventSubClass column).

如果未指定ORDER()提示(并且再次使用SqlBulkCopy时无法指定),那么您还将获得在ObjectName列中显示“sort_init”的“SQLTransaction”事件。此事件还具有“0 - 开始”和“1 - 提交”事件(如EventSubClass列中所示)。

Finally, even though you cannot see the specific rows, you can still see operations against the Transaction Log (e.g. insert row, modify IAM row, modify PFS row, etc) if you capture the following event:

最后,即使您看不到特定的行,如果捕获以下事件,仍然可以看到针对事务日志的操作(例如,插入行,修改IAM行,修改PFS行等):

TransactionLog

and add the following Profiler column:

并添加以下Profiler列:

ObjectID

The main info of interest will be in the EventSubClass column, but unfortunately it is just ID values and I could not find any translation of those values in MSDN documentation. However, I did find the following blog post by Jonathan Kehayias: Using Extended Events in SQL Server Denali CTP1 to Map out the TransactionLog SQL Trace Event EventSubClass Values.

感兴趣的主要信息将在EventSubClass列中,但不幸的是它只是ID值,我在MSDN文档中找不到这些值的任何转换。但是,我确实找到了Jonathan Kehayias的以下博客文章:在SQL Server Denali CTP1中使用扩展事件来映射TransactionLog SQL跟踪事件EventSubClass值。

@RBarryYoung pointed out that EventSubClass values and names can be found in the sys.trace_subclass_values catalog view, but querying that view shows that it has no rows for the TransactionLog event:

@RBarryYoung指出可以在sys.trace_subclass_values目录视图中找到EventSubClass值和名称,但查询该视图显示它没有TransactionLog事件的行:

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 :(

^** Please note that the SqlBulkCopy.BatchSize property is equivalent to setting the -b option for BCP.EXE, which is an operational setting that controls how each command will break up the rows into sets. This is not the same as the ROWS_PER_BATCH= hint which does not physically control how the rows are broken up into sets, but instead allows SQL Server to better plan how it will allocate pages, and hence reduces the number of entries in the Transaction Log (sometimes by quite a bit). Still my testing showed that:

**请注意,SqlBulkCopy.BatchSize属性等同于为BCP.EXE设置-b选项,这是一个操作设置,用于控制每个命令如何将行分解为集合。这与ROWS_PER_BATCH =提示不同,ROWS_PER_BATCH =提示不会物理控制行如何分解成集,而是允许SQL Server更好地规划它将如何分配页面,从而减少事务日志中的条目数(有时相当多)。我的测试仍显示:

specifying -b for BCP.EXE did set the ROWS_PER_BATCH= hint to that same value.

为BCP.EXE指定-b确实将ROWS_PER_BATCH =提示设置为相同的值。

specifying the SqlBulkCopy.BatchSize property did not set the ROWS_PER_BATCH= hint, BUT, the benefit of reduced Transaction Log activity was somehow definitely there (magic?). The fact that the net effect is to still gain the benefit is why I did not mention it towards the top when I said that it was unfortunate that the ORDER() hint was not supported by SqlBulkCopy.

指定SqlBulkCopy.BatchSize属性没有设置ROWS_PER_BATCH =提示,但是,减少事务日志活动的好处在某种程度上确实存在(魔术?)。事实上,净效应仍然可以获得好处,这就是为什么当我说不幸的是SqlBulkCopy不支持ORDER()提示时,我没有提到它。

#2

You cann't capture SqlBulkCopy in SQL Profiler because SqlBulkCopy doesn't generate SQL at all when inserts data in SQL Server table. SqlBulkCopy works similar to bcp utility and loads data directly into SQL Server file system. It's even can ignore FKs and triggers when inserts the rows!

您无法在SQL事件探查器中捕获SqlBulkCopy,因为在SQL Server表中插入数据时,SqlBulkCopy根本不会生成SQL。 SqlBulkCopy的工作方式类似于bcp实用程序,并将数据直接加载到SQL Server文件系统中。插入行时甚至可以忽略FK和触发器!

#1