When does SQL server-focus use option (COMPILE)?

Objective

In the previous article we explored the question of using the WHERE Column = @Param OR @Param is null in a static statement, there is a comment on option (COMPILE), and this section explores the issue of option (COMPILE).

Explore option (COMPILE) issues

When a stored procedure is first called at any time in SQL Server, the stored procedure is optimized by SQL Server and the query plan is cached in memory. Because of the query plan cache, when you run the same stored procedure, it uses the same query plan, eliminating the need to optimize and compile the same stored procedure every time you run it. Therefore, if we need to run the same stored procedure several times a day, we can save a lot of time and hardware resources.

If you have the same parameters in the WHERE clause in each run of the stored procedure, it is meaningful to reuse the same query plan for the stored procedure. However, if you run the same stored procedure, but the value of the parameter changes? What happens depends on the typical nature of the parameter. If the values of the parameters of the stored procedure are similar from execution to execution, then the cached query plan will work correctly and the query will follow the execution best. However, if the parameter is not typical, the cached query plan that is reused may not be optimal, causing the query to run slower because the query plan it uses is not really designed for the parameters that are used. Here we use the ADVENTUREWORKS2012 sample database to illustrate the situation described above.

DECLARE@AddressLine1NVARCHAR( -) =NULL, @AddressLine2NVARCHAR( -) =NULL, @CityNVARCHAR( -) =NULL, @PostalCodeNVARCHAR( the) =NULL, @StateProvinceIDINT=NULL SET@City =' Bothell 'SET@PostalCode =' 98011 'SET@StateProvinceID = -DECLARE@SQLNVARCHAR(MAX), @ColumnNameVARCHAR(4000), @ParamDefinitionNVARCHAR( -)SET@ColumnName =' A.addressid, A.addressline1, A.addressline2, a.city, A.stateprovinceid, A.postalcode, A.rowguid 'SET@SQL =' SELECT '+ @ColumnName +' from Person.Address as a WHERE 1 = 1 'IF(@AddressLine1 is  not NULL)SET@SQL = @SQL +' and a.addressline1 like''%''+ @AddressLine1 +''%'''IF(@AddressLine2 is  not NULL)SET@SQL = @SQL +' and A.addressline2 like''%''+ @AddressLine2 +''%'''IF(@City is  not NULL)SET@SQL = @SQL +' and a.city like''%''+ @City +''%'''IF(@PostalCode is  not NULL)SET@SQL = @SQL +' and A.postalcode like''%''+ @PostalCode +''%'''IF(@StateProvinceID is  not NULL)SET@SQL = @SQL +' and A.stateprovinceid = @StateProvinceID ' SET@ParamDefinition = N' @AddressLine1 NVARCHAR,@AddressLine2 NVARCHAR,@City NVARCHAR (+),@PostalCode NVARCHAR (),@StateProvinceID INT 'EXECUTEsp_executesql @SQL, @ParamDefinition, @AddressLine1 = @AddressLine1, @ AddressLine2 = @AddressLine2, @City = @City, @PostalCode = @PostalCode , @StateProvinceID = @StateProvinceIDGO

We run the above query 1 times and see the following query results and the number of scheduled caches as follows:

At this point we will modify the external variable StateProvinceID type to smallint, and then run the query and cache plan, the query plan is the number of times to use 2, or the occurrence of two 1?

In this case, we will change the internal variable stateprovinceid type in dynamic SQL to smallint, when will the query plan use the number of 3, or two, the number is 2 and 1?

From the above, if we modify the external variable parameter type does not affect the query plan cache will achieve reuse purposes, if you modify the dynamic SQL internal variable parameter type will not reuse the query plan cache.

In most cases, we may not need to worry about these issues. However, in some cases, the assumption that the parameters from execution to execution of a query varies greatly can cause problems. If we determine that the stored procedure is normally working, but sometimes it is slow, you will most likely see the above problem. In this case, what we can do is change the stored procedure and add option (RECOMPILE).

When this option is added, the stored procedure will always recompile itself and create a new query plan each time it is run. This, of course, eliminates the benefit of query plan reuse, but ensures that the correct query plan is used every time the query is run. If there are multiple queries in the stored procedure, it will recompile all queries in the stored procedure, even those that are not affected by the atypical parameters.

After option (COMPILE), we'll talk about how to create a slightly higher-performing stored procedure. Some children's shoes may create the following stored procedure.

