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
DMVs are part of the $system schema. The following will return the SchemaRowsets that can be queried through DMVs.
FROM $System.Discover_Schema_Rowsets — $system schema only
All SchemaRowsets provided by SSAS.
Request to $System.dbschema_tables with a WHERE clause Result is the same list as first request to $System.Discover_Schema_Rowsets
WHERE TABLE_SCHEMA = ‘$SYSTEM’
The traces currently running. I use this to see if my Extended Events trace is working
Detailed list of Sessions. Includes the user and the query text last executed.
Detailed list of client 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.
ORDER BY COMMAND_CPU_TIME_MS DESC