title: optimize Table Spool in SQL Server Execution plan
body: <p>I have the following sql query and trying to optimize it using execution plan. In execution plan it says Estimated subtree cost is 36.89. There are several table spools(Eager Spool). can anyone help me to optimize this query. Thanks in advance.</p>
  
  <pre><code>SELECT
      COUNT(DISTINCT bp.P_ID) AS total,
      COUNT(DISTINCT CASE WHEN bc.Description != 'S' THEN bp.P_ID END) AS m_count,
      COUNT(DISTINCT CASE WHEN bc.Description = 'S' THEN bp.P_ID END) AS s_count,
      COUNT(DISTINCT CASE WHEN bc.Description IS NULL THEN bp.P_ID END) AS n_count
  FROM 
      progress_tbl AS progress
  INNER JOIN  Person_tbl AS bp  ON bp.P_ID = progress.person_id
  LEFT OUTER JOIN  Status_tbl AS bm  ON bm.MS_ID = bp.MembershipStatusID          
  LEFT OUTER  JOIN  Membership_tbl AS m ON m.M_ID = bp.CurrentMembershipID
  LEFT OUTER JOIN  Category_tbl AS bc ON bc.MC_ID = m.MembershipCategoryID
  WHERE 
      logged_when BETWEEN '2017-01-01' AND '2017-01-31'
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/IQBxJ.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/IQBxJ.jpg" alt="enter image description here"></a></p>