SSRS Report takes longer than query; tried parameter sniffing & nolock fixes

Question

I have a SSRS report that loads slowly, presumably from locking errors. Here's what I know.

If I put the query that drives the report into a Management Studio query window, it takes about 50 ms to run.

When running the report criteria I've been testing from the browser interface, the time values from ReportServer..ExecutionLog (WHERE Status='rsSuccess' AND ReportID=[thereport]) range as follows:

TimeDataRetrieval:  95000-120000
TimeProcessing:  35000-50000
TimeRendering: 75-125

Because I don't know a better way to do it, I monitored the sys.dm_exec_requests as I ran the report a couple times and this query seems to be the hangup:

CREATE PROCEDURE [dbo].[CheckSessionLock]  
@SessionID as varchar(32)  AS  
DECLARE @Selected nvarchar(32)  
SELECT @Selected=SessionID 
FROM [ReportServerTempDB].dbo.SessionLock 
WITH (ROWLOCK) WHERE SessionID = @SessionID

It seems this command takes around the same amount of time as the TimeDataRetrieval + TimeProcessing values above, so I believe it's the culprit. I also caught it doing a similar create of CleanOrphanedSnapshots, so I imagine this is part normal SSRS operations. So far I haven't had any luck finding related configuration settings in report builder or the code itself.

Suggested solutions I've found online have to do with "parameter sniffing" and WITH(nolock). The former seems to only be in the context of calling a stored procedure, which this is not doing. I created a SP to see if the treatment of preempting parameters would change the result and it appears to be the same. I've added the WITH(nolock) hint as well as setting the isolation to read uncommitted with no luck.

I'm sure I'm missing something simple. Here's hoping someone knows what it is. Thanks for your help.

Parameter sniffing - Fast query runs slow in SSRS Nolock approach - SSRS is locking table

Answer 1

Per the comment request by Martin Smith above, the answer to this particular issue was to recognize there were subreports running within the problem report that were themselves causing the slowness. This wasn't readily obvious simply reviewing the query being run in SSMS. So be more observant than I was and make sure you know the full composition of the report. :)