sys.dm_db_index_operational_stats (Transact-SQL)sys.dm_db_index_operational_stats (Transact-SQL)

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

返回数据库中的当前低级 I/O、 锁定、 闩锁,和每个分区的表或索引访问方法活动。Returns current lower-level I/O, locking, latching, and access method activity for each partition of a table or index in the database.

内存优化索引不会出现在此 DMV 中。Memory-optimized indexes do not appear in this DMV.

备注

sys.dm_db_index_operational_stats不返回有关内存优化索引的信息。sys.dm_db_index_operational_stats does not return information about memory-optimized indexes. 有关内存优化索引中使用的信息,请参阅sys.dm_db_xtp_index_stats (TRANSACT-SQL)For information about memory-optimized index use, see sys.dm_db_xtp_index_stats (Transact-SQL).

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

    
sys.dm_db_index_operational_stats (    
    { database_id | NULL | 0 | DEFAULT }    
  , { object_id | NULL | 0 | DEFAULT }    
  , { index_id | 0 | NULL | -1 | DEFAULT }    
  , { partition_number | NULL | 0 | DEFAULT }    
)    

参数Arguments

database_id |NULL |0 |默认值database_id | NULL | 0 | DEFAULT
数据库 ID。ID of the database. database_idsmallintdatabase_id is smallint. 有效的输入包括数据库的 ID 号、NULL、0 或 DEFAULT。Valid inputs are the ID number of a database, NULL, 0, or DEFAULT. 默认值为 0。The default is 0. 在此上下文中,NULL、0 和 DEFAULT 是等效值。NULL, 0, and DEFAULT are equivalent values in this context.

指定 NULL 可返回 SQL ServerSQL Server 实例中所有数据库的信息。Specify NULL to return information for all databases in the instance of SQL ServerSQL Server. 如果指定为空, database_id,则还必须指定为 NULL object_idindex_id,以及partition_numberIf you specify NULL for database_id, you must also specify NULL for object_id, index_id, and partition_number.

内置函数DB_ID可以指定。The built-in function DB_ID can be specified.

object_id | NULL | 0 | DEFAULTobject_id | NULL | 0 | DEFAULT
索引所基于的表或视图的对象 ID。Object ID of the table or view the index is on. object_idintobject_id is int.

有效的输入包括表和视图的 ID 号、NULL、0 或 DEFAULT。Valid inputs are the ID number of a table and view, NULL, 0, or DEFAULT. 默认值为 0。The default is 0. 在此上下文中,NULL、0 和 DEFAULT 是等效值。NULL, 0, and DEFAULT are equivalent values in this context.

指定 NULL 可返回指定数据库中的所有表和视图的缓存信息。Specify NULL to return cached information for all tables and views in the specified database. 如果指定为空, object_id,则还必须指定为 NULL index_idpartition_numberIf you specify NULL for object_id, you must also specify NULL for index_id and partition_number.

index_id | 0 | NULL | -1 | DEFAULTindex_id | 0 | NULL | -1 | DEFAULT
索引的 ID。ID of the index. index_idint。有效输入包括索引 0 的 ID 号,如果object_id是一个堆,NULL,-1 或默认值。index_id is int. Valid inputs are the ID number of an index, 0 if object_id is a heap, NULL, -1, or DEFAULT. 默认值为 -1。在此上下文中,NULL、-1 和 DEFAULT 是等价值。The default is -1, NULL, -1, and DEFAULT are equivalent values in this context.

指定 NULL 可返回基表或视图的所有索引的缓存信息。Specify NULL to return cached information for all indexes for a base table or view. 如果指定为空, index_id,则还必须指定为 NULL partition_numberIf you specify NULL for index_id, you must also specify NULL for partition_number.

