Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to implement plan caching in SQL Server. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something from this article.

Parametric and non-parametric

The unique identification of the query plan is the query statement itself, but assuming that the body of the statement is the same, but only the query condition predicate is different, is it one execution plan or two execution plans in the execution plan? It's Depends .

Suppose the following two statements, as shown in figure 3.

Figure 3. Only two sentences with different predicate conditions

Although the execution plan is the same, two execution plans are retained in the execution plan cache, as shown in figure 4.

Figure 4. The same statement, different conditions, there are two different execution plan caches

We know that the execution plan cache depends on the query statement itself to distinguish the cache, so the above two statements are treated as two different statements in the execution plan cache. The way to solve this problem is to make the query statements in the execution plan cache exactly the same.

Parameterization

So that only some parameters are different, and the query itself is the same statement can be reused, is the meaning of parameterization. For example, the statement in figure 3, if we enable forced parameterization of the database, or use stored procedures, and so on. SQL Server will force these statements on parameter words, for example, we have modified the options at the database level according to figure 5.

Figure 5. Database level options

At this point, let's execute the two statements in figure 3. By querying the execution plan cache, we find that the variables are partially parameterized so that the statements in the plan cache become consistent, as shown in figure 6, so that they can be reused.

Figure 6. Query statement after parameter words

However, forcing parameters can cause some problems, and the query optimizer is often unable to optimize some specific queries based on statistics, such as not applying some indexes or finding them when it is time to scan. The negative effects have been mentioned in the previous article, so I won't go into details here.

So there are several solutions to the above problems.

Balanced parameterization and non-parameterization

In specific cases, parameterization is sometimes good, but sometimes it is the culprit of performance problems, so let's take a look at several ways to balance the relationship between the two.

Use RECOMPILE

When the cost of an inaccurate execution plan in a query is higher than the cost of compilation, using the RECOMPILE option in the stored procedure or the RECOMPILE prompt in the ad hoc query causes each query to regenerate the execution plan, which prevents the generated execution plan from being inserted into the execution plan cache. For OLAP-like queries, inaccurate execution plans tend to cost more than compilation costs, so consider this parameter or option, and you can use Hint as shown in the query in listing 1.

SELECT * FROM Sales.CustomerWHERE CustomerID > 20000 AND TerritoryID = 4OPTION (recompile)

Listing 1. Use Recompile

In addition to the fact that we can manually prompt SQL Server to recompile, SQL Server will also automatically recompile under the following conditions:

Metadata changes, such as indicating changes, deleting columns, changing data types, and so on. Statistics change.

Changes in the SET parameters of the connection, different values of SET ANSI_NULLS, etc., will cause the cached execution plan not to be reused, thus recompiling. This is why we see that the statements in the execution plan of the cache are exactly the same, but they are not reused, and the relevant parameters need to be consistent, which can be viewed through sys.dm_exec_plan_attributes.

Use the Optimize For parameter

The RECOMPILE approach provides a rhythm that does not use the plan cache at all. But sometimes, the execution plan of the feature predicate is used more times h, for example, only those predicate conditions produce a large number of parameter compilations that return the result set, we can consider the Optimize For parameter. For example, let's take a look at listing 2.

DECLARE @ vari INTSET @ vari=4SELECT * FROM Sales.CustomerWHERE CustomerID > 20000 AND TerritoryID = @ variOPTION (OPTIMIZE FOR (@ vari=4)

Listing 2. Use the OPTIMIZE FOR prompt

Using this parameter causes the cached execution plan to generate and cache the execution plan according to the predicate conditions after OPTIMIZE FOR, which may also cause inefficient queries that are not in this parameter, but we choose this parameter, so we usually know which predicate conditions will be used more often.

In addition, an additional OPTIMIZE FOR UNKNOWN parameter has been added since SQL Server 2008, which allows the value of the local parameter as a predicate condition to be detected in the process of optimizing the query, rather than detecting statistics based on the initial value of the local variable.

Use local variables instead of stored procedure parameters in stored procedures

Instead of using procedure parameters in stored procedures, using local variables is equivalent to directly disabling parameter sniffing. After all, the value of a local variable is known only at run time, and cannot be known when the execution plan is compiled by the query optimizer, forcing the query analyzer to estimate using the average of the conditional columns.

Although this approach makes the parameter estimation very inaccurate, it will become very stable, after all, the statistics will not change too frequently. This method is not recommended and, if possible, try to use the Optimizer approach.

Listing 3 shows this approach.

CREATE PROC TestForLocalVari@vv INTASDECLARE @ vari INTSET @ vari=@vvSELECT * FROM Sales.CustomerWHERE CustomerID > 20000 AND TerritoryID = @ vari

Listing 3. Refer directly to local variables rather than stored procedure parameters

Forced parameterization

Forced parameterization has been mentioned earlier in this article, so I won't mention it here.

Use plan guidance

In some cases, our environment does not allow us to modify SQL statements directly, such as not wanting to break the logic of the code or that the application is developed by a third party, so it is not realistic to add HINT or parameters. At this point we can use planning guidance.

Planning guidance causes SQL Server to add a hint or option to a query statement when it is thrown into SQL Server by the client application. For example, you can see an example of plan guidance in listing 4.

EXEC sp_create_plan_guide Noble MyPlanGuide1 selected * FROM Sales.Customer WHERE CustomerID > 20000 AND TerritoryID=@vari',@type=N'sql',@module_or_batch=NULL,@params=N'@vari int',@hints=N'OPTION (RECOMPILE)'

Listing 4. Set up plan guidance for our previous query

When the plan guidance is added, when the batch reaches the SQL Server, it also looks for a matching plan cache to see if there is a plan guidance that matches it. If they match, the prompts or options in the plan guide are applied. It is important to note here that the @ stmt parameter must be exactly the same as a sentence in the query statement, and a space missing will be considered a mismatch.

PARAMETERIZATION SIMPLE

When we enable forced parameterization at the database level, we do not want to enable forced parameterization for specific statements, we can use the PARAMETERIZATION SIMPLE option, as shown in listing 5.

DECLARE @ stmt NVARCHAR (MAX) DECLARE @ params NVARCHAR (MAX) EXEC sp_get_query_template N'SELECT * FROM Sales.Customer WHERE CustomerID > 20000 AND TerritoryID=2',@stmt OUTPUT, @ params OUTPUTPRINT @ stmtPRINT @ paramsEXEC sp_create_plan_guide NumberMyTemplatePlanGuideposts, @ stmt, Native templates, NULL, @ params, N'OPTION (PARAMETERIZATION SIMPLE) 'after reading the above, do you have any further understanding of how to implement the plan cache in SQL Server? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.