Dynamic Management Views (DMVs)
Applies to: 
SQL Server Analysis Services
Azure Analysis Services
Fabric/Power BI Premium
Analysis Services Dynamic Management Views (DMVs) are queries that return information about model objects, server operations, and server health. The query, based on SQL, is an interface to schema rowsets. Schema rowsets are predescribed tables that contain information about Analysis Services objects and server state, including database schema, active sessions, connections, commands, and jobs that are executing on the server.
For Power BI Premium semantic models, DMVs for querying through the XMLA endpoint are limited to those that require database admin permissions. Some DMVs are not supported because they require Analysis Services server admin permissions.
DMV queries are an alternative to running XML/A Discover commands. For most administrators, writing a DMV query is simpler because the syntax is based on SQL. In addition, the result is returned in a table format that is easier to read and copy.
Most DMV queries use a SELECT statement and the $System schema with an XML/A schema rowset, for example:
SELECT * FROM $System.<schemaRowset>
DMV queries return information about server and object state at the time the query is run. To monitor operations in real-time, use tracing instead. To learn more about real-time monitoring using traces, see Use SQL Server Profiler to Monitor Analysis Services.
Query syntax
The query engine for DMVs is the Data Mining parser. The DMV query syntax is based on the SELECT (DMX) statement. Although DMV query syntax is based on a SQL SELECT statement, it does not support the full syntax of a SELECT statement. Notably, JOIN, GROUP BY, LIKE, CAST, and CONVERT are not supported.
SELECT [DISTINCT] [TOP <n>] <select list>
FROM $System.<schemaRowset>
[WHERE <condition expression>]
[ORDER BY <expression>[DESC|ASC]]
The following example for DISCOVER_CALC_DEPENDENCY illustrates the use of the WHERE clause for supplying a parameter to the query:
SELECT * FROM $System.DISCOVER_CALC_DEPENDENCY
WHERE OBJECT_TYPE = 'ACTIVE_RELATIONSHIP'
For schema rowsets that have restrictions, the query must include the SYSTEMRESTRICTSCHEMA function. The following example returns CSDL metadata about 1103 compatibility level tabular models. Note that CATALOG_NAME is case-sensitive:
Select * from SYSTEMRESTRICTSCHEMA ($System.Discover_csdl_metadata, [CATALOG_NAME] = 'Adventure Works DW')
Examples and scenarios
A DMV query can help you answer questions about active sessions and connections, and which objects are consuming the most CPU or memory at a specific point in time. For example:
Select * from $System.discover_object_activity
This query reports on object activity since the service last started.
Select * from $System.discover_object_memory_usage
This query reports on memory consumption by object.
Select * from $System.discover_sessions
This query reports on active sessions, including session user and duration.
Select * from $System.discover_locks
This query returns a snapshot of the locks used at a specific point in time.
Tools and permissions
You can use any client application that supports MDX or DMX queries. In most cases, it's best to use SQL Server Management Studio. You must have server administrator permissions on the instance to query a DMV.
To run a DMV query from SQL Server Management Studio
- Connect to the server and model object you want to query.
- Right-click the server or database object > New Query > MDX.
- Type your query, and then click Execute, or press F5.
Schema rowsets
Not all schema rowsets have a DMV interface. To return a list of all the schema rowsets that can be queried using DMV, run the following query.
SELECT * FROM $System.DBSchema_Tables
WHERE TABLE_TYPE = 'SCHEMA'
ORDER BY TABLE_NAME ASC
If a DMV is not available for a given rowset, the server returns error: The <schemarowset> request type was not recognized by the server. All other errors indicate problems with the syntax.
Schema rowsets are described in two SQL Server Analysis Services protocols:
[MS-SSAS-T]: SQL Server Analysis Services Tabular Protocol - Describes schema rowsets for tabular models at the 1200 and higher compatibility levels.
[MS-SSAS]: SQL Server Analysis Services Protocol - Describes schema rowsets for multidimensional models and tabular models at the 1100 and 1103 compatibility levels.
Rowsets described in the [MS-SSAS-T]: SQL Server Analysis Services Tabular Protocol
Note: This list may be incomplete. Refer to the [MS-SSAS-T] and [MS-SSAS] protocols for the latest.
| Rowset | Description |
|---|---|
| TMSCHEMA_ANNOTATIONS | Provides information about the Annotation objects in the model. |
| TMSCHEMA_ATTRIBUTE_HIERARCHIES | Provides information about the AttributeHierarchy objects for a column. |
| TMSCHEMA_CALCULATION_ITEMS | Provides information about the CalculationItem objects in the tabular model. |
| TMSCHEMA_CALCULATION_GROUPS | Provides information about the CalculationGroup objects in the tabular model. |
| TMSCHEMA_COLUMNS | Provides information about the Column objects in each table. |
| TMSCHEMA_COLUMN_PERMISSIONS | Provides information about the ColumnPermission objects in each table-permission. |
| TMSCHEMA_CULTURES | Provides information about the Culture objects in the model. |
| TMSCHEMA_DATA_SOURCES | Provides information about the DataSource objects in the model. |
| TMSCHEMA_DETAIL_ROWS_DEFINITIONS | Provides information about the DetailRowsDefinition objects in the model. |
| TMSCHEMA_EXPRESSIONS | Provides information about the Expression objects in the model. |
| TMSCHEMA_FORMAT_STRING_DEFINITIONS | Provides information about the FormatStringDefinition objects in the tabular model. |
| TMSCHEMA_EXTENDED_PROPERTIES | Provides information about the ExtendedProperty objects in the model. |
| TMSCHEMA_HIERARCHIES | Provides information about the Hierarchy objects in each table. |
| TMSCHEMA_KPIS | Provides information about the KPI objects in the model. |
| TMSCHEMA_LEVELS | Provides information about the Level objects in each hierarchy. |
| TMSCHEMA_LINGUISTIC_METADATA | Provides information about the synonyms for objects in the model for a particular culture |
| TMSCHEMA_MEASURES | Provides information about the Measure objects in each table. |
| TMSCHEMA_MODEL | Specifies a Model object in the database. |
| TMSCHEMA_OBJECT_TRANSLATIONS | Provides information about the translations of different objects for a culture. |
| TMSCHEMA_PARTITIONS | Provides information about the Partition objects in each table. |
| TMSCHEMA_PERSPECTIVE_COLUMNS | Provides information about the PerspectiveColumn objects in each PerspectiveTable object. |
| TMSCHEMA_PERSPECTIVE_HIERARCHIES | Provides information about the PerspectiveHierarchy objects in each PerspectiveTable object. |
| TMSCHEMA_PERSPECTIVE_MEASURES | Provides information about the PerspectiveMeasure objects in each PerspectiveTable object. |
| TMSCHEMA_PERSPECTIVE_TABLES | Provides information about the Table objects in a perspective. |
| TMSCHEMA_PERSPECTIVES | Provides information about the Perspective objects in the model. |
| TMSCHEMA_QUERY_GROUPS | Provides information about the QueryGroup objects in the tabular model. |
| TMSCHEMA_RELATIONSHIPS | Provides information about the Relationship objects in the model. |
| TMSCHEMA_ROLE_MEMBERSHIPS | Provides information about the RoleMembership objects in each role. |
| TMSCHEMA_ROLES | Provides information about the Role objects in the model. |
| TMSCHEMA_TABLE_PERMISSIONS | Provides information about the TablePermission objects in each role. |
| TMSCHEMA_TABLES | Provides information about the Table objects in the model. |
| TMSCHEMA_VARIATIONS | Provides information about the Variation objects in each column. |
Rowsets described in the [MS-SSAS]: SQL Server Analysis Services Protocol
| Rowset | Description |
|---|---|
| DBSCHEMA_CATALOGS | Describes the catalogs that are accessible on the server. |
| DBSCHEMA_COLUMNS | Returns a row for each measure, each cube dimension attribute, and each schema rowset column, exposed as a column. |
| DBSCHEMA_PROVIDER_TYPES | Identifies the (base) data types supported by the server. |
| DBSCHEMA_TABLES | Returns dimensions, measure groups, or schema rowsets exposed as tables. |
| DISCOVER_CALC_DEPENDENCY | Returns information about the calculation dependency for an object that is specified in a Tabular database or in a DAX query that is executed against a Tabular database. Note: The DISCOVER_CALC_DEPENDENCY rowset can be used to analyze dependencies and extract DAX expressions from semantic models hosted in Power BI by using XMLA endpoints. However, the DISCOVER_CALC_DEPENDENCY rowset does not include M dependencies for semantic models with Enhanced Metadata enabled, such as merged or appended M queries and M parameters. |
| DISCOVER_COMMAND_OBJECTS | Provides resource usage and activity information about the objects in use by the referenced command. |
| DISCOVER_COMMANDS | Provides resource usage and activity information about the currently executing or last executed commands in the opened connections on the server. |
| DISCOVER_CONNECTIONS | Provides resource usage and activity information about the currently opened connections on the server. |
| DISCOVER_CSDL_METADATA | Returns information about database metadata for in-memory databases. |
| DISCOVER_DATASOURCES | Returns a list of the data sources that are available on the server. |
| DISCOVER_DB_CONNECTIONS | Provides resource usage and activity information about the currently opened connections from the server to a database. |
| DISCOVER_DB_MEM_STATS | Provides coarse-grained information about the memory trackers that are active on the server. The data is aggregated at the database and system level. |
| DISCOVER_DIMENSION_STAT | returns statistics on the specified dimension. |
| DISCOVER_ENUMERATORS | Returns a list of names, data types, and enumeration values of enumerators supported by the XMLA Provider for a specific data source. |
| DISCOVER_INSTANCES | Describes the instances on the server. |
| DISCOVER_JOBS | Provides information about the active jobs executing on the server. A job is a part of a command that executes a specific task on behalf of the command. |
| DISCOVER_KEYWORDS (XMLA) | Returns information about keywords that are reserved by the XMLA server. |
| DISCOVER_LITERALS | Returns information about literals supported by the server. |
| DISCOVER_LOCATIONS | Returns information about the contents of a backup file. |
| DISCOVER_LOCKS | Provides information about the current standing locks on the server. |
| DISCOVER_MASTER_KEY | Returns the server's master encryption key. |
| DISCOVER_MEM_STATS | Provides fine-grained information about all the memory trackers that are active on the server. |
| DISCOVER_MEMORYGRANT | Returns a list of internal memory quota grants that are taken by jobs that are currently running on the server. |
| DISCOVER_MEMORYUSAGE | Returns the DISCOVER_MEMORYUSAGE statistics for various objects allocated by the server. |
| DISCOVER_OBJECT_ACTIVITY | Provides resource usage per object since the start of the service. |
| DISCOVER_OBJECT_MEMORY_USAGE | Returns the DISCOVER_MEMORYUSAGE statistics for various objects allocated by the server. |
| DISCOVER_PARTITION_DIMENSION_STAT | Returns statistics on the dimension that is associated with a partition. |
| DISCOVER_PARTITION_STAT | Returns statistics on aggregations in a particular partition. |
| DISCOVER_PERFORMANCE_COUNTERS | Returns the value of one or more specified performance counters. |
| DISCOVER_PROPERTIES | Returns a list of information and values about the properties that are supported by the server for the specified data source. |
| DISCOVER_RING_BUFFERS | Returns information about the current XEvent ring buffers on the server. |
| DISCOVER_SCHEMA_ROWSETS | Returns the names, restrictions, description, and other information for all Discover requests. |
| DISCOVER_SESSIONS | Provides resource usage and activity information about the currently opened sessions on the server. |
| DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS | Returns information about the column segments used for storing data for in-memory tables. |
| DISCOVER_STORAGE_TABLE_COLUMNS | Contains information about the columns used for representing the columns of an in-memory table. |
| DISCOVER_STORAGE_TABLES | Returns statistics about in-memory tables available to the server. |
| DISCOVER_TRACE_COLUMNS | |
| DISCOVER_TRACE_DEFINITION_PROVIDERINFO | Contains the DISCOVER_TRACE_COLUMNS schema rowset. |
| DISCOVER_TRACE_EVENT_CATEGORIES | Contains the DISCOVER_TRACE_EVENT_CATEGORIES schema rowset. |
| DISCOVER_TRACES | Contains the DISCOVER_TRACES schema rowset. |
| DISCOVER_TRANSACTIONS | Returns the current set of pending transactions on the system. |
| DISCOVER_XEVENT_TRACE_DEFINITION | Provides information about the XEvent traces that are currently active on the server. |
| DISCOVER_XEVENT_PACKAGES | Provides information about the XEvent packages that are described on the server. |
| DISCOVER_XEVENT_OBJECTS | Provides information about the XEvent objects that are described on the server. |
| DISCOVER_XEVENT_OBJECT_COLUMNS | Provides information about the schema of XEvent objects that are described on the server. |
| DISCOVER_XEVENT_SESSIONS | Provides information about the current XEvent sessions on the server. |
| DISCOVER_XEVENT_SESSION_TARGETS | Provides information about the current XEvent session targets on the server. |
| DISCOVER_XML_METADATA | Returns a rowset with one row and one column. |
| DMSCHEMA_MINING_COLUMNS | Describes the individual columns of all described data mining models that are deployed on the server. |
| DMSCHEMA_MINING_FUNCTIONS | Describes the data mining functions that are supported by the data mining algorithms that are available on a server that is running Analysis Services. |
| DMSCHEMA_MINING_MODEL_CONTENT | Enables the client application to browse the content of a trained data mining model. |
| DMSCHEMA_MINING_MODEL_CONTENT_PMML | Returns the XML structure of the mining model. The format of the XML string follows the PMML 2.1 standard. |
| DMSCHEMA_MINING_MODEL_XML | Returns the XML structure of the mining model. The format of the XML string follows the PMML 2.1 standard. |
| DMSCHEMA_MINING_MODELS | Enumerates the data mining models that are deployed on the server. |
| DMSCHEMA_MINING_SERVICE_PARAMETERS | Provides a list of parameters that can be used to configure the behavior of each data mining algorithm that is installed on the server. |
| DMSCHEMA_MINING_SERVICES | Provides information about each data mining algorithm that the server supports. |
| DMSCHEMA_MINING_STRUCTURE_COLUMNS | Describes the individual columns of all mining structures that are deployed on the server. |
| DMSCHEMA_MINING_STRUCTURES | Enumerates information about the mining structures in the current catalog. |
| MDSCHEMA_ACTIONS | Describes the actions that can be available to the client application. |
| MDSCHEMA_CUBES | Describes the structure of cubes within a database. Perspectives are also returned in this schema. |
| MDSCHEMA_DIMENSIONS | Describes the dimensions within a database. |
| MDSCHEMA_FUNCTIONS | Returns information about the functions that are currently available for use in the DAX and MDX languages. |
| MDSCHEMA_HIERARCHIES | Describes each hierarchy within a particular dimension. |
| MDSCHEMA_INPUT_DATASOURCES | Describes the data source objects described within the database. |
| MDSCHEMA_KPIS | Describes the KPIs within a database. |
| MDSCHEMA_LEVELS | Describes each level within a particular hierarchy. |
| MDSCHEMA_MEASUREGROUP_DIMENSIONS | Enumerates the dimensions of measure groups. |
| MDSCHEMA_MEASUREGROUPS | Describes the measure groups within a database. |
| MDSCHEMA_MEASURES | Describes each measure. |
| MDSCHEMA_MEMBERS | Describes the members within a database. |
| MDSCHEMA_PROPERTIES | Describes the properties of members and cell properties. |
| MDSCHEMA_SETS | Describes any sets that are currently described in a database, including session-scoped sets. |
Note
STORAGES DMVs do not have a schema rowset described in the protocol.