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

2022-01-03 00:00:00 bulkinsert sql-server sql-server-2012 sqlbulkcopy sql-server-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.

推荐答案

Capturing event info for bulk insert operation (BCP.EXE, SqlBulkCopy，我假设 BULK INSERT 和 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:批量启动
SQL:批量完成
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):

文本数据
中央处理器
阅读
写
持续时间
SPID
开始时间
结束时间
行数

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:

SQL 事务

然后添加以下 Profiler 列:

and then add the following Profiler columns:

事件子类
对象名称

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 列中显示sort_init"的SQLTransaction"事件.此事件还有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:

交易日志

并添加以下 Profiler 列:

and add the following Profiler column:

对象 ID

感兴趣的主要信息将在 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 指出可以在 sys.trace_subclass_values 目录视图中找到 EventSubClass 值和名称，但查询该视图显示它没有 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 :(

<小时>

** 请注意，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.EXE 指定 -b 确实将 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.

相关文章