I am using the Azure SQL Server Database.

It seems that the Azure SQL Server Database creates multiple execution plans regardless of whether Option (Keep Plan) is selected.

CREATE PROCEDURE SelectAllCustomers
@varX DataType-Y...
AS
    SELECT .....
    Where ....
    Order By ....
    Option (Keep Plan)
GO;

As per the definition:

KEEP PLAN Forces the query optimizer to relax the estimated recompile threshold for a query. The estimated recompile threshold is the point at which a query is automatically recompiled when the estimated number of indexed column changes have been made to a table by running UPDATE, DELETE, or INSERT statements. Specifying KEEP PLAN makes sure that a query will not be recompiled as frequently when there are multiple updates to a table.

Any reasons for creating multiple execution plans? Is it fine to go with Option (Keep Plan)?

CodePudding user response：

The screen image in your question is from the query store. This QS report shows a history of the plans, not just the current cached one, so you may see multiple plans for the same query.