I have a query that looks something like this
from table a
inner join (select top (100) <identityfield> from table b) x
inner join a.<identityfield>=x.<identityfield>
Before I get judged for something like this, I have to have this process delete in small chunks so that the application can continue to function without having excessive long blocking calls. And, yes the table above is an inner join to itself.
My DBA is telling me that the parallelism in the query plan is showing 4 and that this query is "blocking" itself. Does that makes sense? She wants me to change the code for this query to Option MaxDop 1.
Good advice? Or is there a better way?
Thanks in advance for your time.