System Dynamic Management Views
In this tutorial we will learn about System Dynamic Management Views.
However, Dynamic management views and functions return server state information used for monitoring the health of a server instance, diagnose problems, and tune performance. Their schemas and the data they return may change in future releases of SQL Server. Therefore, dynamic management views and functions in future releases may not be compatible with the dynamic management views and functions in this release. For example, in future releases of SQL Server, Microsoft may augment the definition of any dynamic management view by adding columns to the end of the column list.
Further, there are two types of dynamic management views and functions:
- Firstly, Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server.
- Secondly, Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database.
Querying Dynamic Management Views
Dynamic management views can be a reference in Transact-SQL statements by using two-part, three-part, or four-part names. On the other hand, dynamic management functions can be referenced in Transact-SQL statements by using either two-part or three-part names. Further, dynamic management views and functions cannot be referenced in Transact-SQL statements by using one-part names.
However, all dynamic management views and functions exist in the sys schema and follow this naming convention dm_*. And, when you use a dynamic management view or function then, you must prefix the name of the view or function by using the sys schema. For example, to query the dm_os_wait_stats dynamic management view, run the following query:
SQL
SELECT wait_type, wait_time_ms
FROM sys.dm_os_wait_stats;
Required Permissions
- Firstly, to query a dynamic management view or function requires SELECT permission on object and VIEW SERVER STATE or VIEW DATABASE STATE permission. However, this lets you selectively restrict access of a user or login to dynamic management views and functions.
- Secondly, 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.
Reference: Microsoft Documentation