Monitoring SSAS instance using DMVs
In the previous section you learned about a few DMVs referenced by Activity Viewer. Analysis Services supports many other DMVs not used by Activity Viewer. You can query DMVs directly using the SSMS or ASCMD command-line utility.
How to do it...
You can query DMVs by executing simple SELECT
statements in the MDX query window within SSMS by performing the following steps:
Navigate to FILE | New | Analysis Services MDX Query.
Connect to your SSAS instance as an administrator.
Paste the DMV queries you captured using SQL Server Profiler (see the previous section, Monitoring SSAS instance using Activity Viewer). The
SELECT
statements that you can use for querying DMVs are somewhat limited:You can only query one DMV at a time and cannot join multiple DMVs.
You can extract a subset of all the rows by specifying the
TOP
keyword and theORDER BY
clause.You can extract a subset of all the columns by explicitly listing column names or get all the columns using the star...