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)

SELECT

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

FROM

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

WHERE

(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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s