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
Can you show your query? Do you have indexes on FK columns when you join the tables?
https://www.mssqltips.com/sqlservertip/3257/different-approaches-to-correct-sql-server-parameter-sniffing/

Answer 3

Urgh - wrote it as answer and rather not restart
37444-start-1.xml
37366-rebuild-index-2.xml
37405-recompile-sp-3.xml
37454-newweek-4.xml

rebuild-index-2

Green outliner on "Filter" is part of that index to the right

Answer 4

HI
Can you "cover" you r idx passanger count 2 index with all fields you use in SELECT, you can see them in Key lookup operator which is pretty costly

Example

Create index myidx on tbl (col1) include ( col2,col3)

Answer 5

Well, I still have not seen your query.... Cannot open the files