title: EF Core query fails by timeout on sorting on Azure SQL
body: <p>I have a database hosted on Azure using Premium tier.
  Database has a pretty simple table with the following structure:</p>
  
  <pre><code>Id PK uniqueidentifier
  Timestamp datetimeoffset
  TenantId uniqueidentifier
  other columns
  </code></pre>
  
  <p>Table has only clustered index.</p>
  
  <p>My app is querying the data using EF Core in the following way:</p>
  
  <pre><code>dbContext
  .TaskRecords
  .Where(r =&gt; r.TenantId == tenantId)
  .OrderByDescending(item =&gt; item.Timestamp)
  .Skip(10)
  .Take(10)
  .ToListAsync();
  </code></pre>
  
  <p>At some point in time when there were more than 200k records queries started failing by timeout.  </p>
  
  <p>EF core generates the following SQL query:</p>
  
  <pre><code>exec sp_executesql N'SELECT [r].[Id],[r].[TenantId], [r].[Timestamp], [r].
  [OTHER_COLUMNS]
  FROM [TaskRecords] AS [r]
  WHERE [r].[TenantId] =@__firmId_0
  ORDER BY [r].[Timestamp] DESC
  OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY',N'@__tenantId_0 uniqueidentifier,@__p_1 
  int,@__p_2 int',@__tenantId_0 ='645B38D4-1009-47CF-8519-
  182865827F22',@__p_1=0,@__p_2=10
  </code></pre>
  
  <p>which performs well when executing from SSMS or using SqlCommand, but fails with EF.</p>
  
  <p>Adding nonclustered index for Timestamp and TenantId columns didn't solve the issue at all.
  Index statistics shown that clustered index had 99% of fragmentation. After rebuilding the index queries started working again. But after reaching 15% of fragmentation queries started to fail by timeout. </p>
  
  <p>Using ascending sorting also works even without rebuilding the index.</p>
  
  <p>What's wrong with EF and why it behaves like that? </p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>Rebuilding the indexes doesn't work anymore. So it was probably just coincidence.</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>I spent some time with simple console apps with 1.1.2 and 2.0 versions of EF Core to analyze framework behavior.
  Query executes well and almost immediately when calling from Main method, but hangs when query is called from within another method:</p>
  
  <pre><code>static void Main(string[] args)
  {
      var context = new DbContext(...);
      var result = context.TaskRecords
      .Where(r =&gt; r.TenantId == tenantId)
      .OrderByDescending(item =&gt; item.Timestamp)
      .Skip(10)
      .Take(10)
      .ToList(); // WORKS FINE
  
      result = GetRecords(); //FAILS BY TIMEOUT
  }
  
  public static List&lt;TaskRecords&gt; GetRecords()
  {
      var context = new DbContext(...);
      return context.TaskRecords
      .Where(r =&gt; r.TenantId == tenantId)
      .OrderByDescending(item =&gt; item.Timestamp)
      .Skip(10)
      .Take(10)
      .ToList();
  }
  </code></pre>
  
  <p>My TaskRecord entity includes 18 properties. When I comment out one random property - second call starts executing with no delay.</p>