Brief Overview of SSAS DMVs
DMVs are really just queries that return information about the server. Introduced in SQL Server Analysis Services 2008 DMVs provide an easy, convenient way to retrieve system information through SchemaRowset requests. SchemaRowsets are the objects that contain the important information about the Analysis Services server. Prior to DMVs, the only way to query SchemaRowsets was with Discover XML for Analysis (XML/A) which required building an application or creating XML documents. DMVs use a language very similar to SQL and can be called from an MDX query window. Under the hood DMV request are parsed by the data-mining parser at the DMX Layer.
Frequently used DMVs
download the following in an MDX file
DMVs are part of the $system schema. The following will return the SchemaRowsets that can be queried through DMVs.
SELECT *
FROM $System.Discover_Schema_Rowsets — $system schema only
All SchemaRowsets provided by SSAS.
SELECT *
FROM $System.dbschema_tables
Request to $System.dbschema_tables with a WHERE clause Result is the same list as first request to $System.Discover_Schema_Rowsets
SELECT *
FROM $System.dbschema_tables
WHERE TABLE_SCHEMA = ‘$SYSTEM’
The traces currently running. I use this to see if my Extended Events trace is working
SELECT *
FROM $System.DISCOVER_TRACES
Detailed list of Sessions. Includes the user and the query text last executed.
SELECT *
FROM $System.Discover_Sessions
Detailed list of client connections.
SELECT *
FROM $System.DISCOVER_CONNECTIONS
Current Executing Commands. There’s overlap between this and the $System.Discover_Sessions SchemaRowset. I usually order this by the COMMAND_CPU_TIME_MS, COMMAND_READ, or COMMAND_WRITES fields to identify potential problem queries.
SELECT *
FROM $System.DISCOVER_COMMANDS
ORDER BY COMMAND_CPU_TIME_MS DESC
References: