Optimizer struggling with smaller dataset

[Damian Harvey]

Hi all,

I have two datasets in two databases with identical structures:
- Database A is our production dataset
- Database B is our QA dataset and is roughly half the size of database A

For some reason one of our queries runs in about 2-3 seconds on database A but takes more than 10 minutes on database B.

This is definitely a data issue as I've exported the data in the relevant tables from A and loaded it into B (ie. same structure) and the query runs quickly.

The query plans are different for each dataset. I can only assume that the Optimizer is adjusting it's logic based on the data?

What could cause this behaviour? What settings can I adjust to try to remedy the situation?

Many Thanks,

Damian.