title: SQL Server : efficient index to fetch latest inserted/updated rows?
body: <p>I have a table that has a int auto-increment PK, a <code>InsertDate</code> and a <code>LastUpdated</code> column, like:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">PersonId</th>
  <th>... ... ...</th>
  <th>InsertDate</th>
  <th>LastUpdated</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">123</td>
  <td></td>
  <td>2020-12-15</td>
  <td>2021-10-29</td>
  </tr>
  <tr>
  <td style="text-align: center;">124</td>
  <td></td>
  <td>2021-10-29</td>
  <td>null</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>There is a index for those 2 &quot;filtered-by&quot; columns and others included columns.</p>
  <p>The DW team runs the following query every 5 minutes fetching users created/updated on that window:</p>
  <pre><code>SELECT {included columns} 
  FROM Purchases
  WHERE InsertDate &gt; {five minutes ago} 
     OR LastUpdated &gt; {five minutes ago}
  </code></pre>
  <p>This query usually returns less than 100 items, and the table is over 150 million records long.</p>
  <p>Because the items being queried-for are &quot;extra-fresh&quot;, I'm afraid that they are present on the fragmented portion of the index. The index itself is very large with less than 0.05% fragmentation.</p>
  <p>I have 2 questions:</p>
  <ul>
  <li>Is the query being hurt by the &quot;fragmentation&quot; of the index?</li>
  <li>Does the ordering of the index (ASC vs DESC) impact the performance?</li>
  </ul>
  <p>PS: because of the data sensitivity, I can't replicate this to a non-prod environment and perform tests</p>