系统动态管理视图System Dynamic Management Views

适用对象: 是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

动态管理视图和函数返回可用于监视服务器实例的运行状况、诊断故障以及优化性能的服务器状态信息。Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.

重要

动态管理视图和函数返回特定于实现的内部状态数据。Dynamic management views and functions return internal, implementation-specific state data. 在未来的 SQL ServerSQL Server 版本中,它们的架构和返回的数据可能会发生更改。Their schemas and the data they return may change in future releases of SQL ServerSQL Server. 因此,未来版本中的动态管理视图和函数可能与此版本中的动态管理视图和函数不兼容。Therefore, dynamic management views and functions in future releases may not be compatible with the dynamic management views and functions in this release. 例如,在 SQL ServerSQL Server 的未来版本中,Microsoft 可能会通过在列列表的末尾添加列来扩充任何动态管理视图的定义。For example, in future releases of SQL ServerSQL Server, Microsoft may augment the definition of any dynamic management view by adding columns to the end of the column list. 我们建议不要在生产代码中使用语法 SELECT * FROM dynamic_management_view_name,这是因为返回的列数可能会更改和中断应用程序。We recommend against using the syntax SELECT * FROM dynamic_management_view_name in production code because the number of columns returned might change and break your application.

动态管理视图和函数分为两种类型:There are two types of dynamic management views and functions:

  • 服务器范围内的动态管理视图和函数。Server-scoped dynamic management views and functions. 此类型需要具有该服务器的 VIEW SERVER STATE 权限。These require VIEW SERVER STATE permission on the server.

  • 数据库范围内的动态管理视图和函数。Database-scoped dynamic management views and functions. 此类型需要具有该数据库的 VIEW DATABASE STATE 权限。These require VIEW DATABASE STATE permission on the database.

查询动态管理视图Querying Dynamic Management Views

通过使用两部分、三部分或四部分所组成的名称,可在 Transact-SQLTransact-SQL 语句中引用动态管理视图。Dynamic management views can be referenced in Transact-SQLTransact-SQL statements by using two-part, three-part, or four-part names. 另一方面,也可使用两部分或三部分所组成的名称在 Transact-SQLTransact-SQL 语句中引用动态管理函数。Dynamic management functions on the other hand can be referenced in Transact-SQLTransact-SQL statements by using either two-part or three-part names. 不能使用只由一部分组成的名称在 Transact-SQLTransact-SQL 语句中引用动态管理视图和函数。Dynamic management views and functions cannot be referenced in Transact-SQLTransact-SQL statements by using one-part names.

所有动态管理视图和函数都存在于 sys 架构中,并遵循 dm_* 命名约定。All dynamic management views and functions exist in the sys schema and follow this naming convention dm_*. 当使用动态管理视图或函数时,必须使用 sys 架构作为视图或函数名称的前缀。When you use a dynamic management view or function, you must prefix the name of the view or function by using the sys schema. 例如,若要查询 dm_os_wait_stats 动态管理视图,请运行以下查询:For example, to query the dm_os_wait_stats dynamic management view, run the following query:

SELECT wait_type, wait_time_ms  
FROM sys.dm_os_wait_stats;  

所需的权限Required Permissions

查询动态管理视图或函数需要对于对象具有 SELECT 权限以及 VIEW SERVER STATE 或 VIEW DATABASE STATE 权限。To query a dynamic management view or function requires SELECT permission on object and VIEW SERVER STATE or VIEW DATABASE STATE permission. 这样您可以有选择地限制用户或登录名对动态管理视图和函数的访问。This lets you selectively restrict access of a user or login to dynamic management views and functions. 为此,首先在 master 中创建用户,然后拒绝该用户对不希望被访问的动态管理视图或函数的 SELECT 权限。To do this, first create the user in master and then deny the user SELECT permission on the dynamic management views or functions that you do not want them to access. 此后,无论该用户的数据库上下文如何,用户都将无法选择这些动态管理视图或函数。After this, the user cannot select from these dynamic management views or functions, regardless of database context of the user.

备注

