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!
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:ConnectionCurrent 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:ConnectionCurrent 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:ThreadsProcessing Pool Idle Threads', PERF_COUNTER_NAME = 'MSAS 2008:ThreadsProcessing Pool Busy Threads', PERF_COUNTER_NAME = 'MSAS 2008:CacheCurrent KB', PERF_COUNTER_NAME = 'MSAS 2008:ConnectionCurrent connections' );