Query plan not reused for identical queries sp_executesql vs adhoc

Question

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?

Answer 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.

• Yet Another Post About Local Variables

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

• Plan Caching and Recompilation in SQL Server 2012

What could cause such a dramatic difference in performance?

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

• Why does SQL Server use a better execution plan when I inline the variable?

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'.