Sessions, Processes, Object Instances, Sequential and Parallel Processing Theory

Question

A stored procedure contains a view which is accessed multiple times with the confines of the SP.

If the SP is executed multiple times in one query window with different parameters, e.g.

EXEC spMySP 1, 2, 3
GO
EXEC spMySP 4, 5, 6
GO

Questions

• If one assumes that the first call will complete execution before the second one starts and therefore the view within the SP, will it be bound by the connection, session and process?
• If we were to place each SP execution in its own query window and execute the all, roughly at the same time - we assume they would execute asynchronously?
• Can we assume that the view would within the SP be operating within a new instance (More a theoretical question)?

Answer 1

The SPs will be queued and run based on available resources. Depending on the isolation level used in defining the SP and how the transactions are defined, the SPs will wait accordingly. By design, the SP will not always wait for the first SP to finish, and then move onto the second one etc...

This design make is possible for multiple unrelated users to call the SP and work concurrently.