Query plan not reused for identical queries sp_executesql vs adhoc

All we need is an easy explanation of the problem, so here it is.

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.

Query plan not reused for identical queries sp_executesql vs adhoc

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.

Query plan not reused for identical queries sp_executesql vs adhoc

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.

Query plan not reused for identical queries sp_executesql vs adhoc
Query plan not reused for identical queries sp_executesql vs adhoc

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Can Adhoc cached execution plans be reused by Prepared queries?

Not in this case, because the DECLARE portion of the statements are part of the query text. That’s fundamentally different from when you pass parameters to a stored procedure or dynamic SQL.

You may want to give this a read to better understand why those queries are not "identical".

What could cause such a dramatic difference in performance?

It appears you’re having the opposite problem of this Q&A:

Since the plan was originally compiled with the same set of parameters, a recompile hint isn’t going to change anything. The bad guess you got on initial compilation would carry over to further compilations.

Since you didn’t post the query plans in a way that allows for any additional feedback, some limited ideas that may help:

  • Update statistics on the tables/indexes involved in this query
  • Add an OPTION(HASH JOIN) hint to the query
  • Add an OPTION(OPTIMIZE FOR UNKNOWN) hint to the query

Be cautious about adding query hints without fully understanding the repercussions, and please don’t add these hints to other queries in the hopes that ‘it worked for this one query, so it’ll work everywhere else, too’.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply