Frequently Used DMVs for SSAS

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:

Leave a Reply

Your email address will not be published. Required fields are marked *