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

Examples and Scenarios

Query Syntax

Tools and Permissions

DMV Reference

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

See Also

Other Resources

SQL Server 2008 R2 Analysis Services Operations Guide

New System.Discover_Object_Activity

New SYSTEMRESTRICTEDSCHEMA Function for Restricted Rowsets and DMVs