will SQL Server generate different execution plans?

Chaitanya Kiran 676 Reputation points

If multiple users are running same query with different parameters, will SQL Server generate different execution plans?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,572 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Marcin Policht 7,795 Reputation points MVP

    Yes, SQL Server may generate different execution plans for the same query with different parameters. This behavior occurs due to parameter sniffing, where SQL Server generates an execution plan based on the specific parameter values passed to the query during its initial compilation. If subsequent executions of the query use different parameter values, SQL Server may adapt the execution plan accordingly to optimize performance for those specific parameter values. However, this can sometimes lead to suboptimal performance if the generated execution plan is not suitable for the new parameter values.



    1 person found this answer helpful.
    0 comments No comments

  2. ZoeHui-MSFT 32,401 Reputation points

    Hi @Chaitanya Kiran,

    Yes, it will.

    If a SQL query has parameters, SQL Server creates an execution plan tailored to them to improve performance, via a process called 'parameter sniffing'. This plan is stored and reused since it is usually the best execution plan. Just occasionally, it isn't, and you can then hit performance problems.


    Zoe Hui

    If the answer is helpful, please click "Accept Answer" and upvote it.

    0 comments No comments