| title | System Dynamic Management Views and Functions (Transact-SQL) | |||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| description | 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. | |||||||||||||||||
| author | rwestMSFT | |||||||||||||||||
| ms.author | randolphwest | |||||||||||||||||
| ms.date | 07/07/2026 | |||||||||||||||||
| ms.service | sql | |||||||||||||||||
| ms.subservice | system-objects | |||||||||||||||||
| ms.topic | reference | |||||||||||||||||
| ms.custom |
|
|||||||||||||||||
| helpviewer_keywords |
|
|||||||||||||||||
| dev_langs |
|
|||||||||||||||||
| monikerRange | >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric || =fabric-sqldb |
[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw-fabricsqldb]
Dynamic management objects include dynamic management views (DMVs) and dynamic management functions (DMFs). These objects return server state information that you can use to monitor the health of a server instance, diagnose problems, and tune performance.
Dynamic management objects are either server-scoped or database-scoped.
All dynamic management objects exist in the sys schema and follow the naming convention dm_*.
When you use a dynamic management object, you must prefix the name of the object by using the sys schema. 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;The following table describes how to reference dynamic management objects in [!INCLUDE tsql]:
| T-SQL reference | Dynamic management view | Dynamic management function |
|---|---|---|
| One-part naming | No | No |
| Two-part naming | Yes | Yes |
| Three-part naming | Yes | Yes |
| Four-part naming | Yes | No |
Dynamic management objects return internal, implementation-specific state data. Their schemas and the data they return might change in future [!INCLUDE ssde-md] releases. Therefore, dynamic management objects in future releases might not be compatible with the dynamic management objects in this release.
For example, in future [!INCLUDE ssde-md] releases, Microsoft might augment the definition of any dynamic management view by adding columns to the end of the column list. Don't use the syntax SELECT * FROM dynamic_management_view_name in production code because the number of columns returned might change and break your application.
Querying a dynamic management object requires SELECT permission on the object, and depends on the object's scope and the [!INCLUDE ssdenoversion-md] version:
| Version | Server scope | Database scope |
|---|---|---|
| [!INCLUDE sssql19-md] and earlier versions | VIEW SERVER STATE |
VIEW DATABASE STATE |
| [!INCLUDE sssql22-md] and later versions | VIEW SERVER PERFORMANCE STATE, or VIEW SERVER SECURITY STATE for security-related objects |
VIEW DATABASE PERFORMANCE STATE, or VIEW DATABASE SECURITY STATE for security-related objects |
To selectively restrict access, create the user or login in master and then deny that user SELECT permission on the specific dynamic management views or functions you want to block. The user can't select from those objects afterward, regardless of database context.
Note
DENY takes precedence over GRANT. For example, a user granted VIEW SERVER PERFORMANCE STATE but denied VIEW DATABASE PERFORMANCE STATE can see server-level information but not database-level information.