SHARE
TWEET

[SQL] SharePoint performance counters aggregated by time

private775 Oct 17th, 2019 (edited) 112 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. declare @startDate datetime
  2. declare @increment int
  3. declare @perfCounter nvarchar(100)
  4. declare @machineName nvarchar(100)
  5. declare @startOfTheDay datetime
  6. declare @numofpoints int
  7.  
  8.  
  9. -- set @perfCounter = '% Processor Time'
  10. set @perfCounter = 'Available Mbytes'
  11. set @machineName = 'CONTOSO'
  12.  
  13. set @increment = 15
  14. set @numofpoints = 100
  15.  
  16. set @startDate = DATEADD(minute, -1 * (@numofpoints + 1) * @increment,  GETDATE())
  17.  
  18. -- set @startOfTheDay = DATEFROMPARTS(YEAR(@startDate), MONTH(@startDate), DAY(@startDate))
  19. set @startOfTheDay = convert(datetime, (convert(varchar, @startDate, 112)), 112)
  20. print @startOfTheDay
  21. set @startDate = DATEADD(minute, Floor(DATEDIFF(minute, @startOfTheDay, @startDate)/@increment)*@increment, @startOfTheDay)
  22.  
  23. SELECT top(@numofpoints)
  24.       DATEADD(minute, Floor(DATEDIFF(minute, @startDate, pc.[LogTime])/@increment)*@increment, @startDate) LogTimeN
  25.       ,pc.MachineName
  26.       ,pcd.Counter
  27.       ,avg(pc.[Value]) Avg
  28.       ,min(pc.[Value]) Min
  29.       ,max(pc.[Value]) Max
  30.   FROM [dbo].[PerformanceCounters] pc
  31.   inner join [dbo].[PerformanceCountersDefinitions] pcd on pcd.id=pc.[CounterId]
  32.   where pcd.Counter = @perfCounter
  33.   and pc.logtime >= @startDate
  34.   and pc.MachineName = @machineName
  35.   and pcd.Machine = @machineName
  36.   group by
  37.       DATEADD(minute, Floor(DATEDIFF(minute, @startDate, pc.[LogTime])/@increment)*@increment, @startDate)
  38.       ,pc.MachineName
  39.       ,pcd.Counter
  40.   order by 1 desc
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top