Stored Procedures are one of the integral parts of any real application on the web that has any database associated with it. Stored Procedures have a single or a group of Transact-SQL (T-SQL) statements in a single execution plan.
This statement is, or these statements are, stored as precompiled statement(s), in other words the execution plan is already present in the server, that basically saves the server bandwidth as well as the execution time for the required database operations.
There are several reasons and benefits for creating Stored Procedures in database applications such as to enhance the application performance, security, code modularisation, code maintenance and so on.
But sometimes we may have the situation where these Stored Procedures are not working and may result in the degradation of overall application performance. There could be many reasons for that, like table schema, indexes the T-SQL statement and sometime even the type of data it will manipulate.
1. Stored Procedure “Recompilation”
By default this property is disabled. But when we can create a Stored Procedure with using this property.
As a result, every time the Stored Procedure is invoked it ignores, or rather invalidates, the cached/stored execution plan and creates a new one as per the provided parameters to it in case of any.
Syntax
The benefit here is that the best query plan will be created each time it is run. However, recompiling and creating a new execution plan is a CPU-intensive operation.
Note 1: This may not be an ideal solution for Stored Procedures that are run frequently, or on a server that is constrained by CPU resources already.
Note 2: The plans won't be stored in the cache and that makes them harder to find if they are problematic.
Well at the first sight it may contradict the actual needs and necessity for its creation.
But if we look at the execution plan and corresponding costs, we will definitely support it when there is a requirement for the execution of only a selective part of the Stored Procedure on the selective Stored Procedure. For example if there is only a requirement to execute some case statement and some if-else statements in the stored block.
Example
Let's say, the optimal plans for the query:
- SELECT [column name]
- FROM ORDER
- WHERE ORDER-DATE BETWEEN @begin_report AND @from_report
Will be a full scan if the date range is large or an index scan if it's small.
Now if in such a case we create the Stored Procedure with the RECOMPILE option the server engine will build the execution plan each time.
This hint is usually used in procedures processing large volumes of data and doing complex reports, when the overall query time is large and the time for rebuilding the plan is negligible compared with the time saved by a better plan.
Note: One can even go after the new feature of SQL Server 2008 that helps recompile statement level queries rather than the entire Stored Procedure. But this option will be quite useful since such recompilation is dependent upon input data. For example if we are executing a Stored Procedure in which we are passing a parameter like “First Name”, “DateOfBirth” then the statement-level query recompilation option is better. To use this method, one must specify that SQL statement within a Stored Procedure with the RECOMPILE query hint.2. Query “HINTING”
This is yet another way/option to use the “OPTIMIZE FOR” hint. It informs the SQL Server to use a specified value when generating the execution plan.
The result of using this option is useful only if we understand the best information about the data.
Syntax
CREATE PROCEDURE “Procedure name”
[parameters]
AS
SELECT [some columns]
FROM table name
WHERE clause
OPTION (OPTIMIZE FOR passed parameter value));
Assume we are invoking the Stored Procedure with a specific column and we have the knowledge of the data type of the given parameter as well as about the indexes mainly about the non-clustered. This option can provide us the best result in terms of performance.
Note: When a Stored Procedure is compiled or recompiled, the parameter values passed for that invocation are "sniffed" and used for cardinality estimation. The over effect is that the plan is optimized as if those specific parameter values were used as literals in the query. This Phenomenon is known as a “parameter sniffing problem”.
It's an expected behaviour of SQL Server. The preceding two features, “Recompilation” and “Query Hinting”, are one of several workarounds of the parameter sniffing problem.
3. “Nesting & Recursion” Stored Procedures
Recursion is one of the best known programming techniques in terms of optimization and performance.
Sometimes we encounter such scenarios in which a Stored Procedure calls another Stored Procedure or maybe calls itself.
However, it can be dangerous, leading to infinite loops. That's the reason SQL Server limits the number of nesting calls or nesting levels to 32.
Stored Procedures are nested when one Stored Procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate.
The nesting level increases by one when the called Stored Procedure or managed code reference begins execution and decreases by one when the called Stored Procedure or managed code reference completes execution.
If somehow we try attempting to exceed the maximum of 32 levels of nesting causes the entire calling chain to FAIL.
If we wanted to know the current nesting level for the Stored Procedures durng execution, it is stored in the @@NESTLEVEL function.
Abhishek SaxenaPosted Jul 6, 2015, 12:42 AM
Well explained!
Santhakumar MunuswamyPosted Jul 5, 2015, 4:31 AM
Thanks for nice article
SharadPosted Jul 5, 2015, 1:15 AM
Pankaj Kumar Choudhary: Thanks for asking.
Pankaj Kumar ChoudharyPosted Jul 4, 2015, 9:25 PM
Hello Sir! You explained this article very well but i have a doubt you write that we should use " Recompilation option" at the time creation of Stored Procedure. But it has a big disadvantage that it will always recompile every time when we call the stored procedure so it can reduce the performance. We should avoid this method . In stead of this approach we can create a stored procedure without a recompilation option. But we can use recompilation option at the time of execution of stored procedure. We should prefer this method for recompilation of a stored procedure. EXEC My_Proc_ WITH RECOMPILE. Can you tell me "am i right or not ???"
Pankaj Kumar ChoudharyPosted Jul 4, 2015, 9:20 PM
Nice Article Sir.............
Sam HobbsPosted Jul 4, 2015, 7:17 PM
I am interested in anything authoritative that explains or shows that recursion clearly provides better performance (for the computer) than iteration. When the data is naturally recursive, recursion can provide better performance for developers because iterative solutions are not so obvious but data such as that can also be a challenge for storing in a database.