The other day a developer came to me and asked me why it was taking so long to delete from a table of only about 65,000 rows in the development environment.. I ran a query that I use for these sorts of things:
SELECT r.session_id, r.blocking_session_id, r.start_time, r.sql_handle, r.database_id, r.wait_type, r.wait_resource, r.transaction_id, r.open_transaction_count, t.resource_type, t.resource_description, t.request_mode, t.request_type, t.request_status, t.request_owner_type FROM sys.dm_tran_locks t JOIN sys.dm_exec_requests r ON t.request_session_id = r.session_id
There was nothing out of the ordinary going on. Additionally, there were no waiting tasks in the sys.dm_os_waiting_tasks DMV. At first I was a bit puzzled. Then it hit me: TRIGGERS! So I looked for triggers on the table. There were, unfortunately, no triggers.
However, I was able to find many dependent objects with
SELECT referencing_schema_name, referencing_entity_name FROM sys.dm_sql_referencing_entities('schema.table','OBJECT');
Working smarter, not harder, I decided I should start with the biggest object:
SELECT OBJECT_NAME(object_id) table_name, st.row_count FROM sys.dm_db_partition_stats st WHERE index_id < 2 -- we want heaps (index_id = 0) and tables (ci index_id = 1) AND OBJECT_NAME(object_id) IN ( SELECT referencing_entity_name FROM sys.dm_sql_referencing_entities('dbo.book','OBJECT') )
The top object was a table with ~12bn rows. Let’s look at that one. Sure enough, it had a foreign key to the original table `WITH CHECK`. I told him to be patient while the server checks it (and the other FK constraints on the other dependent objects) and deleted a row