Question

What are the two stacks per process and what are the purposes of each kind? What can happen if you have too many procedure calls.

Answer 1

Each cycle has two stacks:

User-mode stack: The typical stack that the cycle uses to store its transitory information (or information passed by work calls). In the event that the cycle will have more than one string, it might have a stack for each string.

Kernel-mode stack: When a kernel-mode switch happens, the kernel utilizes this stack to store its transitory information. Each cycle has a kernel-mode stack that must be utilized by the kernel. For instance, when the cycle triggers some framework call, the kernel takes control. It switches the stacks and starts utilizing the kernel-mode stack of that cycle. At the point when the framework call routine is done, the kernel switches back the stacks and the cycle proceeds with its work utilizing the user-mode stack.

The biggest limitation to that hundreds or thousands store procedure is maintainability. Even though that is not a direct performance hit, it should be a consideration. That is an architectural stand point, you have to plan not just for the initial development of the application, but future changes and maintenance.

That being said you should design/create as many as your application requires. Although with Hibernate, NHibernate, .NET LINQ I would try to keep as much store procedures logic in the code, and only put it in the database when speed is a factor.