partition_number |NULL |0 |默认值partition_number | NULL | 0 | DEFAULT
对象中的分区号。Partition number in the object. partition_numberint。有效输入包括partion_number索引或堆中,NULL、 0 或 DEFAULT。partition_number is int. Valid inputs are the partion_number of an index or heap, NULL, 0, or DEFAULT. 默认值为 0。The default is 0. 在此上下文中,NULL、0 和 DEFAULT 是等效值。NULL, 0, and DEFAULT are equivalent values in this context.

指定 NULL 可返回索引或堆的所有分区的缓存信息。Specify NULL to return cached information for all partitions of the index or heap.

partition_number 1 开始。partition_number is 1-based. 未分区的索引或堆partition_number设置为 1。A nonpartitioned index or heap has partition_number set to 1.

返回的表Table Returned

列名Column name 数据类型Data type 描述Description
database_iddatabase_id smallintsmallint 数据库 ID。Database ID.
object_idobject_id intint 表或视图的 ID。ID of the table or view.
index_idindex_id intint 索引或堆的 ID。ID of the index or heap.

0 = 堆0 = Heap
partition_numberpartition_number intint 索引或堆中从 1 开始的分区号。1-based partition number within the index or heap.
hobt_idhobt_id bigintbigint 适用范围SQL ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x)当前版本)、 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version), Azure SQL DatabaseAzure SQL Database.

数据堆或 B 树行集跟踪对于列存储索引的内部数据的 ID。ID of the data heap or B-tree rowset that tracks internal data for a columnstore index.

NULL-这不是内部的列存储行集。NULL - this is not an internal columnstore rowset.

有关更多详细信息,请参阅sys.internal_partitions (TRANSACT-SQL)For more details, see sys.internal_partitions (Transact-SQL)
leaf_insert_countleaf_insert_count bigintbigint 叶级插入的累积计数。Cumulative count of leaf-level inserts.
leaf_delete_countleaf_delete_count bigintbigint 叶级删除的累积计数。Cumulative count of leaf-level deletes. leaf_delete_count 才会递增第一次未标记为虚影的已删除的记录。leaf_delete_count is only incremented for deleted records that are not marked as ghost first. 首先,幻像的已删除的记录leaf_ghost_count而递增。For deleted records that are ghosted first, leaf_ghost_count is incremented instead.
leaf_update_countleaf_update_count bigintbigint 叶级更新的累积计数。Cumulative count of leaf-level updates.
leaf_ghost_countleaf_ghost_count bigintbigint 标记为删除但尚未删除的叶级行的累积计数。Cumulative count of leaf-level rows that are marked as deleted, but not yet removed. 此计数不包括不标记为虚影的情况下立即删除的记录。This count does not include records that are immediately deleted without being marked as ghost. 清除线程会按设置的间隔删除这些行。These rows are removed by a cleanup thread at set intervals. 此值不包括保留,因为未完成的快照隔离事务的行。This value does not include rows that are retained, because of an outstanding snapshot isolation transaction.
nonleaf_insert_countnonleaf_insert_count bigintbigint 叶级以上的插入累积计数。Cumulative count of inserts above the leaf level.

0 = 堆或列存储0 = Heap or columnstore
nonleaf_delete_countnonleaf_delete_count bigintbigint 叶级以上的删除累积计数。Cumulative count of deletes above the leaf level.

0 = 堆或列存储0 = Heap or columnstore
nonleaf_update_countnonleaf_update_count bigintbigint 叶级以上的更新累积计数。Cumulative count of updates above the leaf level.

0 = 堆或列存储0 = Heap or columnstore
leaf_allocation_countleaf_allocation_count bigintbigint 索引或堆中的叶级页分配的累积计数。Cumulative count of leaf-level page allocations in the index or heap.

对于索引,页分配与页拆分对应。For an index, a page allocation corresponds to a page split.
nonleaf_allocation_countnonleaf_allocation_count bigintbigint 叶级以上由页拆分引起的页分配的累积计数。Cumulative count of page allocations caused by page splits above the leaf level.