由于 DENY 的优先级高,所以如果用户被授予 VIEW SERVER STATE 权限但被拒绝 VIEW DATABASE STATE 权限,则该用户只能查看服务器级别信息,但不能查看数据库级别信息。Because DENY takes precedence, if a user has been granted VIEW SERVER STATE permissions but denied VIEW DATABASE STATE permission, the user can see server-level information, but not database-level information.

本节内容In This Section

动态管理视图和函数划分为以下类别。Dynamic management views and functions have been organized into the following categories.

Always On 可用性组动态管理视图和函数 (Transact SQL)Always On Availability Groups Dynamic Management Views and Functions (Transact-SQL) 内存优化表动态管理视图(Transact SQL)Memory-Optimized Table Dynamic Management Views (Transact-SQL)
与变更数据捕获相关的动态管理视图 (Transact-SQL)Change Data Capture Related Dynamic Management Views (Transact-SQL) 对象与相关的动态管理视图和函数(Transact SQL)Object Related Dynamic Management Views and Functions (Transact-SQL)
与更改跟踪相关的动态管理视图Change Tracking Related Dynamic Management Views 查询通知相关的动态管理视图(Transact SQL)Query Notifications Related Dynamic Management Views (Transact-SQL)
公共语言运行时与相关的动态管理视图(Transact SQL)Common Language Runtime Related Dynamic Management Views (Transact-SQL) 与复制相关的动态管理视图(Transact SQL)Replication Related Dynamic Management Views (Transact-SQL)
数据库镜像相关的动态管理视图(Transact SQL)Database Mirroring Related Dynamic Management Views (Transact-SQL) 与资源调控器相关的动态管理视图(Transact SQL)Resource Governor Related Dynamic Management Views (Transact-SQL)
与数据库相关的动态管理视图(Transact SQL)Database Related Dynamic Management Views (Transact-SQL) 与安全性相关的动态管理视图和函数 (Transact-SQL)Security-Related Dynamic Management Views and Functions (Transact-SQL)
与执行相关的动态管理视图和函数 (Transact-SQL)Execution Related Dynamic Management Views and Functions (Transact-SQL) 与服务器相关的动态管理视图和函数 (Transact-SQL)Server-Related Dynamic Management Views and Functions (Transact-SQL)
扩展事件动态管理视图Extended Events Dynamic Management Views 与 Service Broker 有关的动态管理视图 (Transact-SQL)Service Broker Related Dynamic Management Views (Transact-SQL)
Filestream 和 FileTable 动态管理视图(Transact SQL)Filestream and FileTable Dynamic Management Views (Transact-SQL) 空间数据相关的动态管理视图和函数(Transact SQL)Spatial Data Related Dynamic Management Views and Functions (Transact-SQL)
全文搜索和语义搜索动态管理视图和函数(Transact SQL)Full-Text Search and Semantic Search Dynamic Management Views and Functions (Transact-SQL) SQL 数据仓库和并行数据仓库动态管理视图(Transact SQL)SQL Data Warehouse and Parallel Data Warehouse Dynamic Management Views (Transact-SQL)
异地复制动态管理视图和函数(Azure SQL 数据库)Geo-Replication Dynamic Management Views and Functions (Azure SQL Database) 与 SQL Server 操作系统相关的动态管理视图(Transact SQL)SQL Server Operating System Related Dynamic Management Views (Transact-SQL)
与索引相关的动态管理视图和函数(Transact SQL)Index Related Dynamic Management Views and Functions (Transact-SQL) Stretch Database 的动态管理视图(Transact SQL)Stretch Database Dynamic Management Views (Transact-SQL)
我 O 相关动态管理视图和函数(Transact SQL)I O Related Dynamic Management Views and Functions (Transact-SQL) 与事务相关的动态管理视图和函数 (Transact-SQL)Transaction Related Dynamic Management Views and Functions (Transact-SQL)

请参阅See Also

GRANT 服务器权限(Transact SQL) GRANT Server Permissions (Transact-SQL)
GRANT 数据库权限 (Transact-SQL) GRANT Database Permissions (Transact-SQL)
系统视图(Transact SQL)System Views (Transact-SQL)