您好，我是小DAI，专注于数据库管理员相关的技术问答，请问有什么可以帮您？
sp_imrs
语法
sp_imrs <action> [,<object> [, <filter> [, <secondary_filter> [, @debug ] ] ] ]
参数
> 注意
> help 参数显示的许多 <action> 参数仅供内部使用，用于对 IMRS 系统进行故障排除，并为 SAP 产品支持部门服务。只有 show 操作是供客户使用的。只有在收到 SAP 产品支持部门的指示时，才能使用其它 <action>
<action>
指定 sp_imrs 执行的操作。可以是下列项之一：
- help – 显示使用信息和示例。
- show – 显示内存行存储使用情况指标，如行计数、内存使用等。
- clear – 从队列中删除阻止其它进程运行的 spid。
<object>指定执行操作的对象。可以是下列项之一：
- all metrics – 显示所有 sp_imrs 'show' 子命令的输出。
- blocking_spid – 显示阻塞其它进程的 spid 列表。
- cacheinfo – 是 IMRS 高速缓存使用情况指标的摘要。
- effectiveness – 显示所有行类型的 insert、select、update 和 delete 操作的 IMRS 数据的有效性。例如，UpdOfMig 列会指明 IMRS 中的迁移行在 IMRS 中更新的次数。
- gcinfo – 显示有关碎片收集线程的常规信息，如状态、最后一次唤醒、需要碎片收集器看管的待定事务数。
- gcstats – 显示有关碎片收集器线程的统信息，如碎片收集器释放的总内存、碎片收集器释放的版本数。
- ilm_metrics – 显示所有 ILM 相关子命令的输出。这包括 ilmstats、ilmpcts 和 effectiveness 的指标。
- ilmstats – 显示 ILM 使用的行计数相关的统计信息。
- ilmpcts – 显示受 ILM 影响的 DML 事件的计数和百分比。
- memusage – 显示不同类型行及 IMRS 中的元数据的内存使用情况指标。
- metrics – 显示内存和行计数相关的指标。
- pack_efficiency – 显示指明各种表中的打包操作效率和高速缓存的整体效率的指标。
- pack_memstats – 显示有关打包线程和子系统内存使用的指标。
- pack_metrics – 显示所有打包相关的子命令的详细信息。
- pack_rowstats – 显示打包子系统行计数相关的指标。例如，打包的插入行的总百分比。
- rowcounts – 显示 IMRS 中的行计数。
- sysimrslogs – 显示有关 sysimrslogs 的详细信息（例如，页数、第一页、最后一页等）。
- tables – 显示表的行计数摘要和 IMRS 驻留数据，以及 IMRS 中的每个表的行百分比。
- versionstats – 显示有关数据行版本的统计信息，如版本数量、旧版本的大小和最新版本的大小。
示例
示例 1
显示 sp_imrs 的完整语法。
Usage: sp_imrs [, @action [, @thing [, @entity [, @optname [, @debug ] ] ] ] ] where: @action is one of 'help', 'show', 'clear' @thing is one of 'all metrics', 'blocking_spid', 'cacheinfo', 'effectiveness', 'ilm_metrics', 'ilmstats', 'ilmpcts', 'versionstats', 'gcinfo', 'gcstats', 'memusage', 'metrics', 'pack_efficiency', 'pack_memstats', 'pack_metrics', 'pack_rowstats', 'rowcounts', 'sysimrslogs', 'tables' @entity is a cache name or database name. @optname is name of table or partition. Examples: -- Show list of IMRS-enabled tables in a database -- sp_imrs show, tables, perf_test_db -- Show top-level cache information for all IMRS caches: -- sp_imrs show, cacheinfo -- Show cache information or memory usage for specific IMRS cache: -- sp_imrs show, cacheinfo, imrs_cache sp_imrs show, memusage, 'imrs_cache%' -- Show row counts: -- sp_imrs show, 'rowcounts', 'my_imrs' -- Show version stats: -- sp_imrs show, 'versionstats' sp_imrs show, 'versionstats', 'my_imrs' sp_imrs show, 'versionstats', 'my_imrs', '<tablename>' -- Show GC statistics or information: -- sp_imrs show, gcstats sp_imrs show, gcinfo sp_imrs show, gcstats, 'imrs_cache%' sp_imrs show, gcinfo, 'imrs_cache%' -- Show top-level information for pack subsystem: -- sp_imrs show, pack_metrics -- Show information about SPIDS blocking GC threads: -- sp_imrs show, blocking_spid sp_imrs show, blocking_spid, 'imrsdb' -- Clear a SPID off the system which may be blocking GC threads: -- sp_imrs clear, blocking_spid, 'imrsdb', '<spid>' NOTE: This command requires an additional 'sa_role' to execute -- Show sysimrslogs details for an IMRS-enabled database -- Execute this command from an IMRS-enabled database: -- sp_imrs show, sysimrslogs示例 2
显示在 tpcc 数据库中启用 IMRS 的表的有关信息（数据库名称参数是可选的。如果未提供，将在当前数据库中检查启用 IMRS 的表。）：
sp_imrs 'show', 'tables', 'tpcc' ID OwnerName Name DRC MVCC NumRows NumRowsIMRS Pct --------- --------- ---------- --- ---- --------- ----------- ------ 656002337 dbo customer 1 0 7200000 1745145 24.23 624002223 dbo district 1 0 2400 2400 100.00 688002451 dbo history 1 0 13444380 6020692 44.78 816002907 dbo item 1 0 100000 98992 98.99 720002565 dbo new_order 1 0 2742647 2682071 97.79 784002793 dbo order_line 1 0 136237347 69654012 51.12 752002679 dbo orders 1 0 13644204 7400261 54.23 848003021 dbo stock 1 0 24000000 10596743 44.15 592002109 dbo warehouse 1 0 240 240 100.00示例 3
显示有关 imrs_cache 的信息：
sp_imrs 'show', 'cacheinfo', 'imrs_cache' CacheName DBName TotalSizeMB UsedSizeMB FreeSizeMB PctUtil UsedSizeHWM PctUtilHWM NumTables NumRows NumRowsHWM NumVersions NumVersionsHWM ---------- ------ ----------- ---------- ---------- ------- ----------- ---------- --------- -------- ---------- ----------- -------------- imrs_cache tpcc 153600.21 42884.98 110715.23 27.91 45516.02 29.63 9 98201102 98201102 0 586282示例 4
显示 imrs_cache 的信息生命周期管理（ILM）指标。Score 列代表如果将表包含在 IMRS 中，此表的获益数量以及它与 IMRS 的适用程度。通常，Score 的值越大，表明列对于 IMRS 的适用性越高。在此示例中，warehouse 表适合启用 IMRS，而 history 表不在这些表中使用 DML：
> sp_imrs 'show', 'effectiveness', 'imrs_cache' ILM Effectiveness Metrics: <Op>Of<RowType>: Columns below indicate effectiveness of IMRS for <Op> affecting <RowType> Example: Column 'UpdOfMig' indicates effectiveness of IMRS for updates of migrated rows. CacheName DBName OwnerName ObjectName Score SelOfIns UpdOfIns DelOfIns SelOfMig UpdOfMig DelOfMig SelOfCached UpdOfCached DelOfCached ---------- ------ --------- ---------- --------- -------- -------- -------- -------- -------- -------- ----------- ----------- ----------- imrs_cache tpcc dbo warehouse 105752.32 NULL NULL NULL 26881.76 26049.93 0.00 26840.13 25980.50 0.00 imrs_cache tpcc dbo district 11030.40 NULL NULL NULL 241.89 5252.02 0.00 243.01 5293.48 0.00 imrs_cache tpcc dbo item 649.72 NULL NULL NULL NULL NULL NULL 649.72 0.00 0.00 imrs_cache tpcc [Any] [Totals] 49.42 0.08 0.47 0.04 0.63 4.61 0.00 39.81 3.78 0.00 imrs_cache tpcc dbo customer 15.25 NULL NULL NULL 1.53 3.78 0.00 3.93 6.01 0.00 imrs_cache tpcc dbo stock 10.91 NULL NULL NULL 0.57 6.02 0.00 1.00 3.32 0.00 imrs_cache tpcc dbo orders 2.22 0.22 0.55 0.00 0.07 1.00 0.00 0.26 0.12 0.00 imrs_cache tpcc dbo order_line 2.16 0.09 0.55 0.00 0.03 1.00 0.00 0.23 0.26 0.00 imrs_cache tpcc dbo new_order 0.55 0.00 0.00 0.55 NULL NULL NULL NULL NULL NULL imrs_cache tpcc dbo history 0.00 0.00 0.00 0.00 NULL NULL NULL NULL NULL NULL示例 5
显示 imrs_cache 的 ILM 指标：
sp_imrs 'show', 'ilm_metrics', 'imrs_cache' Row count statistics affected by ILM strategies: DBName OwnerName ObjectName NIns NUpd NDel NMig NCached NSelOfIns NUpdToIns NDelOfIns NSelOfMig NUpdToMig NDelOfMig NSelOfCached NUpdToCached NDelOfCached ------ --------- ---------- -------- --------- ------- -------- ------- --------- --------- --------- --------- --------- --------- ------------ ------------ ------------ tpcc [Any] [Totals] 80557167 137892857 3471566 20152394 1741519 7243477 38211291 3471566 12802110 93082218 0 69344399 6599348 0 tpcc dbo order_line 62105064 42420921 0 7528479 579293 5855574 34739721 0 291032 7528509 0 138074 152691 0 tpcc dbo stock 0 64414702 0 10672907 19821 0 0 0 6155596 64348781 0 19910 65921 0 tpcc dbo district 0 12693173 0 2313 103 0 0 0 559508 12147944 0 25031 545229 0 tpcc dbo orders 6215689 4287394 0 752326 494294 1387903 3471570 0 56321 752328 0 133074 63496 0 tpcc dbo new_order 6215685 0 3471566 0 0 0 0 3471566 0 0 0 0 0 0 tpcc dbo customer 0 7831278 0 1196224 548921 0 0 0 1841797 4527415 0 2161115 3303863 0 tpcc dbo warehouse 0 6245389 0 145 95 0 0 0 3897856 3777241 0 2549813 2468148 0 tpcc dbo history 6020729 0 0 0 0 0 0 0 0 0 0 0 0 0 tpcc dbo item 0 0 0 0 98992 0 0 0 0 0 0 64317382 0 0 (1 row affected) Total number of rows: 10 Counts and percentages of DML events affected by ILM: CacheName DBName OwnerName ObjectName NIns NUpd NDel InsPct UpdPct DelPct NMig NCached MigPct CachedPct ---------- ------ --------- ---------- -------- --------- ------- ------ ------ ------ -------- ------- ------ --------- imrs_cache tpcc [Any] [Totals] 80557167 137892857 3471566 36.29 62.13 1.56 20152394 1741519 92.04 7.95 imrs_cache tpcc dbo order_line 62105064 42420921 0 59.41 40.58 0.00 7528479 579293 92.85 7.14 imrs_cache tpcc dbo stock 0 64414702 0 0.00 100.00 0.00 10672907 19821 99.81 0.18 imrs_cache tpcc dbo district 0 12693173 0 0.00 100.00 0.00 2313 103 95.73 4.26 imrs_cache tpcc dbo orders 6215689 4287394 0 59.17 40.82 0.00 752326 494294 60.34 39.65 imrs_cache tpcc dbo new_order 6215685 0 3471566 64.16 0.00 35.83 0 0 NULL NULL imrs_cache tpcc dbo customer 0 7831278 0 0.00 100.00 0.00 1196224 548921 68.54 31.45 imrs_cache tpcc dbo warehouse 0 6245389 0 0.00 100.00 0.00 145 95 60.41 39.58 imrs_cache tpcc dbo history 6020729 0 0 100.00 0.00 0.00 0 0 NULL NULL imrs_cache tpcc dbo item 0 0 0 NULL NULL NULL 0 98992 0.00 100.00 (1 row affected) Total number of rows: 10 ILM Effectiveness Metrics: <Op>Of<RowType>: Columns below indicate effectiveness of IMRS for <Op> affecting <RowType> Example: Column 'UpdOfMig' indicates effectiveness of IMRS for updates of migrated rows. CacheName DBName OwnerName ObjectName Score SelOfIns UpdOfIns DelOfIns SelOfMig UpdOfMig DelOfMig SelOfCached UpdOfCached DelOfCached ---------- ------ --------- ---------- --------- -------- -------- -------- -------- -------- -------- ----------- ----------- ----------- imrs_cache tpcc dbo warehouse 105752.32 NULL NULL NULL 26881.76 26049.93 0.00 26840.13 25980.50 0.00 imrs_cache tpcc dbo district 11030.40 NULL NULL NULL 241.89 5252.02 0.00 243.01 5293.48 0.00 imrs_cache tpcc dbo item 649.72 NULL NULL NULL NULL NULL NULL 649.72 0.00 0.00 imrs_cache tpcc [Any] [Totals] 49.42 0.08 0.47 0.04 0.63 4.61 0.00 39.81 3.78 0.00 imrs_cache tpcc dbo customer 15.25 NULL NULL NULL 1.53 3.78 0.00 3.93 6.01 0.00 imrs_cache tpcc dbo stock 10.91 NULL NULL NULL 0.57 6.02 0.00 1.00 3.32 0.00 imrs_cache tpcc dbo orders 2.22 0.22 0.55 0.00 0.07 1.00 0.00 0.26 0.12 0.00 imrs_cache tpcc dbo order_line 2.16 0.09 0.55 0.00 0.03 1.00 0.00 0.23 0.26 0.00 imrs_cache tpcc dbo new_order 0.55 0.00 0.00 0.55 NULL NULL NULL NULL NULL NULL imrs_cache tpcc dbo history 0.00 0.00 0.00 0.00 NULL NULL NULL NULL NULL NULL (1 row affected) Total number of rows: 10示例 6
显示 imrs_cache 的行计数统计信息：
sp_imrs 'show', 'ilmstats', 'imrs_cache' Row count statistics affected by ILM strategies: DBName OwnerName ObjectName NIns NUpd NDel NMig NCached NSelOfIns NUpdToIns NDelOfIns NSelOfMig NUpdToMig NDelOfMig NSelOfCached NUpdToCached NDelOfCached ------ --------- ---------- -------- --------- ------- -------- ------- --------- --------- --------- --------- --------- --------- ------------ ------------ ------------ tpcc [Any] [Totals] 80557167 137892857 3471566 20152394 1741519 7243477 38211291 3471566 12802110 93082218 0 69344399 6599348 0 tpcc dbo order_line 62105064 42420921 0 7528479 579293 5855574 34739721 0 291032 7528509 0 138074 152691 0 tpcc dbo stock 0 64414702 0 10672907 19821 0 0 0 6155596 64348781 0 19910 65921 0 tpcc dbo district 0 12693173 0 2313 103 0 0 0 559508 12147944 0 25031 545229 0 tpcc dbo orders 6215689 4287394 0 752326 494294 1387903 3471570 0 56321 752328 0 133074 63496 0 tpcc dbo new_order 6215685 0 3471566 0 0 0 0 3471566 0 0 0 0 0 0 tpcc dbo customer 0 7831278 0 1196224 548921 0 0 0 1841797 4527415 0 2161115 3303863 0 tpcc dbo warehouse 0 6245389 0 145 95 0 0 0 3897856 3777241 0 2549813 2468148 0 tpcc dbo history 6020729 0 0 0 0 0 0 0 0 0 0 0 0 0 tpcc dbo item 0 0 0 0 98992 0 0 0 0 0 0 64317382 0 0 (10 rows affected) Total number of rows: 10示例 7
显示受 ILM 影响的 DML 事件的相关信息：
sp_imrs 'show', 'ilmpcts', 'imrs_cache' Counts and percentages of DML events affected by ILM: CacheName DBName OwnerName ObjectName NIns NUpd NDel InsPct UpdPct DelPct NMig NCached MigPct CachedPct ---------- ------ --------- ---------- -------- --------- ------- ------ ------ ------ -------- ------- ------ --------- imrs_cache tpcc [Any] [Totals] 80557167 137892857 3471566 36.29 62.13 1.56 20152394 1741519 92.04 7.95 imrs_cache tpcc dbo order_line 62105064 42420921 0 59.41 40.58 0.00 7528479 579293 92.85 7.14 imrs_cache tpcc dbo stock 0 64414702 0 0.00 100.00 0.00 10672907 19821 99.81 0.18 imrs_cache tpcc dbo district 0 12693173 0 0.00 100.00 0.00 2313 103 95.73 4.26 imrs_cache tpcc dbo orders 6215689 4287394 0 59.17 40.82 0.00 752326 494294 60.34 39.65 imrs_cache tpcc dbo new_order 6215685 0 3471566 64.16 0.00 35.83 0 0 NULL NULL imrs_cache tpcc dbo customer 0 7831278 0 0.00 100.00 0.00 1196224 548921 68.54 31.45 imrs_cache tpcc dbo warehouse 0 6245389 0 0.00 100.00 0.00 145 95 60.41 39.58 imrs_cache tpcc dbo history 6020729 0 0 100.00 0.00 0.00 0 0 NULL NULL imrs_cache tpcc dbo item 0 0 0 NULL NULL NULL 0 98992 0.00 100.00 (10 rows affected) Total number of rows: 10示例 9
显示 imrs_cache 的指标：
sp_imrs 'show', 'metrics', 'imrs_cache' **<strong> Execute: sp_imrs show, tables, tpcc </strong><strong> ID OwnerName Name DRC MVCC NumRows NumRowsIMRS Pct --------- --------- ---------- --- ---- --------- ----------- ------ 656002337 dbo customer 1 0 7200000 1745145 24.23 624002223 dbo district 1 0 2400 2400 100.00 688002451 dbo history 1 0 13444380 6020692 44.78 816002907 dbo item 1 0 100000 98992 98.99 720002565 dbo new_order 1 0 2742647 2682071 97.79 784002793 dbo order_line 1 0 136237347 69654012 51.12 752002679 dbo orders 1 0 13644204 7400261 54.23 848003021 dbo stock 1 0 24000000 10596743 44.15 592002109 dbo warehouse 1 0 240 240 100.00 (1 row affected) </strong><strong> Execute: sp_imrs show, cacheinfo, imrs_cache </strong><strong> CacheName DBName TotalSizeMB UsedSizeMB FreeSizeMB PctUtil UsedSizeHWM PctUtilHWM NumTables NumRows NumRowsHWM NumVersions NumVersionsHWM ---------- ------ ----------- ---------- ---------- ------- ----------- ---------- --------- -------- ---------- ----------- -------------- imrs_cache tpcc 153600.21 42884.98 110715.23 27.91 45516.02 29.63 9 98201102 98201102 0 586282 (1 row affected) </strong><strong> Execute: sp_imrs show, rowcounts, imrs_cache </strong><strong> DBName OwnerName ObjectName NRows NRowsHWM NVersions NVersHWM NInsRows NMigRows NCachedRows NInsVers NMigVers NRowsPendGC ------ --------- ---------- -------- -------- --------- -------- -------- -------- ----------- -------- -------- ----------- tpcc [Any] [Totals] 98201102 98202326 0 715406 76403190 19848638 974637 0 0 0 tpcc dbo order_line 69654448 69654471 0 214648 61546676 7681170 426602 0 0 0 tpcc dbo stock 10596743 10596745 0 322917 0 10586259 10484 0 0 0 tpcc dbo orders 7400261 7400262 0 21409 6153641 815822 430798 0 0 0 tpcc dbo history 6020729 6020729 0 0 6020729 0 0 0 0 0 tpcc dbo new_order 2682144 2683342 0 15081 2682144 0 0 0 0 0 tpcc dbo customer 1745145 1745145 0 49671 0 1737384 7761 0 0 0 tpcc dbo item 98992 98992 0 0 0 0 98992 0 0 0 tpcc dbo district 2400 2400 0 62938 0 2400 0 0 0 0 tpcc dbo warehouse 240 240 0 28742 0 240 0 0 0 0 (10 rows affected) Total number of rows: 10 </strong><strong> Execute: sp_imrs show, memusage, imrs_cache </strong><strong> DBName OwnerName ObjectName NRows LatestVersMB LatestVersHWM OlderVersMB OlderVersHWM InsRowsMB MigRowsMB QPFRowsMB TotalMemMB ------ --------- ---------- -------- ------------ ------------- ----------- ------------ --------- --------- --------- ---------- tpcc dbo order_line 69654448 19145.23 19145.23 0.00 39.16 16915.13 2230.10 0.00 19145.23 tpcc dbo stock 10596743 5501.37 5501.37 0.00 137.58 0.00 5501.37 0.00 5501.37 tpcc dbo orders 7400261 1808.07 1808.07 0.00 3.26 1503.24 304.84 0.00 1808.07 tpcc dbo history 6020729 1655.38 1655.38 0.00 0.00 1655.38 0.00 0.00 1655.38 tpcc dbo customer 1745145 1493.07 1498.86 0.00 37.91 0.00 1493.07 0.00 1493.07 tpcc dbo new_order 2682144 654.82 655.03 0.00 2.34 654.82 0.00 0.00 654.82 tpcc dbo item 98992 31.00 31.00 0.00 0.00 0.00 31.00 0.00 31.00 tpcc dbo district 2400 0.81 0.84 0.00 14.94 0.00 0.81 0.00 0.81 tpcc dbo warehouse 240 0.07 0.08 0.00 6.09 0.00 0.07 0.00 0.07 (1 row affected) Total number of rows: 9 Cache Utilization Metrics for set of objects listed above: CacheName DBName CacheSizeMB UsedFor UsedSizeMB CacheUtilPct ---------- ------ ----------- -------- ---------- ------------ imrs_cache tpcc 153600.21 Metadata 12595.15 8.199 imrs_cache tpcc 153600.21 Tables 30289.82 19.719 </strong><strong> Execute: sp_imrs show, ilm_metrics, imrs_cache </strong><strong> Row count statistics affected by ILM strategies: DBName OwnerName ObjectName NIns NUpd NDel NMig NCached NSelOfIns NUpdToIns NDelOfIns NSelOfMig NUpdToMig NDelOfMig NSelOfCached NUpdToCached NDelOfCached ------ --------- ---------- -------- --------- ------- -------- ------- --------- --------- --------- --------- --------- --------- ------------ ------------ ------------ tpcc [Any] [Totals] 80557167 137892857 3471566 20152394 1741519 7243477 38211291 3471566 12802110 93082218 0 69344399 6599348 0 tpcc dbo order_line 62105064 42420921 0 7528479 579293 5855574 34739721 0 291032 7528509 0 138074 152691 0 tpcc dbo stock 0 64414702 0 10672907 19821 0 0 0 6155596 64348781 0 19910 65921 0 tpcc dbo district 0 12693173 0 2313 103 0 0 0 559508 12147944 0 25031 545229 0 tpcc dbo orders 6215689 4287394 0 752326 494294 1387903 3471570 0 56321 752328 0 133074 63496 0 tpcc dbo new_order 6215685 0 3471566 0 0 0 0 3471566 0 0 0 0 0 0 tpcc dbo customer 0 7831278 0 1196224 548921 0 0 0 1841797 4527415 0 2161115 3303863 0 tpcc dbo warehouse 0 6245389 0 145 95 0 0 0 3897856 3777241 0 2549813 2468148 0 tpcc dbo history 6020729 0 0 0 0 0 0 0 0 0 0 0 0 0 tpcc dbo item 0 0 0 0 98992 0 0 0 0 0 0 64317382 0 0 (1 row affected) Total number of rows: 10 Counts and percentages of DML events affected by ILM: CacheName DBName OwnerName ObjectName NIns NUpd NDel InsPct UpdPct DelPct NMig NCached MigPct CachedPct ---------- ------ --------- ---------- -------- --------- ------- ------ ------ ------ -------- ------- ------ --------- imrs_cache tpcc [Any] [Totals] 80557167 137892857 3471566 36.29 62.13 1.56 20152394 1741519 92.04 7.95 imrs_cache tpcc dbo order_line 62105064 42420921 0 59.41 40.58 0.00 7528479 579293 92.85 7.14 imrs_cache tpcc dbo stock 0 64414702 0 0.00 100.00 0.00 10672907 19821 99.81 0.18 imrs_cache tpcc dbo district 0 12693173 0 0.00 100.00 0.00 2313 103 95.73 4.26 imrs_cache tpcc dbo orders 6215689 4287394 0 59.17 40.82 0.00 752326 494294 60.34 39.65 imrs_cache tpcc dbo new_order 6215685 0 3471566 64.16 0.00 35.83 0 0 NULL NULL imrs_cache tpcc dbo customer 0 7831278 0 0.00 100.00 0.00 1196224 548921 68.54 31.45 imrs_cache tpcc dbo warehouse 0 6245389 0 0.00 100.00 0.00 145 95 60.41 39.58 imrs_cache tpcc dbo history 6020729 0 0 100.00 0.00 0.00 0 0 NULL NULL imrs_cache tpcc dbo item 0 0 0 NULL NULL NULL 0 98992 0.00 100.00 (1 row affected) Total number of rows: 10 ILM Effectiveness Metrics: <Op>Of<RowType>: Columns below indicate effectiveness of IMRS for <Op> affecting <RowType> Example: Column 'UpdOfMig' indicates effectiveness of IMRS for updates of migrated rows. CacheName DBName OwnerName ObjectName Score SelOfIns UpdOfIns DelOfIns SelOfMig UpdOfMig DelOfMig SelOfCached UpdOfCached DelOfCached ---------- ------ --------- ---------- --------- -------- -------- -------- -------- -------- -------- ----------- ----------- ----------- imrs_cache tpcc dbo warehouse 105752.32 NULL NULL NULL 26881.76 26049.93 0.00 26840.13 25980.50 0.00 imrs_cache tpcc dbo district 11030.40 NULL NULL NULL 241.89 5252.02 0.00 243.01 5293.48 0.00 imrs_cache tpcc dbo item 649.72 NULL NULL NULL NULL NULL NULL 649.72 0.00 0.00 imrs_cache tpcc [Any] [Totals] 49.42 0.08 0.47 0.04 0.63 4.61 0.00 39.81 3.78 0.00 imrs_cache tpcc dbo customer 15.25 NULL NULL NULL 1.53 3.78 0.00 3.93 6.01 0.00 imrs_cache tpcc dbo stock 10.91 NULL NULL NULL 0.57 6.02 0.00 1.00 3.32 0.00 imrs_cache tpcc dbo orders 2.22 0.22 0.55 0.00 0.07 1.00 0.00 0.26 0.12 0.00 imrs_cache tpcc dbo order_line 2.16 0.09 0.55 0.00 0.03 1.00 0.00 0.23 0.26 0.00 imrs_cache tpcc dbo new_order 0.55 0.00 0.00 0.55 NULL NULL NULL NULL NULL NULL imrs_cache tpcc dbo history 0.00 0.00 0.00 0.00 NULL NULL NULL NULL NULL NULL (1 row affected) Total number of rows: 10 </strong><strong> Execute: sp_imrs show, pack_metrics, imrs_cache </strong><strong> Row counts for pack subsystem: CacheName DBName OwnerName ObjectName NRows InsPct MigPct CachedPct NDRPackPct NSteadyPct NAggrPct SkipRatio NSkipped HotRowsPct NoLockPct GT1VersPct OtherPct --------- ------ --------- ---------- ----- ------ ------ --------- ---------- ---------- -------- --------- -------- ---------- --------- ---------- -------- (1 row affected) Total number of rows: 0 Memory related metrics for pack subsystem: CacheName DBName OwnerName ObjectName LastPacked LastVisited PackedMB InsMBPct MigMBPct CachedMBPct MemOvhdMB MemOvhdPct --------- ------ --------- ---------- ---------- ----------- -------- -------- -------- ----------- --------- ---------- (1 row affected) Total number of rows: 0 Pack efficiency and transaction metrics for pack subsystem: CacheName DBName OwnerName ObjectName SkipRatio MemOvhdPct NCTrans DRTPct SteadyTPct AggrTPct NRBTrans MBPerTran NRowsPerTran NDRowsPerTran NStRowsPerTran NAgRowsPerTran --------- ------ --------- ---------- --------- ---------- ------- ------ ---------- -------- -------- --------- ------------ ------------- -------------- -------------- (1 row affected) Total number of rows: 0 </strong><strong> Execute: sp_imrs show, sysimrslogs, imrs_cache </strong><strong> Description Value Comment --------------------------------------------------------- -------- ------------------------ Total number of pages in imrslogsegment 16000000 250000.00 MB Total number of pages in use 3597587 56212.30 MB ( 22.48 %) Number of non-truncatable pages 3597587 56212.30 MB ( 22.48 %) Number of truncatable pages 0 0.00 MB ( 0.00 %) Total number of free pages of imrslogsegment 12339912 192811.13 MB ( 77.12 %) First page ID of sysimrslogs 9600001 Last page ID of sysimrslogs 13211696 Page ID of oldest non-truncatable page 9600001 Percentage of active space that can be freed by DUMP TRAN 0 Number of pages reserved for Last-Chance Threshold (LCT) 800000 12500.00 MB ( 5.00 %) (10 rows affected)示例 10
显示 imrs_cache 打包的有效程度的有关信息：
sp_imrs 'show', 'pack_efficiency', 'imrs_cache' Pack efficiency and transaction metrics for pack subsystem: CacheName DBName OwnerName ObjectName SkipRatio MemOvhdPct NCTrans DRTPct SteadyTPct AggrTPct NRBTrans MBPerTran NRowsPerTran NDRowsPerTran NStRowsPerTran NAgRowsPerTran ---------- ------ --------- ---------- --------- ---------- ------- ------ ---------- -------- -------- --------- ------------ ------------- -------------- -------------- imrs_cache tpcc dbo new_order 0 43.70 39998 100.00 0.00 0.00 17384 0 86 86 NULL NULL imrs_cache tpcc [Any] [Totals] 0 43.70 39998 100.00 0.00 0.00 17384 0 86 86 NULL NULL imrs_cache tpcc dbo item NULL NULL 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL imrs_cache tpcc dbo stock NULL NULL 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL imrs_cache tpcc dbo orders NULL NULL 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL imrs_cache tpcc dbo history NULL NULL 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL imrs_cache tpcc dbo customer NULL NULL 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL imrs_cache tpcc dbo district NULL NULL 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL imrs_cache tpcc dbo warehouse NULL NULL 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL imrs_cache tpcc dbo order_line NULL NULL 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL (10 rows affected) Total number of rows: 10示例 11
显示 imrs_cache 的打包指标：
sp_imrs 'show', 'pack_memstats', 'imrs_cache' Memory related metrics for pack subsystem: CacheName DBName OwnerName ObjectName LastPacked LastVisited PackedMB InsMBPct MigMBPct CachedMBPct MemOvhdMB MemOvhdPct ---------- ------ --------- ---------- ------------------- ------------------- -------- -------- -------- ----------- --------- ---------- imrs_cache tpcc dbo new_order Jan 20 2017 1:45AM Jan 20 2017 1:45AM 848 100.00 0.00 0.00 370 43.70 imrs_cache tpcc [Any] [Totals] Jan 20 2017 1:45AM Jan 20 2017 1:45AM 848 100.00 0.00 0.00 370 43.70 imrs_cache tpcc dbo item NULL NULL 0 NULL NULL NULL 0 NULL imrs_cache tpcc dbo stock NULL NULL 0 NULL NULL NULL 0 NULL imrs_cache tpcc dbo orders NULL NULL 0 NULL NULL NULL 0 NULL imrs_cache tpcc dbo history NULL NULL 0 NULL NULL NULL 0 NULL imrs_cache tpcc dbo customer NULL NULL 0 NULL NULL NULL 0 NULL imrs_cache tpcc dbo district NULL NULL 0 NULL NULL NULL 0 NULL imrs_cache tpcc dbo warehouse NULL NULL 0 NULL NULL NULL 0 NULL imrs_cache tpcc dbo order_line NULL NULL 0 NULL NULL NULL 0 NULL (10 rows affected) Total number of rows: 10 (return status = 0)示例 12
显示 imrs_cache 的行计数相关的统计信息：
sp_imrs 'show', 'pack_rowstats', 'imrs_cache' Row counts for pack subsystem: CacheName DBName OwnerName ObjectName NRows InsPct MigPct CachedPct NDRPackPct NSteadyPct NAggrPct SkipRatio NSkipped HotRowsPct NoLockPct GT1VersPct OtherPct ---------- ------ --------- ---------- ------- ------ ------ --------- ---------- ---------- -------- --------- -------- ---------- --------- ---------- -------- imrs_cache tpcc dbo new_order 3471493 100.00 0.00 0.00 100.00 0.00 0.00 0 0 NULL NULL NULL NULL imrs_cache tpcc [Any] [Totals] 3471493 100.00 0.00 0.00 100.00 0.00 0.00 0 0 NULL NULL NULL NULL imrs_cache tpcc dbo item 0 NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL imrs_cache tpcc dbo stock 0 NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL imrs_cache tpcc dbo orders 0 NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL imrs_cache tpcc dbo history 0 NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL imrs_cache tpcc dbo customer 0 NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL imrs_cache tpcc dbo district 0 NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL imrs_cache tpcc dbo warehouse 0 NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL imrs_cache tpcc dbo order_line 0 NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL (10 rows affected) Total number of rows: 10示例 13
显示打包操作的行计数的统计信息：
sp_imrs 'show', 'pack_metrics', 'imrs_cache' Row counts for pack subsystem: CacheName DBName OwnerName ObjectName NRows InsPct MigPct CachedPct NDRPackPct NSteadyPct NAggrPct SkipRatio NSkipped HotRowsPct NoLockPct GT1VersPct OtherPct ---------- ------ --------- ---------- ------- ------ ------ --------- ---------- ---------- -------- --------- -------- ---------- --------- ---------- -------- imrs_cache tpcc dbo new_order 3471493 100.00 0.00 0.00 100.00 0.00 0.00 0 0 NULL NULL NULL NULL imrs_cache tpcc [Any] [Totals] 3471493 100.00 0.00 0.00 100.00 0.00 0.00 0 0 NULL NULL NULL NULL imrs_cache tpcc dbo item 0 NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL imrs_cache tpcc dbo stock 0 NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL imrs_cache tpcc dbo orders 0 NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL imrs_cache tpcc dbo history 0 NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL imrs_cache tpcc dbo customer 0 NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL imrs_cache tpcc dbo district 0 NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL imrs_cache tpcc dbo warehouse 0 NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL imrs_cache tpcc dbo order_line 0 NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL (1 row affected) Total number of rows: 10 Memory related metrics for pack subsystem: CacheName DBName OwnerName ObjectName LastPacked LastVisited PackedMB InsMBPct MigMBPct CachedMBPct MemOvhdMB MemOvhdPct ---------- ------ --------- ---------- ------------------- ------------------- -------- -------- -------- ----------- --------- ---------- imrs_cache tpcc dbo new_order Jan 20 2017 1:45AM Jan 20 2017 1:45AM 848 100.00 0.00 0.00 370 43.70 imrs_cache tpcc [Any] [Totals] Jan 20 2017 1:45AM Jan 20 2017 1:45AM 848 100.00 0.00 0.00 370 43.70 imrs_cache tpcc dbo item NULL NULL 0 NULL NULL NULL 0 NULL imrs_cache tpcc dbo stock NULL NULL 0 NULL NULL NULL 0 NULL imrs_cache tpcc dbo orders NULL NULL 0 NULL NULL NULL 0 NULL imrs_cache tpcc dbo history NULL NULL 0 NULL NULL NULL 0 NULL imrs_cache tpcc dbo customer NULL NULL 0 NULL NULL NULL 0 NULL imrs_cache tpcc dbo district NULL NULL 0 NULL NULL NULL 0 NULL imrs_cache tpcc dbo warehouse NULL NULL 0 NULL NULL NULL 0 NULL imrs_cache tpcc dbo order_line NULL NULL 0 NULL NULL NULL 0 NULL (1 row affected) Total number of rows: 10 Pack efficiency and transaction metrics for pack subsystem: CacheName DBName OwnerName ObjectName SkipRatio MemOvhdPct NCTrans DRTPct SteadyTPct AggrTPct NRBTrans MBPerTran NRowsPerTran NDRowsPerTran NStRowsPerTran NAgRowsPerTran ---------- ------ --------- ---------- --------- ---------- ------- ------ ---------- -------- -------- --------- ------------ ------------- -------------- -------------- imrs_cache tpcc dbo new_order 0 43.70 39998 100.00 0.00 0.00 17384 0 86 86 NULL NULL imrs_cache tpcc [Any] [Totals] 0 43.70 39998 100.00 0.00 0.00 17384 0 86 86 NULL NULL imrs_cache tpcc dbo item NULL NULL 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL imrs_cache tpcc dbo stock NULL NULL 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL imrs_cache tpcc dbo orders NULL NULL 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL imrs_cache tpcc dbo history NULL NULL 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL imrs_cache tpcc dbo customer NULL NULL 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL imrs_cache tpcc dbo district NULL NULL 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL imrs_cache tpcc dbo warehouse NULL NULL 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL imrs_cache tpcc dbo order_line NULL NULL 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL (1 row affected) Total number of rows: 10示例 15
显示有关 imrs_cache 的内存使用情况信息（sp_imrs 内部发出 sp_imrs show, rowcounts, imrs_cache 和 sp_imrs show, sysimrslogs, imrs_cache）：
sp_imrs 'show', 'memusage', 'imrs_cache' DBName OwnerName ObjectName NRows LatestVersMB LatestVersHWM OlderVersMB OlderVersHWM InsRowsMB MigRowsMB QPFRowsMB TotalMemMB ------ --------- ---------- -------- ------------ ------------- ----------- ------------ --------- --------- --------- ---------- tpcc dbo order_line 69654448 19145.23 19145.23 0.00 39.16 16915.13 2230.10 0.00 19145.23 tpcc dbo stock 10596743 5501.37 5501.37 0.00 137.58 0.00 5501.37 0.00 5501.37 tpcc dbo orders 7400261 1808.07 1808.07 0.00 3.26 1503.24 304.84 0.00 1808.07 tpcc dbo history 6020729 1655.38 1655.38 0.00 0.00 1655.38 0.00 0.00 1655.38 tpcc dbo customer 1745145 1493.07 1498.86 0.00 37.91 0.00 1493.07 0.00 1493.07 tpcc dbo new_order 2682144 654.82 655.03 0.00 2.34 654.82 0.00 0.00 654.82 tpcc dbo item 98992 31.00 31.00 0.00 0.00 0.00 31.00 0.00 31.00 tpcc dbo district 2400 0.81 0.84 0.00 14.94 0.00 0.81 0.00 0.81 tpcc dbo warehouse 240 0.07 0.08 0.00 6.09 0.00 0.07 0.00 0.07 (1 row affected) Total number of rows: 9示例 16
显示 imrs_cache 的行计数：
sp_imrs show, rowcounts, imrs_cache DBName OwnerName ObjectName NRows NRowsHWM NVersions NVersHWM NInsRows NMigRows NCachedRows NInsVers NMigVers NRowsPendGC ------ --------- ---------- -------- -------- --------- -------- -------- -------- ----------- -------- -------- ----------- tpcc [Any] [Totals] 98201102 98202326 0 715406 76403190 19848638 974637 0 0 0 tpcc dbo order_line 69654448 69654471 0 214648 61546676 7681170 426602 0 0 0 tpcc dbo stock 10596743 10596745 0 322917 0 10586259 10484 0 0 0 tpcc dbo orders 7400261 7400262 0 21409 6153641 815822 430798 0 0 0 tpcc dbo history 6020729 6020729 0 0 6020729 0 0 0 0 0 tpcc dbo new_order 2682144 2683342 0 15081 2682144 0 0 0 0 0 tpcc dbo customer 1745145 1745145 0 49671 0 1737384 7761 0 0 0 tpcc dbo item 98992 98992 0 0 0 0 98992 0 0 0 tpcc dbo district 2400 2400 0 62938 0 2400 0 0 0 0 tpcc dbo warehouse 240 240 0 28742 0 240 0 0 0 0 (10 rows affected) Total number of rows: 10示例 17
显示 imrs_cache 的 sysimrslogs 信息：
sp_imrs show, sysimrslogs, imrs_cache </strong><strong> Description Value Comment --------------------------------------------------------- -------- ------------------------ Total number of pages in imrslogsegment 16000000 250000.00 MB Total number of pages in use 3597587 56212.30 MB ( 22.48 %) Number of non-truncatable pages 3597587 56212.30 MB ( 22.48 %) Number of truncatable pages 0 0.00 MB ( 0.00 %) Total number of free pages of imrslogsegment 12339912 192811.13 MB ( 77.12 %) First page ID of sysimrslogs 9600001 Last page ID of sysimrslogs 13211696 Page ID of oldest non-truncatable page 9600001 Percentage of active space that can be freed by DUMP TRAN 0 Number of pages reserved for Last-Chance Threshold (LCT) 800000 12500.00 MB ( 5.00 %)示例 18
显示当前阻塞进程的 spid：
sp_imrs show, 'blocking_spid' SPID UserName HostName OSPID StartTime NTransBlocked DBName Status ---- -------- -------------- ----- ------------------- ------------- ------- ---------- 36 dbo big_machine 32441 Mar 29 2017 2:24AM 0 imrsdb recv sleep 37 dbo bigger_machine 6381 Mar 29 2017 2:27AM 0 imrsdb2 recv sleep Above SPID(s) are active in the server. GC threads will be unblocked when the transaction completes (commit / abort) If the SPID has become unresponsive, you may disconnect it and run the following - sp_imrs 'clear', 'blocking_spid', '<dbname>', '<spid>' Running the above command on an active connection may result in data corruption. Please exercise due caution!使用此命令删除编号为 36 的阻塞 spid：
sp_imrs clear, 'blocking_spid', 'imrsdb', '36' SPID 36 is active in the system. Cleaning up an active process may result in data corruption. Action aborted! (return status = 0)示例 19
显示 imrs_cache 的所有指标：
sp_imrs 'show', 'all metrics', 'imrs_cache' </strong><strong> Execute: sp_imrs show, tables, tpcc </strong><strong> ID OwnerName Name DRC MVCC NumRows NumRowsIMRS Pct --------- --------- ---------- --- ---- --------- ----------- ------ 656002337 dbo customer 1 0 7200000 1745145 24.23 624002223 dbo district 1 0 2400 2400 100.00 688002451 dbo history 1 0 13444380 6020692 44.78 816002907 dbo item 1 0 100000 98992 98.99 720002565 dbo new_order 1 0 2742647 2682071 97.79 784002793 dbo order_line 1 0 136237347 69654012 51.12 752002679 dbo orders 1 0 13644204 7400261 54.23 848003021 dbo stock 1 0 24000000 10596743 44.15 592002109 dbo warehouse 1 0 240 240 100.00 (1 row affected) </strong><strong> Execute: sp_imrs show, cacheinfo, imrs_cache </strong><strong> CacheName DBName TotalSizeMB UsedSizeMB FreeSizeMB PctUtil UsedSizeHWM PctUtilHWM NumTables NumRows NumRowsHWM NumVersions NumVersionsHWM ---------- ------ ----------- ---------- ---------- ------- ----------- ---------- --------- -------- ---------- ----------- -------------- imrs_cache tpcc 153600.21 42884.98 110715.23 27.91 45516.02 29.63 9 98201102 98201102 0 586282 (1 row affected) </strong><strong> Execute: sp_imrs show, rowcounts, imrs_cache </strong><strong> DBName OwnerName ObjectName NRows NRowsHWM NVersions NVersHWM NInsRows NMigRows NCachedRows NInsVers NMigVers NRowsPendGC ------ --------- ---------- -------- -------- --------- -------- -------- -------- ----------- -------- -------- ----------- tpcc [Any] [Totals] 98201102 98202326 0 715406 76403190 19848638 974637 0 0 0 tpcc dbo order_line 69654448 69654471 0 214648 61546676 7681170 426602 0 0 0 tpcc dbo stock 10596743 10596745 0 322917 0 10586259 10484 0 0 0 tpcc dbo orders 7400261 7400262 0 21409 6153641 815822 430798 0 0 0 tpcc dbo history 6020729 6020729 0 0 6020729 0 0 0 0 0 tpcc dbo new_order 2682144 2683342 0 15081 2682144 0 0 0 0 0 tpcc dbo customer 1745145 1745145 0 49671 0 1737384 7761 0 0 0 tpcc dbo item 98992 98992 0 0 0 0 98992 0 0 0 tpcc dbo district 2400 2400 0 62938 0 2400 0 0 0 0 tpcc dbo warehouse 240 240 0 28742 0 240 0 0 0 0 (10 rows affected) Total number of rows: 10 </strong><strong> Execute: sp_imrs show, memusage, imrs_cache </strong><strong> DBName OwnerName ObjectName NRows LatestVersMB LatestVersHWM OlderVersMB OlderVersHWM InsRowsMB MigRowsMB QPFRowsMB TotalMemMB ------ --------- ---------- -------- ------------ ------------- ----------- ------------ --------- --------- --------- ---------- tpcc dbo order_line 69654448 19145.23 19145.23 0.00 39.16 16915.13 2230.10 0.00 19145.23 tpcc dbo stock 10596743 5501.37 5501.37 0.00 137.58 0.00 5501.37 0.00 5501.37 tpcc dbo orders 7400261 1808.07 1808.07 0.00 3.26 1503.24 304.84 0.00 1808.07 tpcc dbo history 6020729 1655.38 1655.38 0.00 0.00 1655.38 0.00 0.00 1655.38 tpcc dbo customer 1745145 1493.07 1498.86 0.00 37.91 0.00 1493.07 0.00 1493.07 tpcc dbo new_order 2682144 654.82 655.03 0.00 2.34 654.82 0.00 0.00 654.82 tpcc dbo item 98992 31.00 31.00 0.00 0.00 0.00 31.00 0.00 31.00 tpcc dbo district 2400 0.81 0.84 0.00 14.94 0.00 0.81 0.00 0.81 tpcc dbo warehouse 240 0.07 0.08 0.00 6.09 0.00 0.07 0.00 0.07 (1 row affected) Total number of rows: 9 Cache Utilization Metrics for set of objects listed above: CacheName DBName CacheSizeMB UsedFor UsedSizeMB CacheUtilPct ---------- ------ ----------- -------- ---------- ------------ imrs_cache tpcc 153600.21 Metadata 12595.15 8.199 imrs_cache tpcc 153600.21 Tables 30289.82 19.719 </strong><strong> Execute: sp_imrs show, ilm_metrics, imrs_cache </strong><strong> Row count statistics affected by ILM strategies: DBName OwnerName ObjectName NIns NUpd NDel NMig NCached NSelOfIns NUpdToIns NDelOfIns NSelOfMig NUpdToMig NDelOfMig NSelOfCached NUpdToCached NDelOfCached ------ --------- ---------- -------- --------- ------- -------- ------- --------- --------- --------- --------- --------- --------- ------------ ------------ ------------ tpcc [Any] [Totals] 80557167 137892857 3471566 20152394 1741519 7243477 38211291 3471566 12802110 93082218 0 69344399 6599348 0 tpcc dbo order_line 62105064 42420921 0 7528479 579293 5855574 34739721 0 291032 7528509 0 138074 152691 0 tpcc dbo stock 0 64414702 0 10672907 19821 0 0 0 6155596 64348781 0 19910 65921 0 tpcc dbo district 0 12693173 0 2313 103 0 0 0 559508 12147944 0 25031 545229 0 tpcc dbo orders 6215689 4287394 0 752326 494294 1387903 3471570 0 56321 752328 0 133074 63496 0 tpcc dbo new_order 6215685 0 3471566 0 0 0 0 3471566 0 0 0 0 0 0 tpcc dbo customer 0 7831278 0 1196224 548921 0 0 0 1841797 4527415 0 2161115 3303863 0 tpcc dbo warehouse 0 6245389 0 145 95 0 0 0 3897856 3777241 0 2549813 2468148 0 tpcc dbo history 6020729 0 0 0 0 0 0 0 0 0 0 0 0 0 tpcc dbo item 0 0 0 0 98992 0 0 0 0 0 0 64317382 0 0 (1 row affected) Total number of rows: 10 Counts and percentages of DML events affected by ILM: CacheName DBName OwnerName ObjectName NIns NUpd NDel InsPct UpdPct DelPct NMig NCached MigPct CachedPct ---------- ------ --------- ---------- -------- --------- ------- ------ ------ ------ -------- ------- ------ --------- imrs_cache tpcc [Any] [Totals] 80557167 137892857 3471566 36.29 62.13 1.56 20152394 1741519 92.04 7.95 imrs_cache tpcc dbo order_line 62105064 42420921 0 59.41 40.58 0.00 7528479 579293 92.85 7.14 imrs_cache tpcc dbo stock 0 64414702 0 0.00 100.00 0.00 10672907 19821 99.81 0.18 imrs_cache tpcc dbo district 0 12693173 0 0.00 100.00 0.00 2313 103 95.73 4.26 imrs_cache tpcc dbo orders 6215689 4287394 0 59.17 40.82 0.00 752326 494294 60.34 39.65 imrs_cache tpcc dbo new_order 6215685 0 3471566 64.16 0.00 35.83 0 0 NULL NULL imrs_cache tpcc dbo customer 0 7831278 0 0.00 100.00 0.00 1196224 548921 68.54 31.45 imrs_cache tpcc dbo warehouse 0 6245389 0 0.00 100.00 0.00 145 95 60.41 39.58 imrs_cache tpcc dbo history 6020729 0 0 100.00 0.00 0.00 0 0 NULL NULL imrs_cache tpcc dbo item 0 0 0 NULL NULL NULL 0 98992 0.00 100.00 (1 row affected) Total number of rows: 10 ILM Effectiveness Metrics: <Op>Of<RowType>: Columns below indicate effectiveness of IMRS for <Op> affecting <RowType> Example: Column 'UpdOfMig' indicates effectiveness of IMRS for updates of migrated rows. CacheName DBName OwnerName ObjectName Score SelOfIns UpdOfIns DelOfIns SelOfMig UpdOfMig DelOfMig SelOfCached UpdOfCached DelOfCached ---------- ------ --------- ---------- --------- -------- -------- -------- -------- -------- -------- ----------- ----------- ----------- imrs_cache tpcc dbo warehouse 105752.32 NULL NULL NULL 26881.76 26049.93 0.00 26840.13 25980.50 0.00 imrs_cache tpcc dbo district 11030.40 NULL NULL NULL 241.89 5252.02 0.00 243.01 5293.48 0.00 imrs_cache tpcc dbo item 649.72 NULL NULL NULL NULL NULL NULL 649.72 0.00 0.00 imrs_cache tpcc [Any] [Totals] 49.42 0.08 0.47 0.04 0.63 4.61 0.00 39.81 3.78 0.00 imrs_cache tpcc dbo customer 15.25 NULL NULL NULL 1.53 3.78 0.00 3.93 6.01 0.00 imrs_cache tpcc dbo stock 10.91 NULL NULL NULL 0.57 6.02 0.00 1.00 3.32 0.00 imrs_cache tpcc dbo orders 2.22 0.22 0.55 0.00 0.07 1.00 0.00 0.26 0.12 0.00 imrs_cache tpcc dbo order_line 2.16 0.09 0.55 0.00 0.03 1.00 0.00 0.23 0.26 0.00 imrs_cache tpcc dbo new_order 0.55 0.00 0.00 0.55 NULL NULL NULL NULL NULL NULL imrs_cache tpcc dbo history 0.00 0.00 0.00 0.00 NULL NULL NULL NULL NULL NULL (1 row affected) Total number of rows: 10 </strong><strong> Execute: sp_imrs show, pack_metrics, imrs_cache </strong><strong> Row counts for pack subsystem: CacheName DBName OwnerName ObjectName NRows InsPct MigPct CachedPct NDRPackPct NSteadyPct NAggrPct SkipRatio NSkipped HotRowsPct NoLockPct GT1VersPct OtherPct ---------- ------ --------- ---------- ------- ------ ------ --------- ---------- ---------- -------- --------- -------- ---------- --------- ---------- -------- imrs_cache tpcc dbo new_order 3471493 100.00 0.00 0.00 100.00 0.00 0.00 0 0 NULL NULL NULL NULL imrs_cache tpcc [Any] [Totals] 3471493 100.00 0.00 0.00 100.00 0.00 0.00 0 0 NULL NULL NULL NULL imrs_cache tpcc dbo item 0 NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL imrs_cache tpcc dbo stock 0 NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL imrs_cache tpcc dbo orders 0 NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL imrs_cache tpcc dbo history 0 NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL imrs_cache tpcc dbo customer 0 NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL imrs_cache tpcc dbo district 0 NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL imrs_cache tpcc dbo warehouse 0 NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL imrs_cache tpcc dbo order_line 0 NULL NULL NULL NULL NULL NULL NULL 0 NULL NULL NULL NULL (1 row affected) Total number of rows: 10 Memory related metrics for pack subsystem: CacheName DBName OwnerName ObjectName LastPacked LastVisited PackedMB InsMBPct MigMBPct CachedMBPct MemOvhdMB MemOvhdPct ---------- ------ --------- ---------- ------------------- ------------------- -------- -------- -------- ----------- --------- ---------- imrs_cache tpcc dbo new_order Jan 20 2017 1:45AM Jan 20 2017 1:45AM 848 100.00 0.00 0.00 370 43.70 imrs_cache tpcc [Any] [Totals] Jan 20 2017 1:45AM Jan 20 2017 1:45AM 848 100.00 0.00 0.00 370 43.70 imrs_cache tpcc dbo item NULL NULL 0 NULL NULL NULL 0 NULL imrs_cache tpcc dbo stock NULL NULL 0 NULL NULL NULL 0 NULL imrs_cache tpcc dbo orders NULL NULL 0 NULL NULL NULL 0 NULL imrs_cache tpcc dbo history NULL NULL 0 NULL NULL NULL 0 NULL imrs_cache tpcc dbo customer NULL NULL 0 NULL NULL NULL 0 NULL imrs_cache tpcc dbo district NULL NULL 0 NULL NULL NULL 0 NULL imrs_cache tpcc dbo warehouse NULL NULL 0 NULL NULL NULL 0 NULL imrs_cache tpcc dbo order_line NULL NULL 0 NULL NULL NULL 0 NULL (1 row affected) Total number of rows: 10 Pack efficiency and transaction metrics for pack subsystem: CacheName DBName OwnerName ObjectName SkipRatio MemOvhdPct NCTrans DRTPct SteadyTPct AggrTPct NRBTrans MBPerTran NRowsPerTran NDRowsPerTran NStRowsPerTran NAgRowsPerTran ---------- ------ --------- ---------- --------- ---------- ------- ------ ---------- -------- -------- --------- ------------ ------------- -------------- -------------- imrs_cache tpcc dbo new_order 0 43.70 39998 100.00 0.00 0.00 17384 0 86 86 NULL NULL imrs_cache tpcc [Any] [Totals] 0 43.70 39998 100.00 0.00 0.00 17384 0 86 86 NULL NULL imrs_cache tpcc dbo item NULL NULL 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL imrs_cache tpcc dbo stock NULL NULL 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL imrs_cache tpcc dbo orders NULL NULL 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL imrs_cache tpcc dbo history NULL NULL 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL imrs_cache tpcc dbo customer NULL NULL 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL imrs_cache tpcc dbo district NULL NULL 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL imrs_cache tpcc dbo warehouse NULL NULL 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL imrs_cache tpcc dbo order_line NULL NULL 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL (1 row affected) Total number of rows: 10 </strong><strong> Execute: sp_imrs show, sysimrslogs, imrs_cache </strong>** Description Value Comment --------------------------------------------------------- -------- ------------------------ Total number of pages in imrslogsegment 16000000 250000.00 MB Total number of pages in use 3597587 56212.30 MB ( 22.48 %) Number of non-truncatable pages 3597587 56212.30 MB ( 22.48 %) Number of truncatable pages 0 0.00 MB ( 0.00 %) Total number of free pages of imrslogsegment 12339912 192811.13 MB ( 77.12 %) First page ID of sysimrslogs 9600001 Last page ID of sysimrslogs 13211696 Page ID of oldest non-truncatable page 9600001 Percentage of active space that can be freed by DUMP TRAN 0 Number of pages reserved for Last-Chance Threshold (LCT) 800000 12500.00 MB ( 5.00 %) (10 rows affected)示例 20
显示有关碎片收集器的统计信息：
sp_imrs show, gcstats DBName Type NWakeups MemFreedMB NVersFreed NTransFreed NStmtsFreed NSversPgsDeallocs ------------------ ------------ ---------------- -------------------- -------------------- ---------------------- ---------------------- ---------------------------------- btrim_db imrsgc 0 0.00 0 0 0 0 btrim_db imrsgc 106 0.00 1 3 0 1 btrim_db1 imrsgc 1 0.00 0 0 0 0 btrim_db1 imrsgc 6 0.00 2 2 0 1 (4 rows affected) Total number of rows: 4 DBName Type NWakeups MemFreedMB NVersFreed NTransFreed NStmtsFreed NSversPgsDeallocs ------------------ ---------- ---------------- -------------------- -------------------- ---------------------- ---------------------- ---------------------------------- btrim_db lobgc 0 0.00 0 0 0 0 btrim_db lobgc 0 0.00 0 0 0 0 btrim_db1 lobgc 0 0.00 0 0 0 0 btrim_db1 lobgc 0 0.00 0 0 0 0 (4 rows affected) Total number of rows: 4示例 21
显示有关碎片收集器的信息：
sp_imrs show, gcinfo DBName Type Status WaitStatus ExitStatus LastWakeup BlockingSPID BlockingTime BlockingSPIDType NTranPending NTranBlocked ------------------ ------------ ---------------- -------------------- -------------------- -------------------------------------- ------------------------ ------------------------ -------------------------------- ------------------------ ------------------------ btrim_db lobgc sleeping sleeping alive Jan 1 1900 12:00AM 0 0 NULL 0 0 btrim_db lobgc sleeping sleeping alive Jan 1 1900 12:00AM 0 0 NULL 0 0 btrim_db imrsgc sleeping sleeping alive Jan 1 1900 12:00AM 0 0 NULL 0 0 btrim_db imrsgc sleeping sleeping alive Feb 27 2017 1:30AM 0 0 NULL 0 0 btrim_db1 lobgc sleeping sleeping alive Jan 1 1900 12:00AM 0 0 NULL 0 0 btrim_db1 lobgc sleeping sleeping alive Jan 1 1900 12:00AM 0 0 NULL 0 0 btrim_db1 imrsgc sleeping sleeping alive Feb 22 2017 1:14AM 0 0 NULL 0 0 btrim_db1 imrsgc sleeping sleeping alive Feb 22 2017 1:14AM 0 0 NULL 0 0 (8 rows affected) Total number of rows: 8示例 22
显示有关碎片收集器的统计信息：
sp_imrs show, versionstats Row version statistics: CacheName DBName NRows NRowsHWM NVersions NVersHWM OldVerMB OldVerHWM LtstVerMB LtstVerWHM NOldLOBVer NOldLOBVerHWM OldLOBVerMB OldLOBVerHWM NSvActivePgs NSvActivePgsHWM ------------------------ ------------------ ---------- ---------------- ------------------ ---------------- ---------------- ------------------ ------------------ -------------------- -------------------- -------------------------- ---------------------- ------------------------ ------------------------ ------------------------------ btrim_cache btrim_db 3 3 0 0 0.00 0.00 0.00 0.00 0 0 0.00 0.00 0 0 btrim_cache1 btrim_db1 4 4 0 0 0.00 0.00 0.00 0.00 0 0 0.00 0.00 0 0 (2 rows affected) Total number of rows: 2