sp_showprogress

语法

sp_showprogress <spid>[, <display_option>]

参数

<spid>

正在运行的 update statistics 命令的进程 ID。

<display_option>

确定 sp_showprogress 显示的信息量。下列项之一：

• short –（缺省值）显示正在运行的 update statistics 命令的常规信息。

• long – 显示正在运行的 update statistics 命令的详细信息。

  示例

  示例 1

  显示当前在 spid 15 上运行的 update statistics 命令的详细信息：

  
  sp_showprogress 15, 'long'
  
  Session 15 is running:          UPDATE STATISTCIS
  Table name:                     BW.SAPSR3.E_10
  Starting time:                  Dec  9 2014  9:45:31:850PM
  Sampling level:                 50
  Histogram tuning factor:        20
  Number of consumers:            0
  Step count:                     20
  Total row number of table:      300000916
  Table data scan:
  Local index id: 2
  Local index id: 3
  Local index id: 4
  Local index id: 5
  Local index id: 6
  Local index id: 7
  Local index id: 8
          Partition id: 793946369
                  Completed in: 23000 ms
                  Ratio: 1304761 row/sec
          Partition id: 777946312
                  Running time: 7000 ms, Processed 30.2264% of rows
                  Ratio: 1294727 row/sec
  Total number of scans: 80. Completed: 2.
          Data partitions to scan: 10     Completed: 0
          Global indexes to scan: 0       Completed: 0
          Local indexes to scan: 70       Completed: 2
  Overall completion of this process(Based on processed row count): 1.6280%
  Total running time: 30000 ms
  (return status = 0)
  

  📋✓

  示例 2

  显示当前在 spid 15 上运行的 update statistics 命令的常规信息：

  
  sp_showprogress 15
  
  Session 15 is running:          UPDATE STATISTCIS
  Table name:                     BW.SAPSR3.E_10
  Starting time:                  Dec  9 2014  9:45:31:850PM
  Sampling level:                 50
  Histogram tuning factor:        20
  Number of consumers:            0
  Step count:                     20
  Total row number of table:      300000916
  Total number of scans: 80. Completed: 5.
          Data partitions to scan: 10     Completed: 0
          Global indexes to scan: 0       Completed: 0
          Local indexes to scan: 70       Completed: 5
  Overall completion of this process(Based on processed row count): 5.4461%
  Total running time: 102990 ms
  (return status = 0)
  

  📋✓

  用法

• sp_showprogress 显示当前执行的“update statistics”命令的进度。

• 如果 <spid> 的值无效，sp_showprogress 会报告错误。在此示例中，没有值为 22 的 spid 在服务器上运行：

  
  sp_showprogress 22
  go
  There is no active server process for the specified spid value '22'.  Possibly
  the user connection has terminated.
  (return status = 1)
  
  Permissions:
  

  📋✓

• 如果进程运行了不支持的命令，sp_showprogress 会报告错误：

  
  sp_showprogress 33
  go
  Command 'select @return_value = show_progress(@spid, @display_level)' is unsupported
  (return status = 1)
  

  📋✓