SQL for performance data for a report

Some SQL code I use all the time when I am writing an SSRS report for performance data.

(Database Operations DataWarehouse)


vPerf.DateTime, vPerf.SampleCount,(vPerf.AverageValue),(vPerf.MinValue), vPerf.MaxValue, vPerf.StandardDeviation, vPerformanceRuleInstance.InstanceName, vManagedEntity.Path, vPerformanceRule.ObjectName, vPerformanceRule.CounterName


Perf.vPerfDaily AS vPerf INNER JOIN
vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId INNER JOIN
vManagedEntity ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId


(vPerf.DateTime >= ‘2012-09-01’ ) AND (vPerf.DateTime <= ‘2012-09-30’ ) AND — Just for testing purposes.
vPerformanceRuleInstance.InstanceName = ‘C:’ AND
vPerformanceRule.ObjectName in (‘Drive Statistics’,’LogicalDisk’) AND – Objects interested in
vPerformanceRule.CounterName in (‘FreeSpace’,’FreeSpacePercent’,’UsedSpace’,’VolumeSize’)
–- Counters I am interested in
–vManagedEntity.Path like ‘%The name of the server%’ – Remove leading – if required.
ORDER BY vPerformanceRule.ObjectName,vPerformanceRule.CounterName