0 = 堆或列存储0 = Heap or columnstore
leaf_page_merge_countleaf_page_merge_count bigintbigint 叶级页合并的累积计数。Cumulative count of page merges at the leaf level. 始终为 0 的列存储索引。Always 0 for columnstore index.
nonleaf_page_merge_countnonleaf_page_merge_count bigintbigint 叶级以上页合并的累积计数。Cumulative count of page merges above the leaf level.

0 = 堆或列存储0 = Heap or columnstore
range_scan_countrange_scan_count bigintbigint 从索引或堆开始的范围和表扫描的累积计数。Cumulative count of range and table scans started on the index or heap.
singleton_lookup_countsingleton_lookup_count bigintbigint 对索引或堆的单行检索的累积计数。Cumulative count of single row retrievals from the index or heap.
forwarded_fetch_countforwarded_fetch_count bigintbigint 通过前推记录提取的行计数。Count of rows that were fetched through a forwarding record.

0 = 索引0 = Indexes
lob_fetch_in_pageslob_fetch_in_pages bigintbigint 从 LOB_DATA 分配单元检索到的大型对象 (LOB) 页的累积计数。Cumulative count of large object (LOB) pages retrieved from the LOB_DATA allocation unit. 这些页包含的类型列中存储的数据文本ntext图像varchar (max)nvarchar (max)varbinary (max) ,和xmlThese pages contain data that is stored in columns of type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), and xml. 有关详细信息,请参阅数据类型 (Transact-SQL)For more information, see Data Types (Transact-SQL).
lob_fetch_in_byteslob_fetch_in_bytes bigintbigint 检索到的 LOB 数据字节数的累积计数。Cumulative count of LOB data bytes retrieved.
lob_orphan_create_countlob_orphan_create_count bigintbigint 为大容量操作创建的孤立 LOB 值的累积计数。Cumulative count of orphan LOB values created for bulk operations.

0 = 非聚集索引0 = Nonclustered index
lob_orphan_insert_countlob_orphan_insert_count bigintbigint 大容量操作期间插入的孤立 LOB 值的累积计数。Cumulative count of orphan LOB values inserted during bulk operations.

0 = 非聚集索引0 = Nonclustered index
row_overflow_fetch_in_pagesrow_overflow_fetch_in_pages bigintbigint 从 ROW_OVERFLOW_DATA 分配单元检索到的行溢出数据页数的累积计数。Cumulative count of row-overflow data pages retrieved from the ROW_OVERFLOW_DATA allocation unit.

