Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
SQL database in Microsoft Fabric
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.
Query dynamic management objects
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 Transact-SQL:
| 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 |
Remarks
Dynamic management objects return internal, implementation-specific state data. Their schemas and the data they return might change in future Database Engine releases. Therefore, dynamic management objects in future releases might not be compatible with the dynamic management objects in this release.
For example, in future Database Engine 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.
Permissions
Querying a dynamic management object requires SELECT permission on the object, and depends on the object's scope and the SQL Server Database Engine version:
| Version | Server scope | Database scope |
|---|---|---|
| SQL Server 2019 (15.x) and earlier versions | VIEW SERVER STATE |
VIEW DATABASE STATE |
| SQL Server 2022 (16.x) 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.