CODEWITHSUNDEEP
sql-server
J.T.
J.T.

Reputation: 2616

Execution plan caching - Ad Hoc Query in SMSS vs. parameterized query

Whenever I create ad hoc queries in management studio, consecutive runs of the query without any change do not show any parse / compile time because it appears that the algebrizer has assigned a hash to the query and it just reuses the plan. This is even true for a batch of queries. However, as soon as I introduce creation of a temp table, it appears to never cache the plan as I always see parse and compile time which is nearly 75% of the execution time

On the other hand, I do not see this same symptom when I run the same query (creating a temp table, using it, and dropping it) from our Java application. It appears that each consecutive call to the query is fast, never being bothered with parse/compile time. The difference being that the query is parameterized and run through sp_executeSQL.

This makes it difficult to debug a query in SSMS because I cannot get the times consistent with the application. Do I need to actually take my ad hoc query and run it through sp_executeSQL in SSMS to avoid this?

Edit: Yes, running it through sp_executeSQL does avoid the parse time.

Upvotes: 0

Views: 329

Answers (1)

Bacon Bits
Bacon Bits

Reputation: 32200

Well, the doc for sp_executeSQL says:

sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement many times when the change in parameter values to the statement is the only variation. Because the Transact-SQL statement itself remains constant and only the parameter values change, the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.

The behavior you're seeing seems to be exactly that. So, yes, to match the performance footprint of your application, you will likely need to employ sp_executesql.

You could do things like using plan hints or forcing query plans, but that seems both ugly and risky.

Upvotes: 1

Related Questions