这些页包含类型的列中存储的数据varchar (n)nvarchar(n)varbinary (n) ,以及sql_variant已程序推送到行外。These pages contain data stored in columns of type varchar(n), nvarchar(n), varbinary(n), and sql_variant that has been pushed off-row.
row_overflow_fetch_in_bytesrow_overflow_fetch_in_bytes bigintbigint 检索到的行溢出数据字节数的累积计数。Cumulative count of row-overflow data bytes retrieved.
column_value_push_off_row_countcolumn_value_push_off_row_count bigintbigint 已推出行外以使插入或更新的行可容纳在页中的 LOB 数据和行溢出数据的列值累积计数。Cumulative count of column values for LOB data and row-overflow data that is pushed off-row to make an inserted or updated row fit within a page.
column_value_pull_in_row_countcolumn_value_pull_in_row_count bigintbigint 已请求到行内的 LOB 数据和行溢出数据的列值的累积计数。Cumulative count of column values for LOB data and row-overflow data that is pulled in-row. 当更新操作释放记录中的空间,并提供将一个或多个行外值从 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元请求到 IN_ROW_DATA 分配单元中的机会时,就会出现此计数。This occurs when an update operation frees up space in a record and provides an opportunity to pull in one or more off-row values from the LOB_DATA or ROW_OVERFLOW_DATA allocation units to the IN_ROW_DATA allocation unit.
row_lock_countrow_lock_count bigintbigint 请求的行锁的累积数量。Cumulative number of row locks requested.
row_lock_wait_countrow_lock_wait_count bigintbigint 数据库引擎Database Engine等待行锁的累积次数。Cumulative number of times the 数据库引擎Database Engine waited on a row lock.
row_lock_wait_in_msrow_lock_wait_in_ms bigintbigint 数据库引擎Database Engine等待行锁的总毫秒数。Total number of milliseconds the 数据库引擎Database Engine waited on a row lock.
page_lock_countpage_lock_count bigintbigint 请求的页锁的累积数量。Cumulative number of page locks requested.
page_lock_wait_countpage_lock_wait_count bigintbigint 数据库引擎Database Engine等待页锁的累积次数。Cumulative number of times the 数据库引擎Database Engine waited on a page lock.
page_lock_wait_in_mspage_lock_wait_in_ms bigintbigint 数据库引擎Database Engine等待页锁的总毫秒数。Total number of milliseconds the 数据库引擎Database Engine waited on a page lock.
index_lock_promotion_attempt_countindex_lock_promotion_attempt_count bigintbigint 数据库引擎Database Engine尝试升级锁的累积次数。Cumulative number of times the 数据库引擎Database Engine tried to escalate locks.
index_lock_promotion_countindex_lock_promotion_count bigintbigint 数据库引擎Database Engine升级锁的累积次数。Cumulative number of times the 数据库引擎Database Engine escalated locks.
page_latch_wait_countpage_latch_wait_count bigintbigint 数据库引擎Database Engine由于闩锁争用而等待的累积次数。Cumulative number of times the 数据库引擎Database Engine waited, because of latch contention.
page_latch_wait_in_mspage_latch_wait_in_ms bigintbigint 数据库引擎Database Engine由于闩锁争用而等待的累积毫秒数。Cumulative number of milliseconds the 数据库引擎Database Engine waited, because of latch contention.
page_io_latch_wait_countpage_io_latch_wait_count bigintbigint 数据库引擎Database Engine等待 I/O 页闩锁的累积次数。Cumulative number of times the 数据库引擎Database Engine waited on an I/O page latch.
page_io_latch_wait_in_mspage_io_latch_wait_in_ms bigintbigint 数据库引擎Database Engine等待页 I/O 闩锁的累积毫秒数。Cumulative number of milliseconds the 数据库引擎Database Engine waited on a page I/O latch.
tree_page_latch_wait_counttree_page_latch_wait_count bigintbigint 子集page_latch_wait_count包括仅别的 B 树页。Subset of page_latch_wait_count that includes only the upper-level B-tree pages. 对堆或列存储索引始终为 0。Always 0 for a heap or columnstore index.
tree_page_latch_wait_in_mstree_page_latch_wait_in_ms bigintbigint 子集page_latch_wait_in_ms包括仅别的 B 树页。Subset of page_latch_wait_in_ms that includes only the upper-level B-tree pages. 对堆或列存储索引始终为 0。Always 0 for a heap or columnstore index.
tree_page_io_latch_wait_counttree_page_io_latch_wait_count bigintbigint 子集page_io_latch_wait_count包括仅别的 B 树页。Subset of page_io_latch_wait_count that includes only the upper-level B-tree pages. 对堆或列存储索引始终为 0。Always 0 for a heap or columnstore index.
tree_page_io_latch_wait_in_mstree_page_io_latch_wait_in_ms bigintbigint 子集page_io_latch_wait_in_ms包括仅别的 B 树页。Subset of page_io_latch_wait_in_ms that includes only the upper-level B-tree pages. 对堆或列存储索引始终为 0。Always 0 for a heap or columnstore index.
page_compression_attempt_countpage_compression_attempt_count bigintbigint 对于表、索引或索引视图的特定分区,针对 PAGE 级压缩计算的页数。Number of pages that were evaluated for PAGE level compression for specific partitions of a table, index, or indexed view. 因为未能极大地节省空间,所以将包括未压缩的页。Includes pages that were not compressed because significant savings could not be achieved. 始终为 0 的列存储索引。Always 0 for columnstore index.
page_compression_success_countpage_compression_success_count bigintbigint 对于表、索引或索引视图的特定分区,使用 PAGE 压缩功能压缩的数据页数。Number of data pages that were compressed by using PAGE compression for specific partitions of a table, index, or indexed view. 始终为 0 的列存储索引。Always 0 for columnstore index.

