title: Which fixes are enabled by ENABLE_QUERY_OPTIMIZER_HOTFIXES hint in sql server 2016?
body: <p>I have found that the 'ENABLE_QUERY_OPTIMIZER_HOTFIXES' query hint greatly improves cardinality estimation accuracy in my azure sql server 2016 database with compatibility level 130 and 'Legacy Cardinality Estimation = Off'. </p>
  
  <p>My question: How can I see which 'fixes' this query hint is toggling? (I understand this hint should enable any fix released 'post sql server 2016 RTM' but I can't find a listing of those fixes anywhere.) </p>
  
  <p>I would like to understand the root cause of this issue and this is my best lead so far. </p>
  
  <p><strong>Context</strong> </p>
  
  <p>The query below returns 15,581 results. </p>
  
  <pre><code>select 
      RegistrationId from Registrations 
  where 
      EventId = 1234
      and IsDeleted = 0
  OPTION(
  --USE HINT('ENABLE_QUERY_OPTIMIZER_HOTFIXES'), 
  recompile)
  </code></pre>
  
  <p><strong>Without</strong> the 'ENABLE_QUERY_OPTIMIZER_HOTFIXES' hint the CE predicts 124 rows. </p>
  
  <p><strong>With</strong> the hint the CE predicts 16,750. </p>
  
  <p>Quite a difference! (I understand the difference is immaterial in this simplified query, but I believe these poor estimations are causing the query optimizer to choose bad plans in larger, more complex queries). </p>
  
  <p>Other findings/notes:</p>
  
  <ul>
  <li>Inverting the IsDeleted predicate (and IsDeleted != 1) returns a 'good' estimation (16,750)</li>
  <li>Query hint 'FORCE_LEGACY_CARDINALITY_ESTIMATION' returns a 'good' estimation  (16,750)</li>
  <li>Removing IsDeleted predicate returns a 'good' estimation (17,108)</li>
  <li>Statistics are up to date.</li>
  <li>EventID and IsDeleted are part of a multi-column non-clustered index (IX_RegistrationEventID)</li>
  <li>EventID = 1234 is a RANGE_HI_KEY value for IX_RegistrationEventID stats and has EQ_ROWS = 17100.22 </li>
  </ul>