Suggestions on using plan Buffering

The basic purpose of plan buffering is to improve performance by reusing the execution plan. It is important to confirm that the execution plan is reusable. Because the reusability of ad hoc queries is inefficient, we recommend that you use predefined workload technologies as much as possible. To ensure the efficiency and practicality of plan buffering, follow the suggestions below.

• Explicitly parameterized variable part of the query;
• Use stored procedures to implement business functions;
• Use sp_executesql to avoid stored procedure maintenance;
• Use the preparation/execution mode to avoid resending the query string;
• Ad hoc queries are avoided;
• Use sp_executesql instead of execute for dynamic queries;
• Carefully parameterized variable part of the query;
• Avoid modifying the environment settings between links;
• Avoid implicit parsing of objects in the query;

1. variable part of explicitly parameterized Query

Queries are generally run multiple times. The only difference between each run is the different value of the variable part. Their plans can be reused. However, the static and variable parts of the query should be separated. Although SQL Server has simple parameterization and forced parameterization features, they have various limitations. Standard predefined workloads should always end with explicit execution of parameterization.

Ii. Use stored procedures to implement business functions

If you have already explicitly parameterized the query, placing it in the stored procedure may bring the best reusability. Because only parameters and stored procedure names need to be sent, network traffic is reduced. Stored procedures are pre-compiled and run faster than ad hoc queries. Stored procedures can also maintain a single parameterized plan for a set of queries contained in stored procedures, rather than maintaining a large number of small plans for separate queries. This prevents the plan buffer from being filled by the separately queried plan.

Iii. Use sp_executesql for programming to avoid maintenance of Stored Procedures

If you want to maintain the objects required for the stored procedure or use the query generated by the client, use sp_executesql to submit the query as a predefined workload. Unlike the Stored Procedure Mode, sp_executesql does not create a persistent object in the database. Sp_executesql is suitable for executing single queries or small batch queries. To implement the complete salesman logic in the stored procedure, you can also use sp_executesql as a large query string for submission. However, as the complexity of business logic increases, it is difficult to create and maintain a query string for the entire logic.

4. Implement the preparation/execution mode to avoid re-transmission of query strings

Sp_executesql needs to send query strings through the network each time it is re-executed. It also requires the server to query string matching to confirm the overhead of the corresponding execution plan in the process buffer. In ODBC or oledb (oledb. Net) applications, you can use the preparation/execution mode to avoid re-Uploading query strings in multiple executions, because you only need to submit the scheduler handle and parameters.

In preparation/execution mode, the plan can be reused by other users because the plan handle is returned to the application-this is not limited to users who create the plan.

5. Ad hoc queries are avoided

Do not use ad hoc queries to design new applications. The execution plan created for the ad hoc query cannot be reused when the query is resubmitted with different variable part values. Although SQL Server has simple parameterization and forced parameterization to isolate the variable part of the query, because SQL Server is conservative in parameterization, this feature is only limited to simple queries. For better plan reusability, the query is submitted as a predefined workload.

6. dynamic query of sp_executesql is better than execute

The SQL query string dynamically generated in a stored procedure or database application should be executed using sp_executesql instead of the Execute Command. The Execute Command does not allow explicit parameterization of the variable part of the query.

Execute the following statement:

DECLARE @n VARCHAR(3)SET @n = ‘678‘DECLARE @sql VARCHAR(MAX)SET @sql = ‘SELECT * FROM PersonTenThousand INNER JOIN Province ON PersonTenThousand.PId = Province.Id WHERE PersonTenThousand.Id = ‘ + @n + ‘‘ EXECUTE (@sql)

An ad hoc plan is generated:

　　

If you change:

DECLARE @n NVARCHAR(3)SET @n = 776DECLARE @sql NVARCHAR(MAX),@param NCHAR(6)SET @sql = ‘SELECT * FROM PersonTenThousand INNER JOIN Province ON PersonTenThousand.PId = Province.Id WHERE PersonTenThousand.Id = @1‘SET @param = N‘@1 INT‘ EXECUTE sp_executesql @sql,@param,@1 = @n

The result is as follows:

　　

We can see that a parameter plan is generated in the result.

7. Carefully parameterized variable part of the query

Be careful when converting the variable part of the query to a parameter. The value range of some variables may change sharply, so that the value range of a plan may not be suitable for other values, which may cause parameter sniffing. Handle the issue as needed.

8. Do not allow implicit parsing of objects in the query

SQL Server allows you to create multiple database objects with the same name under different schemas.

For example, table T1 can be created under a separate owner using two different architectures (U1 and U2. In most systems, the default owner is DBO (database owner ).

If user U1 queries:

SELECT * FROM t1 WHERE c1 = 1

SQL Server first tries to query whether table T1 exists in the default U1 architecture. If no, check whether table T1 of the dbo user exists. This implicit parsing allows U1 to create another instance of table T1 in different architectures and temporarily access it (using the same application code) without affecting other users.

On the production server, we recommend that you use the architecture owner and avoid implicit parsing. Otherwise, the following overhead will be added on the production server using implicit Resolution:

• The confirmation object takes more time;
• Reduces the reusability of plan buffering;

 

Suggestions on using plan Buffering