Dynamic Management Views and Functions

Dynamic management views (DMVs) and functions (DMFs) return server state information that can be used to observe active processes and therefore to tune system performance or to monitor the actual system state. In contrast to catalog views, the DMVs and DMFs are based on internal structures of the system.

DMVs and DMFs belong to the sys schema and their names start with the prefix dm_, followed by a text string that indicates the category to which the particular DMV or DMF belongs.

The following list identifies and describes some of these categories:

• sys.dm_db_* Contains information about databases and their objects
• sys.dm_tran_* Contains information in relation to transactions
• sys.dm_io_* Contains information about I/O activities
• sys.dm_exec_* Contains information related to the execution of user code

This section introduces two new DMVs:

• sys.dm_exec_describe_first_result_set
• sys.dm_db_uncontained_entities

The sys.dm_exec_describe_first_result_set view describes the first result set of a group of result sets. For this reason, you can apply this DMV when several subsequent queries are declared in a batch or a stored procedure (see Example 9.4). The sys.dm_db_uncontained_entities view shows any uncontained objects used in the database. (Uncontained objects are objects that cross the application boundary in a contained database. For the description of uncontained objects and the application boundary, see the sub section “Contained Databases” in section “Altering a Database“.)

USE sample;
GO
CREATE PROC TwoSELECTS
AS
SELECT emp_no, job from works_on where emp_no BETWEEN 1000 and 9999;
SELECT emp_no, emp_lname FROM employee where emp_fname LIKE 'S%';
GO
SELECT is_hidden hidden ,column_ordinal ord,
		name, is_nullable nul, system_type_id id
	FROM sys.dm_exec_describe_first_result_set ('TwoSELECTS', NULL, 0) ;Code language: PHP (php)

The result is

The stored procedure in Example 9.4 contains two SELECT statements concerning the sample database. The subsequent query uses the sys.dm_exec_describe_first_result_set view to display several properties of the result set of the first query.