title: Azure SQL Server SUM query times out randomly
body: <p>We have relatively-high load SQL database with accounting information, which is hosted on Azure cloud. To calculate balance of user account(s) we sum all ins and outs every time (<code>entry</code> table). I know we could do that differently, but current solution works quite well in absolute majority of cases and HTTP request for getting balance takes ~134 ms on average (and that includes much more than just the problematic query) despite <code>entry</code> table contains hundreds of billions of rows (we have many accounts and not that much operations on each).</p>
  
  <p>Our <strong>SQL</strong> (slightly beautified after NHibernate - removed column and table aliases):</p>
  
  <pre><code>select AccountId, cast(sum(Amount) as DECIMAL(19,5)) as Balance
  from entry
  where  not (Locked=1) and (AccountId in (@p0))
  group by AccountId
  </code></pre>
  
  <p>Execution plan of fast-executing query (not sure if it is helpful):
  <a href="https://i.stack.imgur.com/GwqqM.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/GwqqM.png" alt="enter image description here"></a>
  So query uses index which include AccountId, Locked and two more columns after that.</p>
  
  <p><strong>Usually</strong> it executes in less than <strong>100ms</strong>, but sometimes (<strong>1 in ~1000 requests</strong>) hangs for <strong>30+ seconds</strong> and fails on timeout.</p>
  
  <p>I've tried to find deadlocks using <code>sp_who2</code> stored procedure, but nothing was found - it didn't show any blocked queries even though I tried it many times and exceptions were constantly being thrown during this time.</p>
  
  <p>Interestingly, we have hundreds of requests of this kind per minute, but <strong>failures occur one by one, so intervals between exceptions are at least 30s</strong> and are multiplies of 30 (30, 60, 90, 120 seconds - 30 and 60 are the most common). I cannot explain this, but I suppose there is some job I don't know about, which is running as single-thread and blocking particular accounts. But how can I find it?</p>