Delete query does not commit I need to delete about 900m rows from a table that has 6b rows. Since big deletes cause big problems for the transaction log, I'm deleting the data in small batches. What puzzles me is SQL Server does not like to commit each chunked delete.

Let's say I delete it in batches of 1m rows:

DECLARE @N int = 1000000

DELETE TOP (@N)
    FROM    my_table
    WHERE   ...


In a separate query, I monitor the status of the delete:

-- Before executing the first query
sp_spaceused 'my_table' -- row_count = 6b

-- During execution
sp_spaceused 'my_table' -- row_count = decreasing from 6b -&gt; 5.999b


And then it just hung! I can tell that 1m rows have been removed from my_table by peeking at the row count, but the first query just keeps going on and on. 

What could cause this behavior? Another big table has a Foreign Key relationship referencing my_table.ID. Could FK validation be the culprit here?

Database is SQL Server 2012.
Yes, it will definitely add overhead if you have foreign keys pointing to the table that you are trying to clear records from. This is because it needs to ensure that the constraint is not violated once the data has been removed. I've personally seen deletes take days with foreign key checks, but hours if you remove the foreign keys before performing the DELETE.

Disabling the foreign keys will not help in your scenario as you can only disable a foreign key check for INSERT and UPDATE statements. I recommend that you remove any dependent data from the related tables, then remove the foreign key (if possible), and then remove the data from the base table. Once the data has been completely removed you can re-add the foreign key relationship.