Is the with recompile in Stored Procedure dry?

Original article: Is the with recompile in Stored Procedure dry?

When SQL Server creates or modifies a stored procedure, the with recompile option can be added, but most documents or books are not detailed, it can only be interpreted as "re-compile every time this stored procedure is executed 」. In fact, when executing this stored procedure, it is necessary to force the re-generation of the "Execution Plan" instead of the "cache) to get the old execution plan 」.

When SQL Server evaluates and generates an "Execution Plan", it consumes a lot of CPU resources. Therefore, it is important to make it correct to reuse the old "Execution Plan" from the cache; however, if you misuse the old "Execution Plan", resulting in a significant reduction in the performance of the SELECT query, the loss is more than worth the candle.

For a general SQL query, the content of the two or multiple executed SQL statements must be completely consistent before the old "Execution Plan" can be extended, including: Case Sensitive, line feed, and blank. For example, 1. Two execution plans are generated because the SQL statement executed twice has a blank space 」, the old execution plan cannot be reused 」.

Figure 1 two execution plans are generated when resources are wasted 」

1 DBCC FREEPROCCACHE2 3 SELECT * FROM Customers SELECT * FROM Orders4 GO5 SELECT * FROM Customers  SELECT * FROM Orders6 7 SELECT cacheobjtype, objtype, usecounts, sql FROM sys.syscacheobjects 8 WHERE sql NOT LIKE ‘%cache%‘ AND sql NOT LIKE ‘%sys.%‘

View code

If you use "parameterized query", as follows:
Select * from MERs where customerid = @ customerid
You can avoid generating a new "Execution Plan" because of different parameter values, or avoid SQL injection attacks.

Compared with General SQL statements, stored procedures have the advantages in performance. Besides being compiled in advance, stored procedures can also improve the reusability (reusability) of execution plans ), avoid generating new execution plans and consuming CPU resources. For example 2, when two calls to the same stored procedure but different parameters are passed in, SQL Server will reuse the same "Execution Plan", just like the preceding "parameterized query, it will not waste resources to generate a new "Execution Plan 」.

Figure 2 "Execution Plan" is reused to avoid wasting resources

 1 CREATE PROC spCust1 @CustID NVARCHAR(5) 2 AS 3 SELECT * FROM dbo.Customers  4 WHERE CustomerID=@CustID 5 GO 6  7 EXEC spCust1 ‘ALFKI‘ 8 EXEC spCust1 ‘BERGS‘ 9 10 --DBCC FREEPROCCACHE11 SELECT cacheobjtype, objtype, usecounts, sql FROM sys.syscacheobjects 12 WHERE sql NOT LIKE ‘%cache%‘ AND sql NOT LIKE ‘%sys.%‘

View code

However, if the stored procedure "data content distribution is uneven", for example, a table contains an int type field, the values stored in a large number of records are 1 ~ 100, but only one record stores 10000. That is to say, there are sometimes a lot of records that meet the filtering conditions (the "Execution Plan" is suitable for "index scanning 」), but sometimes there are only one or two matching items ("Execution Plan" is suitable for "index search 」). In the future, both situations may occur when calling this stored procedure. Therefore, we hope that this stored procedure "Do not cache the execution plan" during execution 」, that is to say, when this stored procedure is executed, it is re-evaluated to generate the most appropriate "Execution Plan", and the with recompile option can be added. For example, 3 is left to the front-end application to decide whether to add the with recompile parameter when the AP calls the stored procedure.

Figure 3

1 exec select_proc1 @ key1 = 5 -- auto-selected high-performance "dynamic row analytics" 2 exec select_proc1 @ key1 = 10000 -- extend the "dynamic row analytics" from the cache 」, because it is not ready for use, it will lead to poor performance. 3 exec select_proc1 @ key1 = 10000 with recompile -- Optimized to create a new, high-performance "linear computing 」

View code

There are other advanced option applications, such as using the optimize for option when creating a stored procedure to cache a specific parameter value, to generate a fixed "Execution Plan" with the smallest average impact on performance, and avoid wasting CPU resources by repeatedly generating new "execution plans.

Case study-the same syntax runs slowly in the stored procedure and quickly separately (Hu baijing, Traditional Chinese ):
Http://byronhu.wordpress.com/2010/07/15/with-recompile/

Reference part of this article:

A friend asked an interesting question: the same statement runs slowly in the stored procedure and runs fast separately.

Stored Procedures cache execution plans (with recompile is not added), which can save CPU consumption. However, if the number of referenced records varies greatly during the two execution of this stored procedure, the performance of the second execution will become poor as the old execution plan is used. Observe the following phenomena:

1. Observe the execution plan after execution, but return a large number of records with "index search 」.
2. Use profiler to observe the syntax in the stored procedure, the separately executed syntax, And the IO/CPU/duration values consumed. If you use the syntax of a sentence to obtain the performance of management studio, it is far better than the syntax execution in the stored procedure.

A simple solution is to use the with recompile option when executing or creating a stored procedure.

... In the middle...

The execution situation of the stored procedure can be divided into 80-20 rules. If a few execution conditions are met by the AP, then the AP can determine whether to use with recompile or write a stored procedure directly with option (optimize for (Parameter definition ))

However, in some situations, such as user range query or financial rollback data, a large amount of data is deleted and inserted into the relay table. Developers cannot assess the possible data volume in advance, when a stored procedure is created, it can be directly used with recompile to achieve stable execution performance.

Conclusion: When I wrote an AP in my early years, I could not find it with recompile. At that time, I wrote "Paging (page feed) the with recompile option is added to the stored procedure (double top pinch, or row_number function. In retrospect, we don't need to add it because we can reuse the old "Execution Plan" (which can save a lot of resources on database servers ), the input parameters are similar (the number of pages on which the user is currently located and several records must be returned on each page ).

 1 CREATE PROCEDURE [dbo].[GridView_pager] 2 @StartRowIndex    int, 3 @PageSize int, 4 @tableName nvarchar(50), 5 @columnName nvarchar(100), 6 @sqlWhere nvarchar(1000), 7 @groupBy nvarchar(100), 8 @orderBy nvarchar(100), 9 @rowCount int output10 WITH RECOMPILE11 AS

View code

Related Articles:

SQL Server execution plan Cache
Http://www.cnblogs.com/CareySon/archive/2013/05/04/3058592.html
Http://www.cnblogs.com/CareySon/archive/2013/05/04/PlanCacheInSQLServerPart2.html

Is the with recompile in Stored Procedure dry?