title: Update table with index is too slow
body: <p>I was watching the Profiler on a live system of our application and I saw that there was an update instruction that we run periodically (every second) that was quite slow. It took around 400ms every time.
  The query includes this update (which is the slow part)</p>
  
  <pre><code>UPDATE BufferTable
      SET LrbCount = LrbCount + 1,
      LrbUpdated = getdate()
  WHERE LrbId = @LrbId
  </code></pre>
  
  <p>This is the table</p>
  
  <pre><code>CREATE TABLE BufferTable(
      LrbId [bigint] IDENTITY(1,1) NOT NULL,
          ...
      LrbInserted [datetime] NOT NULL,
      LrbProcessed [bit] NOT NULL,
      LrbUpdated [datetime] NOT NULL,
      LrbCount [tinyint] NOT NULL,
  )
  </code></pre>
  
  <p>The table has 2 indexes (non unique and non clustered) with the fields by this order:<br>
  * <strong>Index1</strong> - (LrbProcessed, LrbCount)<br>
  * <strong>Index2</strong> - (LrbInserted, LrbCount, LrbProcessed)  </p>
  
  <p>When I looked at this I thought that the problem would come from <strong>Index1</strong> since LrbCount is changing a lot and it changes the order of the data in the index.<br>
  But after desactivating <strong>index1</strong> I saw the query was taking the same time as initially.
  Then I rebuilt <strong>index1</strong> and desactivated <strong>index2</strong>, this time the query was very fast.  </p>
  
  <p>It seems to me that <strong>Index2</strong> should be faster to update, the order of the data shouldn't change since the LrbInserted time is not changed.</p>
  
  <p>Can someone explain why <strong>index2</strong> is much heavier to update then <strong>index1</strong>?</p>
  
  <p>Thank you!</p>
  
  <p><strong>EDIT</strong>  </p>
  
  <p>I just realized I was assuming the wrong thing.<br>
  The complete query has another part that is responsible for the delay: </p>
  
  <pre><code>DECLARE @LrbId as bigint
  SELECT TOP 1 @LrbId = LrbId
  FROM Buffertable
  WHERE LrbProcessed = 0
      AND LrbCount &lt; 5
  ORDER BY LrbInserted
  </code></pre>
  
  <p>So, most probably it's related with a bad decision from the Sql engine on which index to use.<br>
  Sorry for the confusion. I guess we can close this question.</p>