Best way to use OPTIMIZE FOR hint

mjswart 2015-02-13 18:14:16

Consider two tables dbo.Collections and dbo.CollectionItems and a stored procedure dbo.s_CopyCollection see attachment.

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?

Aaron Bertrand 2015-02-13 18:51:35
With OPTION (RECOMPILE) I got seeks and no huge discrepancies between estimated and actual rows.
mjswart 2015-02-13 19:13:04
Thanks Aaron, I'll consider the RECOMPILE hint. The one thing that I may have to accept is that the sproc will stay out of the cache and so it will be ignored in some of our performance reports.
Aaron Bertrand 2015-02-13 19:14:33
Right, but remember there are other ways to monitor procedure / statement performance aside from relying on their presence in the cache. ๐Ÿ™‚
mjswart 2015-02-13 19:21:09
Really. Tell me more about such magical things ๐Ÿ™‚
SQLkiwi 2015-02-14 14:59:19
There are a number of potential solutions.

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).

mjswart 2015-02-16 20:56:31
Thanks Paul, There are several options to choose from here. I never considered the FAST 1 hint and I forgot (or never knew) that a LOOP JOIN hint was also a query hint as well as a join hint.