How can I address the massive IO wherein an index insert is occurring? Do I drop the relationship key, perform the insert and then recreate the relationship betwee A05 and A01 tables?

OM 2015-08-11 18:29:30

This query statement was causing a lot of locks to take place and was therefore degrading the performance of the database. Upon inspection of the query plan, there is a massive IO push on the lower left corner from an Index Scan on an index in the A01 table to a Merge Join (Left Semi Join). Inspecting the query statement, the table A01 was not part of the query code. However, upon inserting into the A05 table, there is a FK relationship between the A05 and A01 table on the AccountNumber field so that is where I'm deducing that the insert into the A05 table is causing an index scan to occur in the A01 table. How can I fix this? Do I drop the FK relationship between the A05 and A01 tables, perform the insert and then create the FK again? Thank you!

/storage/attachments/1340-Ticket_1334_Spinning_A05.queryanalysis
SQLkiwi 2015-08-22 11:50:30
> Do I drop the FK relationship between the A05 and A01 tables, perform the insert and then create the FK again?

That is certainly one option, yes. The underlying issue is a familiar one: FK validation requires incompatible locks to be held, and the query optimizer often transitions to merge join for FK validation much earlier than we would like. If there were a hint to force a loop join for that part of the plan, you might well find the locks were more compatible with the wider workload, and the query will often execute much faster to boot.

Sadly, there is no such hint. In simpler plans, we can get away with an OPTION (LOOP JOIN) hint, but your only option here would be to somehow convince the optimizer to generate a loops join plan (via different plan predicates for example), then apply that plan using a plan guide.

If dropping the FK and rebuilding it afterward works for you, that's an option too, as I mentioned. It's a tricky problem though in general, without a good all-purpose solution.

OM 2015-08-25 16:15:02
Thank you for your response. At least my initial thoughts were confirmed by you as to how to handle this.
August 11, 2015
No Comments