My phone rag at 4 AM last Sunday morning, which is never a good sign. It was a member of our IT team who was doing an upgrade to some software. He was concerned because a particular SQL statement had been running for 30 minutes so far and when this person performed the upgrade on the test system earlier in the week, this particular statement completed in a few minutes. Because this was a mission-critical system, he wanted me to take a look and see if I could tell why things were taking so long and to be sure everything was still ok.
My first step was to fire up SSMS and launch activity monitor. Sure enough, I saw the query being executed. There was no blocking going on, nor were there excessive waits. Occasionally, I would see an I/O wait, but they were on the order of 20 milliseconds and were somewhat rare. It looked to me like SQL was just chugging along.
My next step was to get the actual query being run. It was really, really basic:
DELETE FROM amc_rd_ref_data_item WHERE reference_data_group_id = @rdg_id
I looked at the table, thinking about possible indexing issues, or maybe triggers. This is what I saw:
No triggers. The reference_data_group_id was column was the second field in the primary key, so that index couldn’t be used. But each of the other two indexes had this as the first column, so the database engine could have used either one of them for this query. There were also no constraints. There were three foreign keys defined on this table, but the tables they linked too had a small number of rows. No cursors were involved. Data types were matched, so there were no implicit conversion issues. The table only had about 500,000 rows in it. In short, nothing was jumping out at me as to why this simple query should take so long to run.
I decided to look at the execution plan for the query. Bingo!
Wow. That’s zoomed out so you can see the whole plan, but each of those vertical lines is a nested loop operator. It turns out there were more than 200 tables that referenced the table we were deleting from with foreign keys of their own.
SQL was working through this without issue, so I decided to just let the query continue. I made sure transaction log backups were being taken frequently and that the log drive had enough room for it to grow – I didn’t want this thing to bomb out for any reason and have to start over. The query ended up taking just over an hour to complete and when it was done, the table was down to 9,000 rows, so there was a lot of deleting and looping going on.
If you have what looks to be a very simple query that seems to be taking an inordinately long time to run, in addition to checking for blocking, proper indexing, triggers, and the usual suspects, keep in mind foreign keys on other tables. View the execution plan to find out exactly what SQL Server is doing.