Resource Monitoring - Microsoft SQL Server 2008 Analysis Services - page 813

Querying DMVs and SQL Semantics

DMVs are exposed as part of the $System schema, and their naming matches the names of
the underlying SchemaRowset s. For example, if you want to query information about client
connections (exposed by the DISCOVER_CONNECTIONS SchemaRowset request), you use the
Discover_Connections DMV by entering the following query in the DMX query editor of
SQL Server Management Studio:

select * from $system.discover_connections

Figure 41.2 shows the results of this query.

FIGURE 41.2 Connections currently opened by client applications.

Analysis Services does not differentiate between requests related to the resource monitor-
ing and other SchemaRowset requests, such as requests for available cubes or dimensions.
Therefore, you can use DMVs to access all SchemaRowset s provided by Analysis Services.
We will not attempt to enumerate every single SchemaRowset or DMV available for you to
query; instead, you can find all of them by sending the following query:

select * from $system.dbschema_tables.

To see the columns that are available for each of the DMVs, send this query:

select * from $system.dbschema_columns.

This query returns a long list. Use a WHERE clause to restrict this list only to columns rele-
vant to a specific DMV. For example, to list columns related to the Discover_Connections
DMV, use the following query:

select * from $system.dbschema_tables where table_name = 'DISCOVER_CONNECTIONS'

Unfortunately, Analysis Services does not support all constructs of the SQL language for
querying DMVs. For example, names of the DMVs are case sensitive; this explains why in
the preceding query, we had to spell out the name of the DMV in all uppercase letters.

Among many SQL constructs, Analysis Services supports the following (which are the
more frequently used):

. Conditions —Use a WHERE clause to narrow the query results. You can use AND / OR

operators as part of your condition.