title: Why does CTE inside a stored procedure take a very long time compared to CTE w/o stored procedure?
body: <p>I have a recursive CTE that calculates a manager hierarchy in an organization.</p>
  
  <p>This below query takes &lt; 2 seconds to finish</p>
  
  <pre><code>WITH OrganisationChart ([Identity], [DisplayName], Title, [Level], Manager) AS
  (
        SELECT
              [Identity], [DisplayName], Title, 0, Manager
        FROM
              [data].[DailyUserV1] 
        WHERE 
              [Identity] = '7276DB4F-33B0-4074-9903-D95D740A8BF3' AND Date = '2015-08-03'
  
        UNION ALL 
  
        SELECT
              emp.[Identity], 
              emp.[DisplayName], 
              emp.Title, 
              [Level] + 1, 
              emp.Manager
        FROM
            [data].[DailyUserV1]  emp 
        INNER JOIN 
            OrganisationChart ON emp.Manager = OrganisationChart.[Identity]
        WHERE 
            Date = '2015-08-03'
  )
  SELECT * FROM OrganisationChart
  </code></pre>
  
  <p>While this same query wrapped inside a stored procedure takes > 15 mins and then times out!</p>
  
  <pre><code>IF (OBJECT_ID('[dbo].[GetOrganizationChart]') IS NOT NULL)
     DROP PROCEDURE [dbo].[GetOrganizationChart]
  GO
  
  CREATE PROCEDURE [dbo].[GetOrganizationChart]
       @identity varchar(256),
       @date datetime
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
  
      DECLARE @userId varchar(256);
      SET @userId = @identity;
  
      DECLARE @endDate datetime;
      SET @endDate = @date;
  
      WITH OrganisationChart ([Identity], [DisplayName], Title, [Level], Manager) AS
      (
           SELECT
               [Identity], [DisplayName], Title, 0, Manager
           FROM
               [data].[DailyUserV1] 
           WHERE 
               [Identity] = @userId AND Date = @endDate
  
           UNION ALL 
  
           SELECT
               emp.[Identity], 
               emp.[DisplayName], 
               emp.Title, 
               [Level] + 1, 
               emp.Manager
           FROM
               [data].[DailyUserV1]  emp 
           INNER JOIN 
               OrganisationChart ON emp.Manager = OrganisationChart.[Identity]
           WHERE 
               Date = @endDate
      )
  SELECT * FROM OrganisationChart;
  END
  GO
  
  EXEC [dbo].[GetOrganizationChart] @identity = '7276DB4F-33B0-4074-9903-D95D740A8BF3', @date = '2015-08-03'
  </code></pre>
  
  <p>I have ruled out parameter sniffing as a likely cause by using local variables inside the stored procedure. What's going on here?</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Here are the links to the query execution plans in case you want to take a look.</p>
  
  <p><a href="http://pastebin.com/PbgaWNYm" rel="nofollow">cte-without-stored-proc</a>
  <a href="http://pastebin.com/1S7zfdQ9" rel="nofollow">cte-with-stored-proc</a></p>