Search code examples
sql-serverstored-proceduressql-execution-plan

Multiple execution plans for one stored procedure depending on used parameters

Is possible to store multiple execution plans for one SQL procedure depending on used parementers? I mean I have one SQL procedure. There are some typical patterns of calling this procedure (some input parameters are left NULL) and I don't want then plan be recalculated every single call.

Solution

  • The answer is Yes, you can have multiple execution plans for a single stored procedure based on input parameters. This is known as parameter sniffing.

    If there is a set of parameters that produces a good enough plan that all invocations of the procedure perform well, one option is to use the optimize for hint:

    OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] )

    This will avoid using the approach which requires a recompile of the proc for all calls.