SQLTeam.com | Weblogs | Forums

Same query, same workstation, different execution plan


#1

I ran the same query on the same workstation against two different databases that yielded completely different execution plans. The structure and indexes are the same. The only difference is the data. The strange thing is the data in “database1” is much less than the data in “database2” and the performance is worse (database2 takes ½ the time as database1). You’ll notice an obvious design flaw in the ItemProperties table and we plan on changing the structure of this poorly designed table, however before that takes place I’m looking for optimization ideas. Here is the info for both databases.

Database1: [Actual Execution Plan] https://www.brentozar.com/pastetheplan/?id=HyVv5-PE-

Database2: [Actual Execution Plan] https://www.brentozar.com/pastetheplan/?id=rJrzjbDVW


#2

what is

  1. recovery model of each
  2. are you backing them both
  3. is there an index different between the two databases?

#3
  1. Recovery model is Full
  2. Yes they are being backed at regular intervals.
  3. Indexes are identical.

#4

Hi. This occurs because the data is different. SQL Server keeps statistics on tables. The optimizer uses these statistics to try to create an optimal plan. Different data means different statistics on the table, so it is possible the execution plans will be different.

If your update statistics haven't been run recently, do that. Otherwise, SQL server sometimes gets it wrong, i.e. doesn't create a good plan. In which case, you could try forcing it to use a similar execution plan as the faster one with table query hints.