Im trying to query the SCOM Counter (SQL DB Engine:Buffer Manager - Database Pages) directly from SQL.

The counter does not seem to store instance information in the same manner that the counter 

Logical Disk - Avg. Disk sec/Transfer counter does. 

SELECT vManagedEntity.Path as Server, vPerformanceRuleInstance.InstanceName, AVG(vPerfRaw.SampleValue) AS Value

from Perf.vPerfRaw

join vPerformanceRuleInstance on vPerformanceRuleInstance.PerformanceRuleInstanceRowid = vPerfRaw.PerformanceRuleInstanceRowid
 join vPerformanceRule on vPerformanceRule.RuleRowId = vPerformanceRuleInstance.RuleRowId
join vManagedEntity on vManagedEntity.ManagedEntityRowid = vPerfRaw.ManagedEntityRowId
where vPerfRaw.Datetime > DATEADD(minute, -11, GETUTCDATE())
and vPerformanceRule.ObjectName = 'LogicalDisk'
and vPerformanceRule.CounterName = 'Avg. Disk sec/Transfer'

group by Path, ObjectName, CounterName, InstanceName
order by Server

It returns a value per instance per server.

this does not work for the Database Pages counter.

Does anyone know how to retrieve the instances from this counter ?

                          

Please refer and see if any of these links helps -

1) http://blogs.technet.com/b/kevinholman/archive/2007/10/18/useful-operations-manager-2007-sql-queries.aspx

2) http://opsit.blogspot.in/2013/11/useful-sql-queries-in-scom-database.html

3) http://techordian.com/tag/scom-sql-queries-2/

1) make sure that the corresponding perfromance collection is enabled and working

2) You can right click the DB server and open the performance view

3) check to see whether performance view can view Database Pages counter

4) If not, check whether the your custom performance rule for collecting Database Pages counter is configure correctly and enabled.

Roger

1) make sure that the corresponding perfromance collection is enabled and working

2) You can right click the DB server and open the performance view

3) check to see whether performance view can view Database Pages counter

4) If not, check whether the your custom performance rule for collecting Database Pages counter is configure correctly and enabled.

Roger

• Marked as answer by IvanNel 22 hours 7 minutes ago

1) make sure that the corresponding perfromance collection is enabled and working

2) You can right click the DB server and open the performance view

3) check to see whether performance view can view Database Pages counter

4) If not, check whether the your custom performance rule for collecting Database Pages counter is configure correctly and enabled.

Roger

• Marked as answer by IvanNel Tuesday, July 21, 2015 9:04 AM

1) make sure that the corresponding perfromance collection is enabled and working

2) You can right click the DB server and open the performance view

3) check to see whether performance view can view Database Pages counter

4) If not, check whether the your custom performance rule for collecting Database Pages counter is configure correctly and enabled.

Roger

• Marked as answer by IvanNel Tuesday, July 21, 2015 9:04 AM

Thanks Roger - the performance counter was created incorrectly. Its is now collecting and displaying samplevalues per instance... however, i am getting the same value for each instance. any ideas?

Hi Sir,

>> i am getting the same value for each instance. any ideas?

Could you please post a screenshot for us ?

Best Regards,

Elton JI