CODEWITHSUNDEEP
sql-serverstored-procedures
Adarsh H D Dev
Adarsh H D Dev

Reputation: 618

SQL Server procedure recompile

I have a question: let's say I have a procedure which contains dynamic SQL inside the definition of procedure, so when I execute the procedure for the first time, it's obvious that it compiles the procedure and stores the plan for first time.

What happens during the second run? Will the same plan be used or will the procedure go for a recompile as it contains dynamic SQL in it?

Upvotes: 0

Views: 96

Answers (2)

TT.
TT.

Reputation: 16137

Dynamic SQL is always compiled. It may result in the same execution plan as the first run (totally dependent on parameters).

I would suggest reading this article from MS. Relevant quotes:

Recompilations: Definition

Before a query, batch, stored procedure, trigger, prepared statement, or dynamic SQL statement (henceforth, "batch") begins execution on a SQL Server, the batch gets compiled into a plan. The plan is then executed for its effects or to produce results.

and

Compiled plans are stored into a part of SQL Server's memory called plan cache . Plan cache is searched for possible plan reuse opportunities. If a plan reuse for a batch happens, its compilation costs are avoided.

Upvotes: 1

Bala
Bala

Reputation: 704

A similar question has already been answered in Stack Exchange for Database Administrators. Please refer: https://dba.stackexchange.com/questions/47283/when-does-sp-executesql-refresh-the-query-plan

Upvotes: 0

Related Questions