Multiple Plan Handles for a single Stored procedure RRS feed

  • Question

  • We have an ongoing issue with one of the stored procedures which runs very fast at times ( like less than a second) and slow sometimes( 2-3 minutes)

    Trying to figure out what this might be ..we have added option(recompile) option for parameter sniffing.. it still performs the same

    Upon more research I was able to find that there are multiple plan handles generated for the stored procedure with major difference in min and max worker times.

    how do I know what is the reason for multiple plans and how to avoid them?

    Thank you!

    • Edited by SQLAzure678 Thursday, October 29, 2020 2:19 PM
    Thursday, October 29, 2020 11:49 AM