MSSQL optimization (tuning&optmization& optimization)-Planned reuse (plan reusing)

In Oracle, in order to reduce the hard parsing within the system, there are a series of measures, such as binding variables, plan sharing (via cursor_sharing parameters), to save system resources. So, does SQL Server have such a mechanism as one of the three major business relations libraries? The answer is yes, so let's take a look at a similar mechanism in SQL Server systems.

SQL Server uses the following four mechanisms to buffer the execution plan of previously executed queries to avoid the re-compilation of these queries, thereby saving system resources.

1) Temporary query buffer (Ad hoc queries caching);

2) automatic parameterization (parameterize automatically);

3) Prepare inquiry (prepared queries);

4) Compile object (compiled objects);

Below, there are four mechanisms for SQL Server, respectively, described below:

1) Temporary query buffering

Before SQL Server2005, temporary plans are occasionally buffered, but we can't expect this feature to work too much. Even in later versions, when SQL Server buffers temporary schedules, the buffers that are buffered can be reused only if the subsequent batch text and the text of those plans exactly match. We can query the reuse of the buffered temporary query plan by the following SQL statement:

SELECT usecounts, Cacheobjtype, ObjType, [text]
From Sys.dm_exec_cached_plans P
Cross APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = ' Compiled Plan '
and [text] not like '%dm_exec_cached_plans% ';

To improve the problem of poor reuse of the buffered temporary query plan, SQL Server provides a feature called the Temporary Load optimization option (Optimize for Ad Hoc workloads option), which we can turn on in the following ways:

EXEC sp_configure ' optimize for ad hoc workloads ', 1;
RECONFIGURE;

GO

You can also turn this feature on by using the Advanced page of server properties in SQL Server Management Studio. When the feature is opened, the same temporary query is compiled for the first time, generating the statement's compile plan root (compiled plan stub) in the plan buffer, which occupies no more than 300 bytes of memory, and contains pointers to the text of the query. When the query is compiled again, the cached plan root is replaced by the compiled plan, which is much larger than the original plan root and consumes two pages of memory space.

You can obtain the information for the compiled plan root and compile plan that is buffered after the feature is turned on by the following query:

SELECT usecounts, Cacheobjtype, ObjType, size_in_bytes, [text]
From Sys.dm_exec_cached_plans P
Cross APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype like ' Compiled plan% '
and [text] not like '%dm_exec_cached_plans% ';

Once this feature is turned on, you can also turn it off with the following command:

EXEC sp_configure ' optimize for ad hoc workloads ', 0;
RECONFIGURE;
GO

You can also turn off this attribute by using the Advanced page of the server properties in SQL Server Management Studio.

2) Automatic parameterization

When it comes to parameterization, SQL Server divides it into two scenarios: simple parameterization and forced parameterization (forced parameterization). This is somewhat similar to the use of the cursor_sharing parameter in Oracle. Let's talk about simple parameterization, and simple parameterization is the default setting and behavior of SQL Server. Sometimes SQL Server automatically parameterize the constants in the query, and subsequent queries, like parameterized query statements, reuse the buffered execution plan of the previously parameterized query. Similarly, we can query and analyze parameterized behavior through the following statements:

SELECT usecounts, Cacheobjtype, ObjType, size_in_bytes, [text]
From Sys.dm_exec_cached_plans P
Cross APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = ' Compiled Plan '
and [text] not like '%dm_exec_cached_plans% ';
GO

In the result, for each query with constants, a buffered compilation plan is generated, which is buffered as a compilation plan for Shell queries (shell query), which is intended to make it easier to follow the same constant query to find the parameterized version of the query. These constant queries are buffered by a compiled plan that consumes less memory space, is about two pages in size, and does not include a full compilation plan. For all of these constant queries, the system will also generate a parameterized query called the prepared query, which prepares the query for a compiled plan that is the complete compilation plan, and therefore takes up more memory space, about four pages in size.

