howto extend resmon performance counter list

Jul 31, 2014 at 2:53 PM
I have already successfully extended the list of permformance counters, but only those which to not need to have an instance set

Currently I have this data source named query
select  
Format(Now(), 'yyyy-MM-dd HH:mm') as [MinKey] , 
mid( PERF_COUNTER_NAME, 2,  instr( PERF_COUNTER_NAME, ':') - 2 ) as ServerName,
mid( PERF_COUNTER_NAME, instr( PERF_COUNTER_NAME, ':') + 1  , instr( 2, PERF_COUNTER_NAME, '\') - 1 -  instr( PERF_COUNTER_NAME, ':') ) as [Group], 
mid( PERF_COUNTER_NAME, instr( 2, PERF_COUNTER_NAME, '\') + 1   )  as [Name] ,
PERF_COUNTER_NAME,
PERF_COUNTER_VALUE

 from systemrestrictschema ( $system.discover_performance_counters, 
PERF_COUNTER_NAME = '\Processor(_Total)\% User Time', 
PERF_COUNTER_NAME = '\Processor(_Total)\% Processor Time',
PERF_COUNTER_NAME = '\Processor(_Total)\% Privileged Time',
PERF_COUNTER_NAME = '\Memory\Available Bytes', 
PERF_COUNTER_NAME = '\Memory\Cache Bytes', 
PERF_COUNTER_NAME = '\Memory\Committed Bytes', 
PERF_COUNTER_NAME = '\Memory\Page Faults/sec', 
PERF_COUNTER_NAME = '\System\Processes', 
PERF_COUNTER_NAME = '\System\Threads', 
PERF_COUNTER_NAME = '\System\System Up Time', 
PERF_COUNTER_NAME = '\Objects\Events', 
PERF_COUNTER_NAME = '\Objects\Mutexes', 
PERF_COUNTER_NAME = '\Objects\Processes', 
PERF_COUNTER_NAME = '\Objects\Sections', 
PERF_COUNTER_NAME = '\Objects\Semaphores', 
PERF_COUNTER_NAME = '\Objects\Threads', 
PERF_COUNTER_NAME = '\MSAS11:Cache\Current KB', 
PERF_COUNTER_NAME = '\MSAS11:Cache\Total Direct Hits', 
PERF_COUNTER_NAME = '\MSAS11:Cache\Total Misses', 
PERF_COUNTER_NAME = '\MSAS11:Cache\Total Lookups', 

PERF_COUNTER_NAME = '\MSAS11:Connection\Current connections', 
PERF_COUNTER_NAME = '\MSAS11:Connection\Current user sessions', 
PERF_COUNTER_NAME = '\MSAS11:Connection\Total requests', 
PERF_COUNTER_NAME = '\MSAS11:Connection\Total failures', 

PERF_COUNTER_NAME = '\MSAS11:Data Mining Model Processing\Current Models Processing', 
PERF_COUNTER_NAME = '\MSAS11:Data Mining Prediction\Concurrent DM queries', 
PERF_COUNTER_NAME = '\MSAS11:Data Mining Prediction\Total Queries', 
PERF_COUNTER_NAME = '\MSAS11:Data Mining Prediction\Total Rows', 

PERF_COUNTER_NAME = '\MSAS11:Locks\Current Locks', 
PERF_COUNTER_NAME = '\MSAS11:Locks\Total deadlocks detected', 

PERF_COUNTER_NAME = '\MSAS11:Threads\Processing Pool Idle I/O job threads', 
PERF_COUNTER_NAME = '\MSAS11:Threads\Processing Pool Busy I/O job threads', 

PERF_COUNTER_NAME = '\MSAS11:Threads\Processing Pool Idle non-I/O threads', 
PERF_COUNTER_NAME = '\MSAS11:Threads\Processing Pool Busy non-I/O threads', 
PERF_COUNTER_NAME = '\MSAS11:Threads\Query Pool Idle threads', 
PERF_COUNTER_NAME = '\MSAS11:Threads\Query Pool Busy threads', 
PERF_COUNTER_NAME = '\MSAS11:Threads\Query Pool Job Queue Length', 
PERF_COUNTER_NAME = '\MSAS11:Threads\Query Pool Job Rate', 


PERF_COUNTER_NAME = '\MSAS11:Memory\Cleaner Memory KB', 
PERF_COUNTER_NAME = '\MSAS11:Memory\Cleaner Memory nonshrinkable KB', 
PERF_COUNTER_NAME = '\MSAS11:Memory\Cleaner Memory shrinkable KB', 

PERF_COUNTER_NAME = '\MSAS11:Memory\Dimension Index (Hash) files', 
PERF_COUNTER_NAME = '\MSAS11:Memory\Dimension property files', 
PERF_COUNTER_NAME = '\MSAS11:Memory\Dimension string files', 

PERF_COUNTER_NAME = '\MSAS11:Memory\Fact Aggregation files', 
PERF_COUNTER_NAME = '\MSAS11:Memory\Fact Data files', 
PERF_COUNTER_NAME = '\MSAS11:Memory\Fact String files', 

PERF_COUNTER_NAME = '\MSAS11:Memory\Filestore KB', 
PERF_COUNTER_NAME = '\MSAS11:Memory\Filestore IO Errors', 

PERF_COUNTER_NAME = '\MSAS11:Memory\Memory Limit High KB', 
PERF_COUNTER_NAME = '\MSAS11:Memory\Memory Limit Low KB', 
PERF_COUNTER_NAME = '\MSAS11:Memory\Memory Usage KB', 

PERF_COUNTER_NAME = '\MSAS11:Proactive Caching\Notifications/sec', 
PERF_COUNTER_NAME = '\MSAS11:Proactive Caching\Processing Cancellations/sec', 

PERF_COUNTER_NAME = '\MSAS11:Proc Aggregations\Memory Size Bytes', 
PERF_COUNTER_NAME = '\MSAS11:Proc Aggregations\Current Partitions', 
PERF_COUNTER_NAME = '\MSAS11:Proc Aggregations\Total Partitions', 

PERF_COUNTER_NAME = '\MSAS11:Processing\Total Rows Read', 
PERF_COUNTER_NAME = '\MSAS11:Processing\Total Rows Written', 
PERF_COUNTER_NAME = '\MSAS11:Processing\Rows Read/sec', 
PERF_COUNTER_NAME = '\MSAS11:Processing\Rows Written/sec', 

PERF_COUNTER_NAME = '\MSAS11:Storage Engine Query\Total Bytes Sent',
PERF_COUNTER_NAME = '\MSAS11:Storage Engine Query\Total Rows Sent',
PERF_COUNTER_NAME = '\MSAS11:Storage Engine Query\Total Queries Answered',
PERF_COUNTER_NAME = '\MSAS11:Storage Engine Query\Total Measure Group queries',
PERF_COUNTER_NAME = '\MSAS11:Storage Engine Query\Total Dimension queries.',


PERF_COUNTER_NAME = '\MSAS11:MDX\Number of calculation covers',
PERF_COUNTER_NAME = '\MSAS11:MDX\Number of evictions of evaluation nodes',
PERF_COUNTER_NAME = '\MSAS11:MDX\Total Cells Calculated'

)
As you can see I have extended the Processor, Objects, Memory and System counters.

But I could not get or figure out how to write this query to query the "Process" MSMDSRV.EXE and some of its specific counters like Bytes read, Bytes write and so on...
Coordinator
Jul 31, 2014 at 4:38 PM
That's cool. Thanks for sharing with the community.

I have an email thread with a program manager on the SSAS team back in 2011 and he was saying they didn't really intend this DMV to be used for retrieving a bunch of generic perfmon counters unrelated to SSAS. They were investigating removing this functionality at some point in the future. So just a heads up in case it breaks in a future version.