Use Dynamic Management Views (DMVs) to Monitor Analysis Services
Analysis Services Dynamic Management Views (DMV) are query structures that expose information about local server operations and server health. The query structure is an interface to schema rowsets that return metadata and monitoring information about an Analysis Services instance.
For most DMV queries, you use a SELECT statement and the $System schema with an XML/A schema rowset.
SELECT * FROM $System.<schemaRowset>
DMV queries return information about server state that is current at the time the query was run. To monitor operations in real time, use tracing instead. For more information, see Use SQL Server Profiler to Monitor Analysis Services.
This topic includes the following sections:
Benefits of Using DMV Queries
DMV queries return information about operations and resource consumption that are not available through other means.
DMV queries are an alternative to running XML/A Discover commands. For most administrators, writing a DMV query is simpler because the query syntax is based on SQL. In addition, the result set is returned in a tabular format that is easier to read and copy from.
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. This section provides examples for scenarios where DMV queries are most commonly used. You can also review the SQL Server 2008 R2 Analysis Services Operations Guide for additional insights into using DMV queries to monitor a server instance.
Select * from $System.discover_object_activity /** This query reports on object activity since the service last started. For example queries based on this DMV, see New System.Discover_Object_Activity.
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.
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'
Alternatively, for schema rowsets that have restrictions, the query must include the SYSTEMRESTRICTSCHEMA function. The following example returns CSDL metadata about tabular models running on a tabular mode server. Note that CATALOG_NAME is case-sensitive:
Select * from SYSTEMRESTRICTSCHEMA ($System.Discover_csdl_metadata, [CATALOG_NAME] = 'Adventure Works DW')
Tools and Permissions
You must have system administrator permissions on the Analysis Services instance to query a DMV.
You can use any client application that supports MDX or DMX queries, including SQL Server Management Studio, a Reporting Services report, or a PerformancePoint Dashboard.
To run a DMV query from Management Studio, connect to the instance you want to query, click New Query. You can run a query from an MDX or a DMX query window.
DMV Reference
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
Note
If a DMV is not available for a given rowset, the server returns the following error: "The <schemarowset> request type was not recognized by the server". All other errors point to problems with the syntax.
| Rowset | Description |
|---|---|
| DBSCHEMA_CATALOGS Rowset | Returns a list of the Analysis Services databases on the current connection. |
| DBSCHEMA_COLUMNS Rowset | Returns a list of all the columns in the current database. You can use this list to construct a DMV query. |
| DBSCHEMA_PROVIDER_TYPES Rowset | Returns properties about the base data types supported by the OLE DB data provider. |
| DBSCHEMA_TABLES Rowset | Returns a list of all the tables in the current database. You can use this list to construct a DMV query. |
| DISCOVER_CALC_DEPENDENCY Rowset | Returns a list of the columns and tables used in a model that have dependencies on other columns and tables. |
| DISCOVER_COMMAND_OBJECTS Rowset | Provides resource usage and activity information about objects in use by the referenced command. |
| DISCOVER_COMMANDS Rowset | Provides resource usage and activity information about currently executing command. |
| DISCOVER_CONNECTIONS Rowset | Provides resource usage and activity information about open connections to Analysis Services. |
| DISCOVER_CSDL_METADATA Rowset | Returns information about a tabular model. Requires the addition of SYSTEMRESTRICTSCHEMA and additional parameters. |
| DISCOVER_DB_CONNECTIONS Rowset | Provides resource usage and activity information about open connections from Analysis Services to external data sources, for example during processing or importing. |
| DISCOVER_DIMENSION_STAT Rowset | Returns the attributes in a dimension or columns in a table, depending on the model type. |
| DISCOVER_ENUMERATORS Rowset | Returns metadata about the enumerators supported for a specific data source. |
| DISCOVER_INSTANCES Rowset | Returns information about the specified instance. Requires the addition of SYSTEMRESTRICTSCHEMA and additional parameters. |
| DISCOVER_JOBS Rowset | Returns information about current jobs. |
| DISCOVER_KEYWORDS Rowset (XMLA) | Returns the list of reserved keywords. |
| DISCOVER_LITERALS Rowset | Returns the list of literals, including data types and values, supported by XMLA. |
| DISCOVER_LOCKS Rowset | Returns a snapshot of the locks used at a specific point in time. |
| DISCOVER_MEMORYGRANT Rowset | Returns information about memory allocated by Analysis Services at start up. |
| DISCOVER_MEMORYUSAGE Rowset | Shows memory usage by specific objects. |
| DISCOVER_OBJECT_ACTIVITY Rowset | Reports on object activity since the service last started. |
| DISCOVER_OBJECT_MEMORY_USAGE Rowset | Reports on memory consumption by object. |
| DISCOVER_PARTITION_DIMENSION_STAT Rowset | Provides information about the attributes in a dimension. Requires the addition of SYSTEMRESTRICTSCHEMA and additional parameters. |
| DISCOVER_PARTITION_STAT Rowset | Provides information about the partitions in a dimension, table, or measure group. Requires the addition of SYSTEMRESTRICTSCHEMA and additional parameters. |
| DISCOVER_PERFORMANCE_COUNTERS Rowset | Lists the columns used in a performance counter. Requires the addition of SYSTEMRESTRICTSCHEMA and additional parameters. |
| DISCOVER_PROPERTIES Rowset | Returns information about properties supported by XMLA for the specified data source. |
| DISCOVER_SCHEMA_ROWSETS Rowset | Returns names, restrictions, description and other information for all enumeration values supported by XMLA. |
| DISCOVER_SESSIONS Rowset | Reports on active sessions, including session user and duration. |
| DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS Rowset | Provides information at the column and segment level about storage tables used by an Analysis Services database running in Tabular or SharePoint mode. |
| DISCOVER_STORAGE_TABLE_COLUMNS Rowset | Allows the client to determine the assignment of columns to storage tables used by an Analysis Services database running in Tabular or SharePoint mode. |
| DISCOVER_STORAGE_TABLES Rowset | Returns information about the tables used for storage of models in a Tabular model database. |
| DISCOVER_TRACE_COLUMNS Rowset | Returns an XML description of the columns available in a trace. |
| DISCOVER_TRACE_DEFINITION_PROVIDERINFO Rowset | Returns name and version information of the provider. |
| DISCOVER_TRACE_EVENT_CATEGORIES Rowset | Returns a list of available categories. |
| DISCOVER_TRACES Rowset | Returns a list of traces actively running on the current connection. |
| DISCOVER_TRANSACTIONS Rowset | Returns a list of transactions actively running on the current connection. |
| DISCOVER_XEVENT_TRACE_DEFINITION Rowset | Returns a list of xevent traces actively running on the current connection. |
| DMSCHEMA_MINING_COLUMNS Rowset | Lists the individual columns of all mining models available on the current connection. |
| DMSCHEMA_MINING_FUNCTIONS Rowset | Returns a list of functions supported by the data mining algorithms on the server. |
| DMSCHEMA_MINING_MODEL_CONTENT Rowset | Returns a rowset consisting of columns that describe the current model. |
| DMSCHEMA_MINING_MODEL_CONTENT_PMML Rowset | Returns a rowset consisting of columns that describe the current model in PMML format. |
| DMSCHEMA_MINING_MODEL_XML Rowset | Returns a rowset consisting of columns that describe the current model in PMML format. |
| DMSCHEMA_MINING_MODELS Rowset | Returns a list of the mining models in the current database. |
| DMSCHEMA_MINING_SERVICE_PARAMETERS Rowset | Returns a list of the parameters for the algorithms on the server. |
| DMSCHEMA_MINING_SERVICES Rowset | Provides a list of the data mining algorithms available on the server. |
| DMSCHEMA_MINING_STRUCTURE_COLUMNS Rowset | Returns a list of all of the columns from all of the mining models available in the current connection. |
| DMSCHEMA_MINING_STRUCTURES Rowset | Lists the mining structures available in the current connection. |
| MDSCHEMA_CUBES Rowset | Returns information about the cubes that are defined in the current database. |
| MDSCHEMA_DIMENSIONS Rowset | Returns information about the dimensions that are defined in the current database. |
| MDSCHEMA_FUNCTIONS Rowset | Returns a list of functions available to client applications connected to the database. |
| MDSCHEMA_HIERARCHIES Rowset | Returns information about the hierarchies that are defined in the current database. |
| MDSCHEMA_INPUT_DATASOURCES Rowset | Returns information about the data source objects that are defined in the current database. |
| MDSCHEMA_KPIS Rowset | Returns information about the KPIs that are defined in the current database. |
| MDSCHEMA_LEVELS Rowset | Returns information about the levels within the hierarchies that are defined in the current database. |
| MDSCHEMA_MEASUREGROUP_DIMENSIONS Rowset | Lists the dimension of measure groups. |
| MDSCHEMA_MEASUREGROUPS Rowset | Returns a list of measure groups in the current connection. |
| MDSCHEMA_MEASURES Rowset | Returns a list of measures in the current connection. |
| MDSCHEMA_MEMBERS Rowset | Returns a list of all members in the current connection, listed by database, cube, and dimension. |
| MDSCHEMA_PROPERTIES Rowset | Returns a fully qualified name of each property, along with property type, data type, and other metadata. |
| MDSCHEMA_SETS Rowset | Returns a list of set that are defined in the current connection. |
See Also
SQL Server 2008 R2 Analysis Services Operations Guide
New System.Discover_Object_Activity
New SYSTEMRESTRICTEDSCHEMA Function for Restricted Rowsets and DMVs