skjoldtc (3/1/2010)
If stored procedures are never saved to disk, why do you have to bother compiling them? This is a misconception (or misunderstanding by me) on my part. I'm still learning, so, it's probably me.Doesn't some part of a SP need to be stored somewhere? Like maybe just the source code or byte code? Otherwise, how would SQL Server ever find it when called?
The answer to QoD does not mention anything about the storage of the proc on disk. It simply states that the execution plan of the procedure is never stored on disk. The text of the procedure is stored on disk the moment the procedure is created of course. When the procedure is executed first time after creation then the execution plan is created and stored in memory. It is assigned what is called age of the plan at this time. The execution plan will be stored in memory while the value of the age did not go down to 0 yet. The algorithm assigning and modifying the value of the execution plan age depends on the complexity of the plan as well as on frequency of procedure execution requests.
The fact that the execution plan of the stored procedure is not even created, much less stored, at create procedure time explains, for example, why it is possible to reference a table which does not even exist and still allow the procedure to be created successfully. At run time (because there is no plan stored in memory yet) the engine will attempt to generate one and break at this point because the proc is referencing a table which does not yet exist.
This is an easy to answer but excellent, clean a whistle question.
Oleg