如何捕获使用Sql Profiler在SqlBulkCopy中传递的数据？ [英] How do I capture the data passed in SqlBulkCopy using the Sql Profiler?

问题描述

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

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

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

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.

推荐答案

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

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.

大容量插入操作显示为单个（好，每个批次一个，默认是在一个批次中完成所有行）DML语句：

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:

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

您可以在MSDN页面上找到 BCP实用工具。请注意，SqlBulkCopy仅支持这些提示的子集（例如 KEEP_NULLS ， TABLOCK 以及其他一些提示），但是支持不支持 ORDER（...）或 ROWS_PER_BATCH = ^** （实际上，这很不幸，因为需要 ORDER（）提示，以避免在tempdb中发生排序，以便允许该操作最少记录（假设满足此操作的其他条件）。

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).

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

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

SQL：BatchStarting

SQL：BatchCompleted

SQL：StmtStarting

SQL：StmtCompleted

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

您还将至少要选择以下列（在SQL Server Profiler中） ：

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

TextData

CPU

读取

写入

持续时间

SPID

StartTime

EndTime

RowCounts

TextData
CPU
Reads
Writes
Duration
SPID
StartTime
EndTime
RowCounts

而且，由于用户不能直接提交 INSERT BULK 语句，因此，如果您只想查看这些事件，则可以在列过滤器中进行过滤。

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.

如果您想查看 BULK INSERT 操作正在开始和/或结束的正式通知， ，那么您需要捕获以下事件：

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:

SQLTransaction

SQLTransaction

，然后添加以下探查器列：

and then add the following Profiler columns:

EventSubClass

ObjectName

EventSubClass
ObjectName

对于 ObjectName ，您将始终获得显示 BULK INSERT以及是否开始或结束的事件由 EventSubClass 中的值确定，该值为 0-开始或 1-提交（我想如果失败，您应该看到 2 -回滚。

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").

如果未指定 ORDER（）提示（再次提示使用 SqlBulkCopy 不能指定），那么您还会在 ObjectName 列。此事件还具有 0-开始和 1-提交事件（如 EventSubClass 列所示）。

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).

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

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:

TransactionLog

TransactionLog

并添加以下Profiler列：

and add the following Profiler column:

ObjectID

ObjectID

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

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.

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

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

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

---

^** 请不要e SqlBulkCopy.BatchSize 属性等同于为 BCP.EXE 设置 -b 选项。 strong>，这是一个可操作的设置，用于控制每个命令如何将行分成几组。这与 ROWS_PER_BATCH = 提示不同，后者并不物理控制行如何拆分为集合，而是允许SQL Server更好地计划它将如何分配页面。 ，因此减少了事务日志中的条目数（有时会减少很多）。我的测试仍然表明：

---

^** 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:

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


• 指定了 SqlBulkCopy。 BatchSize 属性没有设置 ROWS_PER_BATCH = 提示，但是减少事务日志活动的好处肯定存在（魔法？）。最终效果仍然是获得收益的事实，这就是为什么当我说 ORDER（）提示不是很不幸时我没有提到它的原因由 SqlBulkCopy 支持。

• specifying -b for BCP.EXE did set the ROWS_PER_BATCH= hint to that same value.
• 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.

这篇关于如何捕获使用Sql Profiler在SqlBulkCopy中传递的数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！