will SQL Server generate different execution plans?

Chaitanya Kiran 801 Reputation points
2024-03-24T15:18:22.5566667+00:00

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.
13,998 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Marcin Policht 25,925 Reputation points MVP
    2024-03-24T15:20:11.3033333+00:00

    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.


    hth

    Marcin

    1 person found this answer helpful.
    0 comments No comments

  2. ZoeHui-MSFT 37,671 Reputation points
    2024-03-25T02:39:22.8766667+00:00

    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.

    Regards,

    Zoe Hui


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

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.