备注Remarks

此动态管理对象不接受来自 CROSS APPLY 和 OUTER APPLY 的相关参数。This dynamic management object does not accept correlated parameters from CROSS APPLY and OUTER APPLY.

可以使用sys.dm_db_index_operational_stats来跟踪用户读取或写入到表、 索引或分区,并确定表或索引时遇到大型 I/O 活动或热而必须等待的时间长度点。You can use sys.dm_db_index_operational_stats to track the length of time that users must wait to read or write to a table, index, or partition, and identify the tables or indexes that are encountering significant I/O activity or hot spots.

使用以下各列可标识争用区。Use the following columns to identify areas of contention.

若要分析到表或索引分区的通用访问模式,使用这些列:To analyze a common access pattern to the table or index partition, use these columns:

  • leaf_insert_countleaf_insert_count

  • leaf_delete_countleaf_delete_count

  • leaf_update_countleaf_update_count

  • leaf_ghost_countleaf_ghost_count

  • range_scan_countrange_scan_count

  • singleton_lookup_countsingleton_lookup_count

若要标识闩锁和锁争用,请使用这些列:To identify latching and locking contention, use these columns:

  • page_latch_wait_countpage_latch_wait_in_mspage_latch_wait_count and page_latch_wait_in_ms

    这些列指示索引或堆上是否存在闩锁争用以及争用的意义。These columns indicate whether there is latch contention on the index or heap, and the significance of the contention.

  • row_lock_countpage_lock_countrow_lock_count and page_lock_count

    这些列指示数据库引擎Database Engine尝试获取行锁和页锁的次数。These columns indicate how many times the 数据库引擎Database Engine tried to acquire row and page locks.

  • row_lock_wait_in_mspage_lock_wait_in_msrow_lock_wait_in_ms and page_lock_wait_in_ms

    这些列指示索引或堆上是否存在锁争用以及争用的意义。These columns indicate whether there is lock contention on the index or heap, and the significance of the contention.

若要分析的物理 I/o 的索引或堆分区的统计信息To analyze statistics of physical I/Os on an index or heap partition

  • page_io_latch_wait_countpage_io_latch_wait_in_mspage_io_latch_wait_count and page_io_latch_wait_in_ms

    这些列指示是否已发出物理 I/O 以便将索引或堆页载入内存以及发出的 I/O 数。These columns indicate whether physical I/Os were issued to bring the index or heap pages into memory and how many I/Os were issued.

列备注Column Remarks

中的值lob_orphan_create_countlob_orphan_insert_count应始终为相等。The values in lob_orphan_create_count and lob_orphan_insert_count should always be equal.

列中的值lob_fetch_in_pageslob_fetch_in_bytes可以是大于零的非聚集索引,包含一个或多个 LOB 列作为包含性列。The value in the columns lob_fetch_in_pages and lob_fetch_in_bytes can be greater than zero for nonclustered indexes that contain one or more LOB columns as included columns. 有关详细信息,请参阅 Create Indexes with Included ColumnsFor more information, see Create Indexes with Included Columns. 同样,列中的值row_overflow_fetch_in_pagesrow_overflow_fetch_in_bytes索引包含可推送的列,则可以是大于 0 的非聚集索引行外。Similarly, the value in the columns row_overflow_fetch_in_pages and row_overflow_fetch_in_bytes can be greater than 0 for nonclustered indexes if the index contains columns that can be pushed off-row.

