Not too many people know that they can query internal performance and resource related data for SSAS using DMVs. Yes, I’m talking about similar DMVs to the much more known relational database DMVs, only these DMVs can be queried by connecting to an SSAS instance, open a new MDX or DMX query window (either way will work for you) and type a simple SELECT statement which retrieves the information from the DMVs. These DMVs are actually metadata that is kept internally as schema rowsets and you can simply query them as if they were plain relational tables. There are actually quite a few interesting DMVs that you can use to get a real-time view of your Analysis Services server status.
To look at the list of all available DMVs that you can query, run the following SELECT query:
SELECT * FROM $system.dbschema_tables WHERE Table_Schema = '$SYSTEM'
A few of the more interesting DMVs are:
SELECT * FROM $system.DISCOVER_CONNECTIONS
This DMV holds information regarding connections made to the Analysis Service instance.
SELECT * FROM $system.DISCOVER_OBJECT_ACTIVITY
This DMV holds accumulative information regarding Analysis Services objects’ resource usage. It gives you a list of all objects and how much CPU each consumed, how much read and write activity occurred and even whether access to it incurred an aggregation hit or miss.
SELECT * FROM $system.DISCOVER_Commands
This is the semi equivalent to the relational sp_who2 system procedure and lists all active connections and their associated queries.
There are plenty more DMVs that you can use, simply use the first SELECT statement mentioned above to identify the DMVs that you need to add to your personal SSAS scripts arsenal and use at will. Note that the data in the DMVs is being cleared when you restart SSAS service (this is true to some extent, as object and data version information is kept, for example), so be aware when you perform an analysis on object usage etc. that you look at a long-enough snapshot of the DMV.
(This is post number 10 for week number 9 of my SQL blog challenge)