Sams Teach Yourself Transact-SQL in 21 Days, Second Edition

Q&A

Q1:	Why are multiple execution plans kept in memory for a single procedure?
A1:	Each execution of a stored procedure requires its own execution space to store any session information (temporary table names, cursor information, or variable names). This space is also where the procedure actually runs. To allow multiple users to execute the same procedure at the same time, the server needs to load multiple versions of the procedure into memory.
Q2:	There is a lot of work involved in using stored procedures. Do their benefits outweigh the amount of work required?
A2:	Absolutely! Stored procedures provide performance benefits that include less network traffic and the storing of the procedures'optimization for use by many users. Many of the actions ...