title: SSRS Report takes longer than query; tried parameter sniffing & nolock fixes
body: <p>I have a SSRS report that loads slowly, presumably from locking errors.  Here's what I know.</p>
  
  <p>If I put the query that drives the report into a Management Studio query window, it takes about 50 ms to run.</p>
  
  <p>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:</p>
  
  <pre><code>TimeDataRetrieval:  95000-120000
  TimeProcessing:  35000-50000
  TimeRendering: 75-125
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>I'm sure I'm missing something simple.  Here's hoping someone knows what it is.  Thanks for your help.</p>
  
  <p>Parameter sniffing - <a href="https://stackoverflow.com/questions/2283943/fast-query-runs-slow-in-ssrs">Fast query runs slow in SSRS</a>
  Nolock approach - <a href="https://stackoverflow.com/questions/10756955/ssrs-is-locking-table">SSRS is locking table</a></p>