SELECT TOP 1 harms query performance; is there a dba-accessible way to overcome this?

Question

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;

score 12 · Accepted Answer · answered Jul 07 '16 at 10:46

If you can't change the query you can use a plan guide.

Test the performance of the query with OPTION (QUERYTRACEON 4138) (will need someone with sysadmin permissions to try this).

If that produces satisfactory performance you can apply this with a plan guide. If it doesn't produce satisfactory performance try and find a hint that does. Possibly OPTION (HASH JOIN, MERGE JOIN) if inappropriate nested loops is the problem. You might need to resort to the USE PLAN N'...' hint.

Once you know the hint(s) required you can apply them using the information here.

score 0 · Answer 2 · answered Jul 07 '16 at 11:43

for grins give this a try
`> is changed to >= so not exact same query

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 
       AND apg.group_id = @Id
       AND apg.start_date < GETDATE()
       AND isnnull(apg.end_date, GETDATE()) >= GETDATE()             
  JOIN pplan 
        ON pplan.plan_id = sub.plan_id
       AND isnnull(sub.end_date, GETDATE()) >= GETDATE()
  JOIN product 
        ON product.product_id = [plan].product_id 
  JOIN product_attribute 
        ON product_attribute.product_id = product.product_id 
       AND product_attribute.attribute_type = 'special feature' 
       AND product_attribute.attribute_data = '1' 
 ORDER BY sub.start_date ASC;

SELECT TOP 1 harms query performance; is there a dba-accessible way to overcome this?

2 Answers2