Top Performance Samples Collected (Report Dataset)


image

/*Top performance samples collected
Jonathan Almquist (http://blogs.technet.com/b/jonathanalmquist/)
04-08-2011
*/
 
DECLARE @RowCount AS INT,
    @MGID AS INT,
    @TimeZoneOffset AS INT,
    @OffSetDays AS INT,
    @StartDate AS DATE,
    @EndDate AS DATE
    
SET @RowCount = 20
SET @MGID = 1
SET @TimeZoneOffset = 5
SET @OffSetDays = 60
SET @StartDate = DATEADD(hour, @TimeZoneOffset, DATEADD(day, -@OffSetDays, GETDATE()))
SET @EndDate = DATEADD(hour, @TimeZoneOffset, GETDATE())
 
SELECT TOP (@RowCount) SUM(vPD.SampleCount) AS 'Count',
        vPR.ObjectName AS 'Object',
        vPR.CounterName AS 'Counter',
        vRule.RuleDefaultName AS 'Rule',
        vMP.ManagementPackDefaultName AS 'MP'
FROM  Perf.vPerfDaily AS vPD INNER JOIN
        vPerformanceRuleInstance AS vPRI ON vPRI.PerformanceRuleInstanceRowId = vPD.PerformanceRuleInstanceRowId INNER JOIN
        vPerformanceRule AS vPR ON vPR.RuleRowId = vPRI.RuleRowId INNER JOIN
        vRule ON vRule.RuleRowId = vPR.RuleRowId INNER JOIN
        vManagementPack AS vMP ON vMP.ManagementPackRowId = vRule.ManagementPackRowId INNER JOIN
        vManagedEntity AS vME ON vME.ManagedEntityRowId = vPD.ManagedEntityRowId INNER JOIN
        vManagementGroup AS vMG ON vMG.ManagementGroupRowId = vME.ManagementGroupRowId
WHERE (vMG.ManagementGroupRowId = @MGID) AND (vPD.DateTime BETWEEN @StartDate AND @EndDate)
GROUP BY vPR.ObjectName, vPR.CounterName, vRule.RuleDefaultName, vMP.ManagementPackDefaultName
ORDER BY 'Count' DESC

 

Go to main page to download formatted TSQL scripts for all report dataset samples on my blog.

Comments (0)

Skip to main content