Now that we're done with simple parameterization, let's say forced parameterization.

Sometimes your app uses a lot of similar statements, and you think you might benefit from compiling plan reuse, but unfortunately, the system doesn't parameterize these similar statements. At this point, SQL Server provides a database option, which is parameterization forced, which can be set with the following command:

ALTER DATABASE <database_name> SET PARAMETERIZATION forced;

When this option is set, SQL Server will parameterize the constants in the related query, but there are some exceptions, which can be queried by Microsoft official or other documents. It is worth noting that in order to reuse the buffer compilation plan, it is possible to parameterize the constants in queries, which can sometimes lead to serious performance problems, so the actual application must be specific analysis.

3) Prepare the query

As described above, SQL Server parameterized queries can generate a compilation plan for the prepared query type in the plan buffer. In addition, the other two methods can also generate a compilation plan that prepares the query type. However, they differ from SQL Server parameterized queries in which the user can determine the data type of the parameter themselves. One is to call sp_executesql built-in procedures through T-SQL batches, and the other is to use the prepare and execute methods through the client application. Both of these methods are somewhat similar to those used by binding variables in Oracle.

sp_executesql procedure: Use this procedure to require the user to determine the parameters and their data types, as follows:

sp_executesql @batch_text, @batch_parameter_definitions, param1,... Paramn

The unified compilation plan in the buffer is reused whenever the procedure is called with the same @batch_test and @batch_parameter_definitions parameter values. For example:

EXEC sp_executesql n ' SELECT C2, C3, C4 from test.t1 WHERE c1 = @p ', n ' @p int ', 1;

EXEC sp_executesql n ' SELECT C2, C3, C4 from test.t1 WHERE c1 = @p ', n ' @p int ', 2;

EXEC sp_executesql n ' SELECT C2, C3, C4 from test.t1 WHERE c1 = @p ', n ' @p int ', 3;

Preparation and execution methods: This method is similar to the sp_executesql process, but varies. This method does not require the runtime to pass all the batch command text each time, but only once in the preparation phase, and then the returned handle can be used each time the batch command is run. However, this method generates a compile plan for preparing queries in the plan buffers, but does not generate a corresponding temporary shell query plan similar to automatic parameterization. Similarly, we can get information about compiling plans with the following query:

SELECT usecounts, Cacheobjtype, ObjType, size_in_bytes, [text]
From Sys.dm_exec_cached_plans P
Cross APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = ' Compiled Plan '
and [text] not like '%dm_exec_cached_plans% ';
GO

4) Compiling objects

In the planning buffer, in addition to the compilation plan for the temporary query compilation plan and the preparation query that was mentioned earlier, there is a third compilation plan, which is the procedure type (PROC), which is mainly composed of stored procedures, user-defined scalar functions, and multi-statement table-valued functions. Users have full control over the values and types of these object parameters. The successful execution of these objects will reuse the compilation schedule of the same objects that were previously run by the plan cache. However, users can also force this type of object to generate a new compilation plan through options or rebuilds, for example:

--Stored procedures

EXEC p_test ' EM ';
GO
EXEC p_test ' in ';
GO
EXEC p_test ' in ' with RECOMPILE;

--function

DECLARE @p1 char (11);
EXEC @p1 = Test. F_test ' 123456789 ';
SELECT @p1;
GO
DECLARE @p1 char (11);
EXEC @p1 = Test. F_test ' 987654321 ';
SELECT @p1;
GO
DECLARE @p1 (11);
EXEC @p1 = Test.f_test ' 987612345 ' with RECOMPILE;

Similarly, we can get information about compiling plans with the following query statements:

SELECT usecounts, Cacheobjtype, ObjType, size_in_bytes, [text]
From Sys.dm_exec_cached_plans P
Cross APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = ' Compiled Plan '
and [text] not like '%dm_exec_cached_plans% ';
GO

MSSQL optimization (tuning&amp;optmization&amp; optimization)-Planned reuse (plan reusing)