CODEWITHSUNDEEP
sql-serverstored-procedures
crack3r
crack3r

Reputation: 49

Where are stored procedures stored at? Does it create an execution plan when you create it?

Where is the execution plan for a stored procedure stored at? When you create a stored procedure does it create an execution plan then or do you have to run it once before anything is created?

Upvotes: 1

Views: 269

Answers (2)

marc_s
marc_s

Reputation: 755128

The execution plan is created the first time the stored procedure is run - and it's stored in the (volatile) plan cache.

If the server is too busy and needs space for more recent execution plans to be cached, or if the SQL Server service is shut down, that cache goes away and the next time around, the procedure needs to be parsed and an execution plan determined again.

Just by creating the stored procedure, you are not storing any execution plan - the stored procedure aren't pre-compiled or anything like that, as folklore often claims. That is just simply not the case.

As a matter of fact, SQL Server doesn't even check for object existence at the time when you create the stored procedure. You can totally create a stored procedure that select from a non-existing table - and it will be created just fine. The error only occurs at runtime - once the execution plan is attempted to be constructed for the first time (and at that point, of course, the fact that the table isn't present, will cause an error)

Upvotes: 1

Cosmin
Cosmin

Reputation: 3

The stored procedure body is stores in a system table. The plan is cached and reused every time it is executed, Though yes, first it has to be created at first execution.

Upvotes: 0

Related Questions