13

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;
  • Have you tried it as a subquery? Eg select top 1 subscription_id from (select [the rest of your query without the top1] – SeanR Jul 7 '16 at 12:46
  • Maybe some "normal" query tuning would work? If the indexes are attractive enough the scans go away. That's less invasive than a plan guide. – usr Jul 7 '16 at 12:57
  • So does the same values of @ID always make it "go nuts"? If so, then test using one of those values and capture the actual query plan. That will tell you what is going wrong. If the "bad" values are not consistent, then it seems likely that this is either parameter-sniffing (see @MartinSmith's answer for solution), or else a locking problem involving how the client actually request and consumes the result set. – RBarryYoung Jul 7 '16 at 18:41
12

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.

  • OPTION (QUERYTRACEON 4138) did the trick. Thanks. Now on to sorting out plan guides. – O. Jones Jul 7 '16 at 11:37
0

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;
  • "Changing the query and fixing the app is in progress. Rollout takes a while." the OP is looking for a solution that fixes the performance "as is". – Martin Smith Jul 7 '16 at 11:56
  • @MartinSmith And when they do roll out a fix this may be a better fix. Making the app consume the extra rows will be other program changes. – paparazzo Jul 7 '16 at 12:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.