1

I run my SQL statement twice, including 'Actual Execution Plan'. However, I got two different execution plans for the same query. The only difference is I change ID.

  • First, I run sql with ID which is related to only 5 records.
  • Second, I run sql with ID which is related to 5000+ records.

Does the execution plan change depending on the data?

I'm using SQL Server 2008 R2

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
goutthee
  • 329
  • 4
  • 14
  • 7
    ***YES!*** Of course! SQL Server will analyze the number of rows involved in your query, and depending on the number of rows, it might pick one or another execution plan to be as optimal as possible for all cases – marc_s Jul 08 '16 at 09:29
  • 2
    Look at the execution plans that have been generated - you should notice that each node shows an "estimated number of rows" as well as the actual number of rows - what did you think that was about? – Damien_The_Unbeliever Jul 08 '16 at 09:33
  • You can get instances when the same execution plan is used, this is a really bad thing called Parameter Sniffing. This is basically the optimiser getting confused and can lead to really poor performance. There's plenty of resources on google about this subject if you're interested. – Rich Benner Jul 08 '16 at 09:37
  • see this answer from Remus Rusanu:http://stackoverflow.com/questions/4787205/are-sql-execution-plans-based-on-schema-or-data-or-both – TheGameiswar Jul 08 '16 at 09:45

1 Answers1

0

Does the execution plan change depending on the data?

Strictly speaking, No. The execution plan will change if the query changes or the statistics (which do depend on the data) change.

If the data changes but the stats do not, then your execution plan will stay the same.

MJH
  • 1,710
  • 1
  • 9
  • 19