CREATEPROC [dbo]. [Highperformanceexample] (@AddressLine1NVARCHAR( -) =NULL, @AddressLine2NVARCHAR( -) =NULL, @CityNVARCHAR( -) =NULL, @PostalCodeNVARCHAR( the) =NULL, @StateProvinceIDSMALLINT=NULL) as SETNOCOUNT onSELECTA.addressid, A.addressline1, A.addressline2, a.city, A.stateprovinceid, A.postalcode, A.rowguid fromPerson.Address asAWHERE(A.addressline1 = @AddressLine1OR@AddressLine1 is NULL) and(A.addressline2 = @AddressLine2OR@AddressLine2 is NULL) and(a.city = @CityOR@City is NULL) and(A.postalcode = @PostalCodeOR@PostalCode is NULL) and(A.stateprovinceid = @StateProvinceIDOR@StateProvinceID is NULL)--orSELECTA.addressid, A.addressline1, A.addressline2, a.city, A.stateprovinceid, A.postalcode, A.rowguid fromPerson.Address asAWHEREA.addressline1 =COALESCE(@AddressLine1, A.addressline1) andA.addressline2 =COALESCE(@AddressLine2, A.addressline2) andA.city =COALESCE(@City, a.city) andA.postalcode =COALESCE(@PostalCode, A.postalcode) andA.stateprovinceid =COALESCE(@StateProvinceID, A.stateprovinceid)--orSELECTA.addressid, A.addressline1, A.addressline2, a.city, A.stateprovinceid, A.postalcode, A.rowguid fromPerson.Address asAWHEREA.addressline1 = Case  when@AddressLine1 is NULL  ThenA.addressline1ELSE@AddressLine1END  andA.addressline2 = Case  when@AddressLine2 is NULL  ThenA.addressline1ELSE@AddressLine2END  andA.city = Case  when@City is NULL  ThenA.cityELSE@CityEND  andA.postalcode = Case  when@PostalCode is NULL  ThenA.postalcodeELSE@PostalCodeEND  andA.stateprovinceid = Case  when@StateProvinceID is NULL  ThenA.stateprovinceidELSE@StateProvinceIDENDGOSETNOCOUNTOFF

No matter how this is done, it will behave badly. Because SQL Server does not optimize it well, if it is produced by different combinations of parameters, then we may get an absolutely bad plan. It is not difficult to understand when a stored procedure is executed and a query cache plan has not been generated. Therefore, administrators may update statistics or force recompilation (or even restart SQL Server) to try to resolve this issue, but these are not the best solutions. OPTION (COMPILE) recompile is a good thing, but we are not as simple as the following to recompile it.

SELECT ...FROM ...WHERE ...OPTION (RECOMPILE);

If we are going to use recompilation, do we need to consider the following two questions?

If we know that a particular statement always returns the same number of rows and uses the same plan (and we have tested and know this), then we will normally create the stored procedure and let the plan get cached.

If we know that a particular statement is not the same from execution to execution, the best query plan will also be different (we should also know this from the execution of multiple test samples), and then we will create the stored procedure as normal and then use option (RECOMPILE) To ensure that the statement's schedule is not cached or saved by the stored procedure. At each execution, the stored procedure obtains different parameters so that the statement will get a new plan each time it executes.

To achieve this, we need to analyze the stored procedures that we are querying, such as having a corresponding user under each enterprise, and we can imagine the UI interface being rendered, first of all the users, the query condition is the Enterprise dropdown box, then the user name or employee number. When there are no filters, go to the query plan cache and recompile if you select an enterprise or if you also selected employee-related filters. A stored procedure similar to the following.

CREATEPROC [dbo]. [Highperformanceexample] (@AddressLine1NVARCHAR( -) =NULL, @AddressLine2NVARCHAR( -) =NULL, @CityNVARCHAR( -) =NULL, @PostalCodeNVARCHAR( the) =NULL, @StateProvinceIDSMALLINT=NULL) as SETNOCOUNT onDECLARE@SQLNVARCHAR(MAX), @ColumnNameVARCHAR(4000), @ParamDefinitionNVARCHAR( -), @Recompile BIT =1;SET@ColumnName =' A.addressid, A.addressline1, A.addressline2, a.city, A.stateprovinceid, A.postalcode, A.rowguid 'SET@SQL =' SELECT '+ @ColumnName +' from Person.Address as a WHERE 1 = 1 'IF(@StateProvinceID is  not NULL)SET@SQL = @SQL +' and A.stateprovinceid = @StateProvinceID ' IF(@AddressLine1 is  not NULL)SET@SQL = @SQL +' and a.addressline1 like @AddressLine1 'IF(@AddressLine2 is  not NULL)SET@SQL = @SQL +' and a.addressline2 like @AddressLine2 'IF(@City is  not NULL)SET@SQL = @SQL +' and a.city like @City 'IF(@PostalCode is  not NULL)SET@SQL = @SQL +' and a.postalcode like @PostalCode 'IF(@StateProvinceID is  not NULL)SET@Recompile =0IF(PATINDEX ('%[%_?] %', @AddressLine1) >=4        ORPATINDEX ('%[%_?] %', @AddressLine2) =0) and(PATINDEX ('%[%_?] %', @City) >=4        ORPATINDEX ('%[%_?] %', @PostalCode) =0)SET@Recompile =0IF@Recompile =1BEGIN    SET@SQL = @SQL + N' OPTION (RECOMPILE) ';END;SET@ParamDefinition = N' @AddressLine1 NVARCHAR,@AddressLine2 NVARCHAR,@City NVARCHAR (+),@PostalCode NVARCHAR (),@StateProvinceID SMALLINT 'EXECUTEsp_executesql @SQL, @ParamDefinition, @AddressLine1 = @AddressLine1, @ AddressLine2 = @AddressLine2, @City = @City, @PostalCode = @PostalCode , @StateProvinceID = @StateProvinceIDGOSETNOCOUNTOFF

Summarize

In this section we explain how to use option (COMPILE) in a stored procedure and make the best performance for the stored procedure, which I think is the relative preferred and best way to analyze the stored procedure based on the actual scenario, so that we don't have to recompile every time. This gives us a long-term, better scalability.

When does SQL server-focus use option (COMPILE)?