One of the queries in my application is timing out. With the SQL Server profiler I was able to get the exact faulty SQL code. It's an
sp_executesql dynamic statement that runs in > 2 minutes in SSMS on the server. In contrast, running the same SQL ad hoc - outside of
sp_executesql but still with parameters - only takes 600 ms.
Looking at the cached execution plans, the 2 queries have the exact same query hash but different plans. I DBCC FREEPROCCACHEd the Prepared one (slow one) hoping the following execution would reuse the plan for the Adhoc, fast query. But it doesn't. Running the sp_executesql query always repops a new, slow plan.
Following the guide here, I looked into the possibility of parameter sniffing. Only to find that my 4 parameters are exactly the same in both execution plans.
I also tried
OPTION (RECOMPILE) as well as updating the involved tables statistics with no results.
The execution plans have 2 main branches that are very similar except the slower one has nested loops at the junction of the branches while the fast one doesn't. The lower branch leaves are index scans in both plans, except the slower one reads millions of rows and the faster one, thousands.
There is no obvious difference in the use of indexes in the faster one. The top branch is just organized a bit differently.
Can Adhoc cached execution plans be reused by Prepared queries? What could cause such a dramatic difference in performance?