3

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!

Eric Hauenstein
  • 2,557
  • 6
  • 31
  • 41
No1Lives4Ever
  • 6,430
  • 19
  • 77
  • 140
  • Even if there are no other actual references from/to your table, if such constraints exist, perhaps SQL Server has to do more work when deleting, rather than selecting, each record in your table. – Tim Biegeleisen Aug 28 '18 at 10:54
  • A) Check the fragmentation of your index B) Run your Delete Statement then Run SP_Who2 active, check the CPU and IO. C) How large are your .mdf and .ldf files – mvisser Aug 28 '18 at 10:57
  • While there may be a programmatic solution to your issue, I think you may have a greater chance at finding a cause and solution to your issue, if you asked it on the [DBA StackExchange Site](https://dba.stackexchange.com/) – Lars Kristensen Aug 28 '18 at 11:02
  • 2
    I had a case when I was trying to delete from a table containing ~1 million rows but it took forever. Querying the rows with select * was fast, but the delete was insanely slow. Then I realized there was a foreign key to the table from another one with 2 billion(!) rows. Of course, the FK column was not indexed. Solution: dropped the FK, deleted the rows, recreated the FK. Recreating the FK still took some time, but it was much faster. – Gauravsa Aug 28 '18 at 11:06
  • 1
    Upload your actual execution plan to https://www.brentozar.com/pastetheplan/ and add the link to your question. The fact that subsequent deletes are much faster than the first suggest full scans, where data are not cached the first run. – Dan Guzman Aug 28 '18 at 11:25
  • `Many Foreign keys to check` - there are possible situations when FK number wouldn't let delete anything from table ever. `cascade delete shouldn't delete anything on other tables.` - but server **must** check and put locks. Note, server must have checked all the tables, looking at this one vie FK at once. Withing one single transaction. Even *estimated execution plan* could help discovering what is going on. – Ivan Starostin Aug 28 '18 at 11:35
  • Is your table indexed on ID, meaning, does the table have any index were ID is the first field in that index? If your select statement selecting based on ID takes 2 seconds for a table with only 150k rows, it looks like you don't have a suitable index for this use case. – TT. Aug 28 '18 at 13:03

1 Answers1

3

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!

No1Lives4Ever
  • 6,430
  • 19
  • 77
  • 140