如何重置元数据缓存中的计数器How the Counters in the Metadata Cache Are Reset

返回的数据sys.dm_db_index_operational_stats存在仅当表示堆或索引的元数据缓存对象不可用。The data returned by sys.dm_db_index_operational_stats exists only as long as the metadata cache object that represents the heap or index is available. 此数据既不是持久性数据,也不是事务上一致的数据。This data is neither persistent nor transactionally consistent. 这意味着,不能使用这些计数器确定是否已使用索引,或确定上次使用索引的时间。This means you cannot use these counters to determine whether an index has been used or not, or when the index was last used. 有关此信息,请参阅sys.dm_db_index_usage_stats (TRANSACT-SQL)For information about this, see sys.dm_db_index_usage_stats (Transact-SQL).

只要堆或索引的元数据被载入元数据缓存,每列中的值就会被设置为零,且在从元数据缓存中删除缓存对象前会累积统计信息。The values for each column are set to zero whenever the metadata for the heap or index is brought into the metadata cache and statistics are accumulated until the cache object is removed from the metadata cache. 所以,活动堆或索引可能始终将其元数据放在缓存中,且累积计数可能反映自上次启动 SQL ServerSQL Server 以来的活动。Therefore, an active heap or index will likely always have its metadata in the cache, and the cumulative counts may reflect activity since the instance of SQL ServerSQL Server was last started. 活动较少的堆或索引的元数据将在使用时移入和移出缓存。The metadata for a less active heap or index will move in and out of the cache as it is used. 因此,它可能有、也可能没有可用值。As a result, it may or may not have values available. 删除索引将导致从内存中删除对应统计信息,且函数不再报告这些统计信息。Dropping an index will cause the corresponding statistics to be removed from memory and no longer be reported by the function. 对索引执行的其他 DDL 操作可能导致统计信息的值被重置为零。Other DDL operations against the index may cause the value of the statistics to be reset to zero.

使用系统函数指定参数值Using System Functions to Specify Parameter Values

可以使用Transact-SQLTransact-SQL函数DB_IDOBJECT_ID若要为指定值database_idobject_id参数。You can use the Transact-SQLTransact-SQL functions DB_ID and OBJECT_ID to specify a value for the database_id and object_id parameters. 但是,将无效的值传递给这些函数可能会导致意外结果。However, passing values that are not valid to these functions may cause unintended results. 请始终确保使用 DB_ID 或 OBJECT_ID 时返回了有效的 ID。Always make sure that a valid ID is returned when you use DB_ID or OBJECT_ID. 有关详细信息,请参阅中的备注部分sys.dm_db_index_physical_stats (TRANSACT-SQL)For more information, see the Remarks section in sys.dm_db_index_physical_stats (Transact-SQL).

权限Permissions

需要下列权限:Requires the following permissions:

  • 对数据库中的指定对象具有 CONTROL 权限CONTROL permission on the specified object within the database

  • VIEW DATABASE STATE 权限返回有关指定的数据库中的所有对象的信息通过使用对象通配符 @object_id = NULLVIEW DATABASE STATE permission to return information about all objects within the specified database, by using the object wildcard @object_id = NULL

  • VIEW SERVER STATE 权限,以返回有关所有数据库的信息通过使用数据库通配符 @database_id = NULLVIEW SERVER STATE permission to return information about all databases, by using the database wildcard @database_id = NULL

授予 VIEW DATABASE STATE 权限允许返回数据库中的所有对象,而不考虑对特定对象拒绝的任何 CONTROL 权限。Granting VIEW DATABASE STATE allows all objects in the database to be returned, regardless of any CONTROL permissions denied on specific objects.

