A cool way to check Reporting Services memory usage

I recently came across a reporting server whose memory was a touch too low and would cause a particularly large report to sometimes fail to execute to completion.

I noticed the execution log view has XML with memory estimates for each report; I wanted to tally these for all reports running at any one time and pick out which periods required the most memory.

It turned out this isn't too difficult.

Select  Top 10
        @@Servername,
        el.TimeStart,
        el.TimeEnd,
        MemoryUsageMB
From    [ReportServer$SQL2014].dbo.ExecutionLog2 el
Outer Apply (
        Select  Sum(
                    Coalesce(c.value('Pagination[1]', 'int'), 0) +
                    Coalesce(c.value('Rendering[1]', 'int'), 0) +
                    Coalesce(c.value('Processing[1]', 'int'), 0)) / 1024.0 As MemoryUsageMB
        From    [ReportServer$SQL2014].dbo.ExecutionLog2 el2
        Cross Apply
                el2.AdditionalInfo.nodes('AdditionalInfo/EstimatedMemoryUsageKB') As b(c)
        Where   el.TimeStart Between el2.TimeStart and el2.TimeEnd
        Or      el.TimeEnd Between el2.TimeStart And el2.TimeEnd
        ) a
Order By MemoryUsageMB Desc

This can take a little while to run if you have a massive execution log, and my test instance isn't very busy, but you get the idea.

I haven't conducted analysis across many servers in this way yet but from a cursory glance it's nowhere near what SSRS will actually have at any point in time; for example on a server that estimated 500MB for reports it may be holding 1.5GB of memory.

But I think this might still be useful in the future for comparing busy periods between servers.