It Depends

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

… one
… second
… at
… a
… time.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s