T-SQL Tuesday #024 – Using SYSTEMRESTRICTSCHEMA Function in MDX to Query DMVs

This blog entry is participating in T-SQL Tuesday #024, hosted this month by Brad Schulz (Blog). You are invited to visit his blog to join the blog party. You are welcome to write your own participating blog post for the party or just to read more blogs participating in this month’s theme: Prox ‘n’ Funx.

For my part in this month’s blog party, I’m going to write about the performance counters DMV in SSAS. Yes, that’s right. I said SSAS. There are DMVs in Analysis Services too!

$system.discover_performance_counters

DMVs in Analysis Services can be queried directly using MDX or XMLA. it can also be queried via T-SQL by using a linked server to the SSAS engine. Personally, I prefer to use MDX. One of the most useful DMVs for a DBA is $system.discover_performance_counters. This DMV is the is not documented in Books Online like the other discover DMVs. This DMV cannot be queried directly like most DMVs. You must use the SYSTEMRESTRICTSCHEMA function.

Attempting to query this DMV directly will give teh following error:

-- Will not work
Select *
From $system.discover_performance_counters
Where PERF_COUNTER_NAME = '\MSAS 2008:Connection\Current connections';

Executing the query ...
Obtained object of type: Microsoft.AnalysisServices.AdomdClient.AdomdDataReader
Errors from the SQL query module: The 'PERF_COUNTER_NAME' restriction is required but is missing from the request.  Consider using SYSTEMRISTRICTSCHEMA to provide restrictions.
Execution complete

As the error states, you have to use the SYSTEMRISTRICTSCHEMA function. The following query works:

-- Query cCurrent connections
Select *
From SYSTEMRESTRICTSCHEMA(
    $SYSTEM.DISCOVER_PERFORMANCE_COUNTERS,
    PERF_COUNTER_NAME = '\MSAS 2008:Connection\Current connections'
);

Unfortunately, there is no way to have the DMV return all performance counters. you have to tell it explicitly whihc counters to return. You can request multiple performance counters in a single query by supplying multiple counter names one at a time.

-- Query multiple perf counters
Select *
From SYSTEMRESTRICTSCHEMA(
    $SYSTEM.DISCOVER_PERFORMANCE_COUNTERS,
    PERF_COUNTER_NAME = '\MSAS 2008:Threads\Processing Pool Idle Threads',
    PERF_COUNTER_NAME = '\MSAS 2008:Threads\Processing Pool Busy Threads',
    PERF_COUNTER_NAME = '\MSAS 2008:Cache\Current KB',
    PERF_COUNTER_NAME = '\MSAS 2008:Connection\Current connections'
);

SSAS Perf Counters