拒绝 VIEW DATABASE STATE 将禁止返回数据库中的所有对象,而不管对特定对象授予的任何 CONTROL 权限。Denying VIEW DATABASE STATE disallows all objects in the database to be returned, regardless of any CONTROL permissions granted on specific objects. 此外,当数据库通配符 @database_id= 指定了 NULL,则省略数据库。Also, when the database wildcard @database_id=NULL is specified, the database is omitted.

有关详细信息,请参阅动态管理视图和函数(TRANSACT-SQL)For more information, see Dynamic Management Views and Functions (Transact-SQL).

示例Examples

A.A. 返回指定表的信息Returning information for a specified table

下面的示例返回 AdventureWorks2012AdventureWorks2012 数据库中 Person.Address 表的所有索引和分区的信息。The following example returns information for all indexes and partitions of the Person.Address table in the AdventureWorks2012AdventureWorks2012 database. 执行此查询至少需要对 Person.Address 表具有 CONTROL 权限。Executing this query requires, at a minimum, CONTROL permission on Person.Address table.

重要

在使用 Transact-SQLTransact-SQL 函数 DB_ID 和 OBJECT_ID 返回参数值时,请始终确保返回了有效的 ID。When you are using the Transact-SQLTransact-SQL functions DB_ID and OBJECT_ID to return a parameter value, always ensure that a valid ID is returned. 如果找不到数据库或对象的名称,例如相应名称不存在或拼写不正确,则两个函数都会返回 NULL。If the database or object name cannot be found, such as when they do not exist or are spelled incorrectly, both functions will return NULL. sys.dm_db_index_operational_stats 函数将 NULL 解释为指定所有数据库或所有对象的通配符值 。The sys.dm_db_index_operational_stats function interprets NULL as a wildcard value that specifies all databases or all objects. 由于这可能是无心之举,所以此部分中的示例说明了确定数据库 ID 和对象 ID 的安全方法。Because this can be an unintentional operation, the examples in this section demonstrate the safe way to determine database and object IDs.

DECLARE @db_id int;    
DECLARE @object_id int;    
SET @db_id = DB_ID(N'AdventureWorks2012');    
SET @object_id = OBJECT_ID(N'AdventureWorks2012.Person.Address');    
IF @db_id IS NULL     
  BEGIN;    
    PRINT N'Invalid database';    
  END;    
ELSE IF @object_id IS NULL    
  BEGIN;    
    PRINT N'Invalid object';    
  END;    
ELSE    
  BEGIN;    
    SELECT * FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL);    
  END;    
GO    
    

B.B. 返回所有表和索引的信息Returning information for all tables and indexes

下面的示例返回 SQL ServerSQL Server 实例中所有表和索引的信息。The following example returns information for all tables and indexes within the instance of SQL ServerSQL Server. 执行此查询需要 VIEW SERVER STATE 权限。Executing this query requires VIEW SERVER STATE permission.

SELECT * FROM sys.dm_db_index_operational_stats( NULL, NULL, NULL, NULL);    
GO    
    

请参阅See Also

动态管理视图和函数 (Transact-SQL) Dynamic Management Views and Functions (Transact-SQL)
与索引相关的动态管理视图和函数 (Transact-SQL) Index Related Dynamic Management Views and Functions (Transact-SQL)
监视和优化性能 Monitor and Tune for Performance
sys.dm_db_index_physical_stats (Transact-SQL) sys.dm_db_index_physical_stats (Transact-SQL)
sys.dm_db_index_usage_stats (Transact-SQL) sys.dm_db_index_usage_stats (Transact-SQL)
sys.dm_os_latch_stats (Transact-SQL) sys.dm_os_latch_stats (Transact-SQL)
sys.dm_db_partition_stats (TRANSACT-SQL) sys.dm_db_partition_stats (Transact-SQL)
sys.allocation_units (Transact-SQL) sys.allocation_units (Transact-SQL)
sys.indexes (Transact-SQL)sys.indexes (Transact-SQL)