Why is a poor performing plan chosen over bad performing plan

Paul Zgondea 2017-03-17 15:53:26

Here is the description of our scenario:

Environment description:
We have an environment with SQL Server 2012 Standard edition on a i2.2xlarge AWS instance with Windows 2012 Standard as our operating system.
The exact version of SQL Server is : Microsoft SQL Server 2012 (SP3-CU4) (KB3165264) – 11.0.6540.0 (X64).
Our Application is using NHibernate to form the queries and they rely on the application's user default schema to generate the statements.
The database that we have an issue with hosts multiple database schemas, each schema having the same table structure.

Description of the problem:
We are experiencing a problem with SQL Server picking a poor performing execution plan and it's hurting our performance (see the attached execution plans).
We think that this might be related to parameter sniffing but we are not sure why SQL Server is not re-evaluating the query costs and use of resources in order to use the good execution plan.

Thins we have tried so far:

Eliminating the bad execution plan from Plan Cache – The good plan is used for a while but then the bad plan takes over and the good execution plan is ignored.

Flushing the whole plan cache – The bad plan is almost immediately created preferred as opposed to the good execution plan.

Restarting the SQL Server instance – same effect as flushing the plan cache.

When we restored only the one schema that we are having trouble with to another database the good execution plan is chosen and the problem is not reproducible anymore.

We are hoping that you are able to help us understand who is generating the bad plan and why this plan is preferred.
link text