title: Different execution plan for the same query
body: <p>I have two identical SQL Databases that contain nearly the same records in each of their tables. The only difference between them is that one lives on my local machine and the other one is in Azure. Yet, after investigating a performance issue I found out that the two databases produce different execution plans for some of the queries. To give you an example, here is a simple query that takes approximately 1 second to run.</p>
  
  <pre><code>select count(*) from Deposits
  inner join households on households.id = deposits.HouseholdId
  where CashierId = 'd89c8029-4808-4cea-b505-efd8279dc66d'
  </code></pre>
  
  <p>It is obvious that the inner join needs to be omitted as it doesn't contribute to the end result. Indeed, it is omitted on my local machine but this is not the fact for Azure. Here are two pictures visualizing the execution plans for my local machine and Azure, respectively.</p>
  
  <p><a href="https://i.stack.imgur.com/KnmCU.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/KnmCU.png" alt="Local machine"></a></p>
  
  <p><a href="https://i.stack.imgur.com/jpjqY.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/jpjqY.png" alt="Azure"></a></p>
  
  <p>Just to give you some background on what happened, everything worked perfectly until I scaled down my Azure database to Basic 5 DTUs. Afterwards, some queries became extremely slow and I had no idea why. I scaled up the Db instance again but I saw no improvement. Then I rebuilt the indexes and noticed that if I rebuild them <strong>in the correct order</strong>, the queries will once again start performing as expected. Yet, I have absolutely no idea why I need to rebuild them in some specific order and, even less, how to determine the correct order. Now, I have issues with virtually all queries related to the <code>Deposits</code> table. I tried rebuilding the indexes but I saw no improvement whatsoever. I suspect that there is something to do with the PK index but I am not quite sure. The table contains approximately 300k rows.</p>