SQL query plan wrong / fixes with recompile

Question

Is there any good guides on queries that run on multiple servers but sometimes performance tanks i.e. the query plan joins big tables and then filter rather than filter & then join/expand. If I recompile the stored procedure it starts working again i.e. selects 900 rows & expand them rather than expanding 91 million rows from index in cluster & then filter based on index. Could always expand query with tons of hints & force order or subqueries to attempt to force it but feels wrong & cannot do every time I write a query.

A query can go from less than a second or seconds to hours based on query plans suddenly switching order & recompile usually fixes it temporarily, guide to troubleshoot such issues anywhere?

In this case the parameters should be the same since it's a night job - before partially blamed parameter sniffing.

Answer 1

Probably the estimated selectivity is off. Why? Perhaps statistics is off. Or perhaps you are a victim if parameter sniffing going in the wrong direction?

How to handle? It depends, as always. OPTIMIZE FOR can be one option. OPTION RECOMPILE another.

Start with reading this, if you haven't already: http://www.sommarskog.se/query-plan-mysteries.html

Answer 2

Hi @llarsson ，

Thank you so much for posting here.

Please refer below suggestions and check whether any of them is helpful to you.

1. Check whether there is any memory issue in your server.
2. Bring its schema name (fully qualified object name) instead of the object's own name in your statement.
3. Update Statistics.
4. Set the database Parameterization property to Forced.
5. Create Procedure… with Recompile option and EXEC… with Recompile option.
6. Use query hints such as "Keep Plan" or "KeepFixed Plan" while calling this statement.
7. Adjust the index on the tables (change from table scan to table seek). For example, create one covering index like Create index idx on tbl (col1) include ( col2,col3) since there are one key lookup and one index scan in your execution plan.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet

Answer 3

I suggest you read this:

https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/

Answer 4

The issue has fallen in prio, marked something as answered but no silver bullet or extended view/diagnostics to help diagnose but rather the usual add more hints,force query, change queries etc. Was hoping more for something more generall that makes sure thousand of sp don't need specialized query/hints & how to avoid this creating issue somewhere else - i.e. more of a silver bullet, missing a concept how to maintain statistics or a cool view/query that explains why this happenede in this case and how to avoid.

Haven't read sommerskog page yet but can recommend brentozar & its diagnostics queries for generall issue seeking.