[xtrumanx]

I made an app that generated reports. The queries ran quickly when I tested it directly on the database but when I ran my app it would hang on a particular query. Sometimes.

I started making some small seemingly insignificant changes to my code and suddenly my app generated all the reports quickly. All was well until the next month when I had to generate the same reports and my app failed on the same query. I solved it by reverting the small insignificant change I made the previous month. WTF.

I looked into it and there's a wealth of information on the topic[0]. Apparently, how my code passed the query to the database differed to how I tested my query directly on the database which cause the database to compile the query differently and use different execution plans.

My most recent fix was to add another index which the database suggested. Let's hope it works next month.

[0] http://www.sommarskog.se/query-plan-mysteries.html

[hobs]

I do a lot of minor performance tuning in mssql, and while I am not any senior professional, I often see parameter sniffing or a cached plan that makes no sense causing an issue when the server thinks its too expensive to create a new plan, and as a result the cardinality estimates are all wrong, the operators are often wrong, and you can see a query that often runs in insignificant time take forever.

As far as I know, the small changes you made just invalidated the plan (as the code wasnt the same) and make the server recompile your query's plan. You can accomplish the same thing by using sp_recompile

Depending on the code, I have fixed this with a few different answers: 1. You can write code that is clearer and simpler for the optimizer, sargeable queries and all that. 2. You can actually (I think in 2012 or later) issue a plan guide so that the query always runs in the way you decide. 3. You can do more hacky things like deciding to recompile the plan each time you run the query, or do a similar thing by assigning all your parameters to local variables, the query optimizer then cant parameter sniff.

You want to learn a little bit about parameter sniffing, because an execution plan that is poor can often be caused by re-using old values to plan the query,

[ngzhian]

Oh wow, that is a pretty bothersome trouble. I don't know nearly enough about databases and generating reports to understand this, but that link is saved. Thanks for sharing!