SQL Server Stored Procedures
SQL Server Stored Procedures (SP) offer an efficient way to separate data logic and business logic, thus allowing for a better design of database solutions. This can help reduce code complexity and include performance enhancements. They also provide high-level abstraction between the process that's running on the front end, making it possible to dynamically generate SQL queries. SP are actions that are stored in a database table rather than in a single stored procedure or trigger. The only requirement is that the action has column names and return values. SPs can be executed through ad hoc SQL query or by using stored procedure language (SPL). These actions are typically created as a new table with a unique ID or by recreating already existing tables.
Although SP provide a convenient way to separate business logic and data logic, they can also introduce some complexity and introduce the unnecessary overhead of constructing the SQL queries. They also require the creation of a Transaction log for each stored procedure. For these reasons, there are some limitations on how many SP we can have in an instance. The limit for the number of stored procedures is 65,535 and is applied for each database within a SQL Server instance. This limit applies to both user-created SPs and transactions logSP (aka "binlog SPs").
Using EXECUTE AS syntax, it is possible to dynamically generate the SQL query that contains the stored procedure. For this purpose, an expanded model can be created through sp_executesql stored procedure. This procedure opens three expanded models in one statement:
The IS_LINKED_TO_ACTIVITY and IS_CONTAINER_OF tables contains the __________ information for linking the event to its container or not. The IS_CONTAINER_OF table is used to know if the event is of a container's type. The EVENT_TYPE table will return the type of event that has been received.
First, the system must be able to find the SP in the database, and then it can issue instructions to execute it. When using sp_executesql, there are two steps:
There are many different ways to navigate in an execution plan with access plans and access method based on how many rows we want to process at a time. In this way you can only specify objects that will be processed for each set of instructions that go through as an optimization. If a nested loop join is used and no indexes are used to process a query, the process will be slow because it must read every row from each table that is listed in the query.
In SQL Server, each SP receives a unique ID (SPID) that identifies it and all its activities. If a SP executes one of its own SPs, then both SPs receive two different IDs:
The first number for each SP is called old SPID (SPID_OLD) and the second one is called new SPID (SPID_NEW). The new procedure will have an ID higher than its parent procedure.
A stored procedure is usually created to perform some operation that has to be repeated periodically. This means that the code has to wait for each execution before it can be executed again. To prevent this, we can use Cursors or variables (Dynamic SQL) and dynamically insert the result of computation.
Since SQL Server 2000 SP2, we have gained a new feature called stored procedure cache (stored Proc Cache). This feature allows us to allocate a certain amount of memory for storing parsed execution plans and parameters without affecting the performance of the user application. This feature works with OLAP tasks only and does not reduce load on any other database engine.
An alternate execution plan allows the optimizer to choose plans that are suitable for executing stored procedures. The optimizer uses this alternate plan when it has already executed the procedure once, and it knows how many rows the procedure returns. The optimizer tells the cursor that it is an alternate plan by setting a cached plan bit (SP:Cache) indicating that it should not be reparsed, and setting a summary bit (SP:SummARY) indicating that the SP has been executed at least once. It is important to note that when a cached SP is reparsed, it will lose its status as an alternate SP.
SP and Scale Out:
The following are some specific scenarios where we may encounter performance issues with SP. If we find ourselves in one of these situations, it may be time to investigate solutions to the performance problems caused by the SPs.
Scenario #1: Multiple Stored Procedures Per User Type
If your application uses a large number of stored procedures, there is a possibility that your queries will be slow due to multiple processes operating on the same set of data. When you run multiple copies of your procedure against the same data set, you can expect two things to happen. First, each process will have to access the data multiple times. This will require additional disk I/O for each query it executes. Second, these multiple processes may interfere with each other, resulting in a slow response time for the user.
Scenario #2: Multiple Stored Procedures Per User Type (Continued)
Another scenario that could cause a performance problem is when you have SPs that are part of separate users. If they all reside within the same database, then they can be in conflict with one another and cause performance issues for users who have queries against them. If you have multiple stored procedures per user type, you are better off moving those stored procedures to separate databases. This will ensure that the SPs do not conflict with one another, and will result in a more optimal execution plan for the queries that access them.
Scenario #3: Stored Procedure Overload and Database Performance
Finally, when using a large number of stored procedures, you may overload SQL Server's parser. When this occurs, it will be necessary to add additional memory to the system or use a larger parse cache size. Keep in mind that if you need to store a greater amount of parsed SPs, SQL Server will also use more memory to execute the SPs.
Conclusion
In the TSQL world, a stored procedure is usually a T-SQL script that sends SQL statements to server memory for later execution. SPs will run under their own connection contexts (SPID), and will have the ability to accept parameters. SPs are also stored in the storage engine itself (in the SQL Server catalog), so even if an instance crashes, the SPs remain in memory and can be automatically executed in other instances of SQL Server.
You can use sp_dropextendedproc and sp_renamedextendedproc stored procedures to drop or rename a SP respectively.
https://msdn.microsoft.com/en-us/library/ms188305(v=sql.120).