title: SQL DELETE is taking too much time
body: <p>I have an <strong>Azure SQL server</strong> (standard tier, S3) with tables. I have a specific query that takes very long time to be executed: </p>
  
  <pre><code>DELETE FROM MyTable WHERE ID=@ID
  </code></pre>
  
  <p>This table has 150K rows. The delete query takes +-10 minutes.</p>
  
  <p>I tried to understand why. First check is how fast is locate this row in SELECT query. I run: </p>
  
  <pre><code>SELECT * FROM MyTable WHERE ID=@ID
  </code></pre>
  
  <p>The result: less then 2 seconds. </p>
  
  <p>I read about this issue in <a href="https://stackoverflow.com/questions/10901299/delete-statement-in-sql-is-very-slow">this thread</a>. I took the selected answer and extract a check list from it. See my answers inside.</p>
  
  <h2>Checklist:</h2>
  
  <ul>
  <li><em>deleting a lot of records -</em> 
  This query deletes exactly 1 row.</li>
  <li><em>many indexes -</em> <code>MyTable</code> has: 1 PK, 1 FK, 4 indexes and nothing else. So I belive this is not the problem.</li>
  <li><em>missing indexes on foreign keys in child tables -</em> My only FK has also INDEX on the other table.</li>
  <li><em>triggers -</em> no triggers.</li>
  <li><em>cascade delete (those ten parent records you are deleting could mean millions of child records getting deleted) -</em> I tried to delete a row that referenced in other tables. So, cascade delete shouldn't delete anything on other tables. </li>
  <li>Many Foreign keys to check - Not sure what it mean. I have only one FK that point to very small table (&lt;10 rows + index).</li>
  <li><em>Transaction log needing to grow -</em> How to check?</li>
  <li><em>deadlocks and blocking -</em> How to check? </li>
  </ul>
  
  <p>My guts feeling is that it somehow depends on the other tables that reference to this table (<code>MyTable</code> is FK to them). Since I have +-15 tables, I don't know how to locate the problem.</p>
  
  <p><strong>Additional observation:</strong> I found something interesting. If you run the delete one after another, the first call is taking a lot of time (as reported above), the second call is faster and so on. If you run this query few times, it getting really fast (&lt;3 seconds - which is great). After few hours - it's became slow again. </p>
  
  <h2>Questions:</h2>
  
  <ol>
  <li>What more to check? </li>
  <li>Is this possible to run the <code>DELETE</code> query and profile it? Let assume that <code>table2</code> has a FK to <code>MyTable</code> and the search for row <code>@ID</code> is taking alot of time. Maybe I can have a tool that say: "99% of the execution time spend on <code>table2</code>?"</li>
  <li>Any other tips will be welcome!</li>
  </ol>
  
  <p>Thanks!</p>