Query taking 2 mins in prod while 2 secs in staging

SQLDB 2015-11-06 13:59:28

Hi Experts.

I have this query which runs fine on test server with 2 cores and 8 G B RAM for database of size 900 MB on SQL server 2012.

But on prod with 4 cores and 16 GB RAM it takes 2-3 mins and quite slow being good on resource.

Attached is the plan. As i am new with query tuning please guide on how to proceed.


Aaron Bertrand 2015-11-06 15:51:11
Is the plan attached from staging or production? Would be useful to have both to compare, and also see this post for some potential differences to check (such as whether all indexes are identical, stats are up to date, same @@version and compat level, etc). Also how many columns are in the table represented in the anonymized plan as Schema1.Table1? I find it interesting that it is choosing index scans + key lookups with that many columns instead of just scanning the clustered index.
SQLDB 2015-11-16 16:23:28
Thanks Aaron , you're link was helpful.