Dynamic Management Views (DMV) in SSAS

Vincent Rainardi wrote in his Blog SSAS DMV (Dynamic Management View) on a topic we tried already to cover here some weeks ago. The article provides some interesting links to Blogs that explain who to join the results of different DMVs together.

Monitoring current SSAS acitivities

A question DBA's are always interested in is: who is connected to my database and what are they doing? For SSAS 2008 there are some Dynamic Management Views (DMV) to answer this question(s) (and many others). The query:

SELECT TABLE_NAME
  FROM $system.dbschema_tables
 WHERE TABLE_SCHEMA = '$SYSTEM'
   AND LEFT(TABLE_NAME,8) = 'DISCOVER'
 ORDER BY table_name

provides the following list of tables (I added links to and descriptions from the documentation; some of the linked pages are not very extensive):

- DISCOVER_COMMAND_OBJECTS: Provides resource usage and activity information about the objects in use by the referenced command.
- DISCOVER_COMMANDS: Provides resource usage and activity information about the currently executing or last executed commands in the opened connections on the server.
- DISCOVER_CONNECTIONS: Provides resource usage and activity information about the currently opened connections on the server.
- DISCOVER_DB_CONNECTIONS: Provides resource usage and activity information about the currently opened connections from the server to a database.
- DISCOVER_DIMENSION_STAT: This schema rowset returns statistics on the specified dimension.
- DISCOVER_ENUMERATORS: Returns a list of names, data types, and enumeration values of enumerators supported by the Microsoft XML for Analysis (XMLA) provider for a specific data source.
- DISCOVER_INSTANCES: Describes the instances on the server.
- DISCOVER_JOBS: Provides information about the active jobs executing on the server.
- DISCOVER_KEYWORDS: Returns information about keywords reserved by the Microsoft XML for Analysis (XMLA) provider.
- DISCOVER_LITERALS: Returns information about literals, including data types and values, supported by the Microsoft XML for Analysis (XMLA) provider.
- DISCOVER_LOCKS: Provides information about the current standing locks on the server.
- DISCOVER_MASTER_KEY: This schema rowset returns the server's master encryption key (the key that is used by the server to encrypt sensitive data on disk).
- DISCOVER_MEMORYGRANT: This rowset returns a list of internal memory quota grants that are taken by jobs that are currently running on the server.
- DISCOVER_MEMORYUSAGE: This schema rowset returns the DISCOVER_MEMORYUSAGE statistics for various objects allocated by the server.
- DISCOVER_OBJECT_ACTIVITY: This schema rowset provides resource usage per object since the start of the service. Some further information on this view can be found in Vidas Matelis' Blog.
- DISCOVER_OBJECT_MEMORY_USAGE: Provides information about memory resources used by objects.
- DISCOVER_PARTITION_DIMENSION_STAT: This schema rowset returns statistics on the dimension associated with a partition.
- DISCOVER_PARTITION_STAT: This schema rowset returns statistics on aggregations in a particular partition.
- DISCOVER_PERFORMANCE_COUNTERS: This schema rowset returns the value of one or more specified performance counters.
- DISCOVER_PROPERTIES: Returns a list of information and values about the standard and provider-specific properties that are supported by the Microsoft XML for Analysis (XMLA) provider for the specified data source.
- DISCOVER_SCHEMA_ROWSETS: Returns the names, restrictions, description, and other information for all enumeration values and any additional provider-specific enumeration values supported by the Microsoft XML for Analysis (XMLA) provider.
- DISCOVER_SESSIONS: Provides resource usage and activity information about the currently opened sessions on the server.
- DISCOVER_TRACE_COLUMNS: This schema rowset contains the DISCOVER_TRACE_COLUMNS schema rowset.
- DISCOVER_TRACE_DEFINITION_PROVIDERINFO: This schema rowset returns basic information about the trace provider, such as its name and description.
- DISCOVER_TRACE_EVENT_CATEGORIES: This schema rowset contains the DISCOVER_TRACE_EVENT_CATEGORIES schema rowset.
- DISCOVER_TRACES: This schema rowset contains the DISCOVER_TRACES schema rowset.
- DISCOVER_TRANSACTIONS: This schema rowset returns the current set of pending transactions on the system.

Some of these views have to be queried with the function SYSTEMRESTRICTSCHEMA to query restricted schema rowsets - you find some examples for the use of this function in Vidas Matelis' Blog.

To answer the initial question - who is on my db? - you could use the (simple) query

SELECT *
  FROM $system.DISCOVER_SESSIONS

Another option to get these information is to use the small GUI-application Activity Viewer (download from codeplex).