SQL DELETE is taking too much time

user_1846762 0
Viewed 8424

I have an Azure SQL server (standard tier, S3) with tables. I have a specific query that takes very long time to be executed: 

DELETE FROM MyTable WHERE ID=@ID

This table has 150K rows. The delete query takes +-10 minutes.

I tried to understand why. First check is how fast is locate this row in SELECT query. I run: 

SELECT * FROM MyTable WHERE ID=@ID

The result: less then 2 seconds.

I read about this issue in this thread. I took the selected answer and extract a check list from it. See my answers inside.

Checklist:

  • deleting a lot of records - This query deletes exactly 1 row.
  • many indexes - MyTable has: 1 PK, 1 FK, 4 indexes and nothing else. So I belive this is not the problem.
  • missing indexes on foreign keys in child tables - My only FK has also INDEX on the other table.
  • triggers - no triggers.
  • cascade delete (those ten parent records you are deleting could mean millions of child records getting deleted) - I tried to delete a row that referenced in other tables. So, cascade delete shouldn't delete anything on other tables.
  • Many Foreign keys to check - Not sure what it mean. I have only one FK that point to very small table (<10 rows + index).
  • Transaction log needing to grow - How to check?
  • deadlocks and blocking - How to check?

My guts feeling is that it somehow depends on the other tables that reference to this table (MyTable is FK to them). Since I have +-15 tables, I don't know how to locate the problem.

Additional observation: 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 (<3 seconds - which is great). After few hours - it's became slow again.

Questions:

  1. What more to check?
  2. Is this possible to run the DELETE query and profile it? Let assume that table2 has a FK to MyTable and the search for row @ID is taking alot of time. Maybe I can have a tool that say: "99% of the execution time spend on table2?"
  3. Any other tips will be welcome!

Thanks!

azure-sql-server azure-sql-database sql-server-profiler sql-server
1 Answers

Solved the problem.

In order to do that, I did:

  1. Manually checked all the tables in the database and confirm that they have INDEX over FK to MyTable. I found 4 tables with missing indexes. Those tables was pretty small (<1K rows). Not sure how effective it was.
  2. I had a SP that executed pretty many times. This SP was running a Transaction with lock on one of the table with FK to MyTable. I removed the lock. Agian, not sure how effective it is since the senario I mentioned above is that row dont have actualy any FK row on other tables.

However, now it works like a charm! Execution time <1 second!

user_18467620
Related