Best way to use OPTIMIZE FOR hint
Collections contain collection items. There are a few large collections and a lot of small collections. And there is one humungous collection with id=0. Again, see attachment.
When the copy sproc is compiled with a large collection or the zero collection, the inserts into dbo.CollectionItems causes a scan on dbo.Collections to enforce foreign keys. This causes deadlocks and a lot of grief. I'd like to avoid the scan and I'm considering using hints.
- Adding an option (OPTIMIZE FOR (@CollectionId UNKNOWN)) causes a seek, but only barely.
- Adding option (OPTIMIZE FOR (@CollectionId = -1)). This works consistently but seems hacky.
- Adding a static dummy collection (id = 42 say) with exactly five items so that we can use (OPTIMIZE FOR (@CollectionId = 42)) Which is too gross.
- I would have liked to put a query hint on dbo.Collections (FORCESEEK maybe) but the table doesn't appear anywhere in the insert statement.
I'm leaning toward (@CollectionId = -1) but I want to hear what others think. This pattern can't be too rare. Are there other ideas I'm missing?
You could, for example, use a FAST 1 hint on the insert to CollectionItems. Equally, a LOOP JOIN hint would be effective, even though the source query contains no joins – the join introduced to check the foreign keys will respect the hint.
You could also use a plan guide. A convenient way to do this for a particular statement in a stored procedure is to use sys.sp_create_plan_guide_from_handle. You will need to cache the desired plan shape first, then locate the plan_handle and statement_start_offset.
Using a guide will force the same plan shape for all future executions, which may or may not be desirable (you may want a merge join in some cases). Note that using a plan guide will prevent the stored procedure being ALTERed while the plan guide exists (even if it is disabled). This may not be convenient in your environment.
Using OPTION (RECOMPILE) on the CollectionItems insert statement alone is also a valid option, at the cost of recompiling that statement on each execution, with the possibility of a merge join being selected. Also, that statement will not be cached (though the rest of the procedure will be). Note this differs from using WITH RECOMPILE on the stored procedure definition, which would compile the whole procedure each time, and mean none of the statements within the procedure will be cached (or execution statistics maintained).