In a production application (C# talking to SQL Server 2014 Standard) there's a query that looks like this, below. Most of the time it runs in milliseconds. But occasionally (for certain values of
@Id), it goes nuts and takes a minute or so. This is longer than the app timeout, so the app fails for the user.
In the "goes nuts" cases, the returned result set is correctly empty, as it is in many but not all the other cases.
Luckily this is reproducible both in the production and development environments.
The developer says removing "TOP 1" from the query, then making sure the app consumes the extra rows of the result set, clears up the performance problem.
The query planner suggests no indexes when
TOP 1 is present. (in dev).
Changing the query and fixing the app is in progress. Rollout takes a while.
My question: Is there any DBA-accessible way to tune or tweak the production SQL Server instance to overcome this problem before the app change with the new query rolls out?
SELECT TOP 1 subscription_id FROM subscription AS sub JOIN billing_info AS bi ON bi.billing_info_id = sub.billing_info_id JOIN person_group AS apg ON apg.person_id = bi.person_id JOIN pplan ON pplan.plan_id = sub.plan_id JOIN product ON product.product_id = [plan].product_id JOIN product_attribute ON product_attribute.product_id = product.product_id WHERE apg.group_id = @Id AND apg.start_date < GETDATE() AND (apg.end_date IS NULL OR apg.end_date > GETDATE()) AND (sub.end_date IS NULL OR sub.end_date > GETDATE()) AND product_attribute.attribute_type = 'special feature' AND product_attribute.attribute_data = '1' ORDER BY sub.start_date ASC;