TechNet
Export (0) Print
Expand All

Use Dynamic Management Views (DMVs) to Monitor Analysis Services

 

Applies To: SQL Server 2016

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

Examples and Scenarios

Query Syntax

Tools and Permissions

DMV Reference

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.

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.

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')

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.

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

System_CAPS_ICON_note.jpg 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.

RowsetDescription
DBSCHEMA_CATALOGS RowsetReturns a list of the Analysis Services databases on the current connection.
DBSCHEMA_COLUMNS RowsetReturns a list of all the columns in the current database. You can use this list to construct a DMV query.
DBSCHEMA_PROVIDER_TYPES RowsetReturns properties about the base data types supported by the OLE DB data provider.
DBSCHEMA_TABLES RowsetReturns a list of all the tables in the current database. You can use this list to construct a DMV query.
DISCOVER_CALC_DEPENDENCY RowsetReturns a list of the columns and tables used in a model that have dependencies on other columns and tables.
DISCOVER_COMMAND_OBJECTS RowsetProvides resource usage and activity information about objects in use by the referenced command.
DISCOVER_COMMANDS RowsetProvides resource usage and activity information about currently executing command.
DISCOVER_CONNECTIONS RowsetProvides resource usage and activity information about open connections to Analysis Services.
DISCOVER_CSDL_METADATA RowsetReturns information about a tabular model.

Requires the addition of SYSTEMRESTRICTSCHEMA and additional parameters.
DISCOVER_DB_CONNECTIONS RowsetProvides resource usage and activity information about open connections from Analysis Services to external data sources, for example during processing or importing.
DISCOVER_DIMENSION_STAT RowsetReturns the attributes in a dimension or columns in a table, depending on the model type.
DISCOVER_ENUMERATORS RowsetReturns metadata about the enumerators supported for a specific data source.
DISCOVER_INSTANCES RowsetReturns information about the specified instance.

Requires the addition of SYSTEMRESTRICTSCHEMA and additional parameters.
DISCOVER_JOBS RowsetReturns information about current jobs.
DISCOVER_KEYWORDS Rowset (XMLA)Returns the list of reserved keywords.
DISCOVER_LITERALS RowsetReturns the list of literals, including data types and values, supported by XMLA.
DISCOVER_LOCKS RowsetReturns a snapshot of the locks used at a specific point in time.
DISCOVER_MEMORYGRANT RowsetReturns information about memory allocated by Analysis Services at start up.
DISCOVER_MEMORYUSAGE RowsetShows memory usage by specific objects.
DISCOVER_OBJECT_ACTIVITY RowsetReports on object activity since the service last started.
DISCOVER_OBJECT_MEMORY_USAGE RowsetReports on memory consumption by object.
DISCOVER_PARTITION_DIMENSION_STAT RowsetProvides information about the attributes in a dimension.

Requires the addition of SYSTEMRESTRICTSCHEMA and additional parameters.
DISCOVER_PARTITION_STAT RowsetProvides information about the partitions in a dimension, table, or measure group.

Requires the addition of SYSTEMRESTRICTSCHEMA and additional parameters.
DISCOVER_PERFORMANCE_COUNTERS RowsetLists the columns used in a performance counter.

Requires the addition of SYSTEMRESTRICTSCHEMA and additional parameters.
DISCOVER_PROPERTIES RowsetReturns information about properties supported by XMLA for the specified data source.
DISCOVER_SCHEMA_ROWSETS RowsetReturns names, restrictions, description and other information for all enumeration values supported by XMLA.
DISCOVER_SESSIONS RowsetReports on active sessions, including session user and duration.
DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS RowsetProvides 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 RowsetAllows 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 RowsetReturns information about the tables used for storage of models in a Tabular model database.
DISCOVER_TRACE_COLUMNS RowsetReturns an XML description of the columns available in a trace.
DISCOVER_TRACE_DEFINITION_PROVIDERINFO RowsetReturns name and version information of the provider.
DISCOVER_TRACE_EVENT_CATEGORIES RowsetReturns a list of available categories.
DISCOVER_TRACES RowsetReturns a list of traces actively running on the current connection.
DISCOVER_TRANSACTIONS RowsetReturns a list of transactions actively running on the current connection.
DISCOVER_XEVENT_TRACE_DEFINITION RowsetReturns a list of xevent traces actively running on the current connection.
DMSCHEMA_MINING_COLUMNS RowsetLists the individual columns of all mining models available on the current connection.
DMSCHEMA_MINING_FUNCTIONS RowsetReturns a list of functions supported by the data mining algorithms on the server.
DMSCHEMA_MINING_MODEL_CONTENT RowsetReturns a rowset consisting of columns that describe the current model.
DMSCHEMA_MINING_MODEL_CONTENT_PMML RowsetReturns a rowset consisting of columns that describe the current model in PMML format.
DMSCHEMA_MINING_MODEL_XML RowsetReturns a rowset consisting of columns that describe the current model in PMML format.
DMSCHEMA_MINING_MODELS RowsetReturns a list of the mining models in the current database.
DMSCHEMA_MINING_SERVICE_PARAMETERS RowsetReturns a list of the parameters for the algorithms on the server.
DMSCHEMA_MINING_SERVICES RowsetProvides a list of the data mining algorithms available on the server.
DMSCHEMA_MINING_STRUCTURE_COLUMNS RowsetReturns a list of all of the columns from all of the mining models available in the current connection.
DMSCHEMA_MINING_STRUCTURES RowsetLists the mining structures available in the current connection.
MDSCHEMA_CUBES RowsetReturns information about the cubes that are defined in the current database.
MDSCHEMA_DIMENSIONS RowsetReturns information about the dimensions that are defined in the current database.
MDSCHEMA_FUNCTIONS RowsetReturns a list of functions available to client applications connected to the database.
MDSCHEMA_HIERARCHIES RowsetReturns information about the hierarchies that are defined in the current database.
MDSCHEMA_INPUT_DATASOURCES RowsetReturns information about the data source objects that are defined in the current database.
MDSCHEMA_KPIS RowsetReturns information about the KPIs that are defined in the current database.
MDSCHEMA_LEVELS RowsetReturns information about the levels within the hierarchies that are defined in the current database.
MDSCHEMA_MEASUREGROUP_DIMENSIONS RowsetLists the dimension of measure groups.
MDSCHEMA_MEASUREGROUPS RowsetReturns a list of measure groups in the current connection.
MDSCHEMA_MEASURES RowsetReturns a list of measures in the current connection.
MDSCHEMA_MEMBERS RowsetReturns a list of all members in the current connection, listed by database, cube, and dimension.
MDSCHEMA_PROPERTIES RowsetReturns a fully qualified name of each property, along with property type, data type, and other metadata.
MDSCHEMA_SETS RowsetReturns a list of set that are defined in the current connection.

SQL Server 2008 R2 Analysis Services Operations Guide
New System.Discover_Object_Activity
New SYSTEMRESTRICTEDSCHEMA Function for Restricted Rowsets and DMVs

Community Additions

ADD
Show:
© 2016 Microsoft