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

How to understand the parameterization of SQL Server SQL performance optimization, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Mode of database parameterization

There are two ways to parameterize the database, simple (simple) and mandatory (forced). The default parameterization defaults to "simple". In simple mode, if each SQL sent is exactly the same, it will be recompiled (automatic parameterization in special cases, which is the focus of this article).

The mandatory mode is to force the adhoc SQL to be parameterized to avoid recompiling each run due to different parameter values, which is not detailed here.

First of all, thanks to the hint of the "Xiaoxiang hermit" god, I also encountered a practical problem at that time. I found that the estimate of the data row in the execution plan was all wrong. It was observed that no matter how to change the parameters, the SQL statement was not recompiled before execution. I was confused for a long time. This problem is precisely caused by the reuse of the execution plan caused by some SQL automatic parameterization in the simple parameterization mode, and it is also the focus of this article.

This question has been written before, and it was only said in theory in the book at that time. I did not think of its impact. This parameter is a data-level option. For settings, please refer to the figure below.

Under what circumstances will it be parameterized automatically

In simple parameterization mode, SQL Server automatically parameterizes Adhoc SQL statements that have one or only one way of execution, so as to achieve the purpose of reusing the execution plan.

Exactly which types of SQL will be automatically parameterized will be illustrated later.

What are the problems with automatic parameterization?

In simple mode, SQL automatically parameterizes him for some SQL to avoid recompiling every time.

The behavior of SQL Server automatically parameterizing SQL statements can avoid some recompiling, which is originally out of "good intentions", but such "good intentions" often do not always bring benefits to us.

Give an example to illustrate the circumstances under which automatic parameterization will occur.

Create a simple test environment first

Create table TestAuotParameter (id int not null, col2 varchar (50)) GO declare @ I int=0 while @ i100000 begin insert into TestAuotParameter values (@ I, NEWID ()) set @ i=@i+1 end GO create unique index idx_id on TestAuotParameter (id) GO

The reason for the automatic parameterization of the SQL statement is that the SQL statement select * from TestAuotParameter where id=33333 (66666 ~ 99999) determines that there is and only one efficient execution mode (that is, index lookup) under the current data volume and the characteristics of the index. Here, there is and only one way is that the amount of data in the table is relatively large, and because the index idx_id is unique. If it's not unique, then the situation is different. Let's explain what there is and only one efficient execution plan.

Screenshot below: for the same test, I delete the * index on id, create it as a non-* index, and then do the same test. I will find that executing the same SQL is not automatically parameterized.

Let's explain why, how does the index type relate to the execution plan cache?

For non-* indexes, it is possible that citation lookups are efficient, and it is possible that full table scans are efficient (for example, the data distribution of an ID is particularly large). At this time, the execution plan may be diverse, and there is not only one way, so the SQL will not be parameterized automatically.

Problems in automatic parameterization

The benefits of automatic parameterization are needless to say, because the cached execution plan can be reused to avoid the problem of recompiling every time the parameter values are different. When it comes to the reuse of executive plans, one of the topics we have to talk about is parameter sniff.

Yes, automatic parameterization because different parameters reuse the execution plan generated by * compilation, which is likely to cause parameter sniff problems and other problems derived from parameter sniff.

Also use an example to demonstrate, this problem is recently encountered when observing the implementation Plan Statistics (statistics) estimation problem, which puzzled me for a long time. Here, thanks again to the Xiaoxiang Hermit.

This problem is also due to automatic parameterization of SQL statements, resulting in execution plan reuse, resulting in an extremely simple SQL execution efficiency is low in some cases, why the reason for automation parameters is similar to the above, there is only one way of implementation (index lookup), different parameters execution plan reuse leads to misestimation of data rows. Clear the cache execution plan before testing, and observe the estimate of data rows in the actual execution plan under different query conditions.

The query criteria are as follows:

1. The initial query condition is: CreateDate > '2016-6-1' and CreateDate

2. Update the query condition to: CreateDate > '2016-6-1' and CreateDate

3. Update the query condition to: CreateDate > '2016-6-1' and CreateDate

Found no, because the query period changes, the actual number of rows also changes, but regardless of the actual number of rows, the estimated number of rows is always the number of rows that perform the estimate * *.

This must be wrong, right? No matter what conditions are brought in, the estimated number of rows is 37117, which was suddenly fooled at that time. Why is the estimate of data rows the same every time SQL is executed?

In fact, this problem is the same as the example at the beginning, the SQL statement is automatically parameterized, resulting in execution plan reuse, execution plan reuse, resulting in misestimation of the number of rows of data in the actual query.

How to solve the problem of incorrect reuse of execution plan caused by automatic parameterization

Many problems have found the real cause, and it is often not difficult to solve. This problem is caused by the reuse of the execution plan, so we only need to solve the problem of the reuse of the execution plan. Instead of letting him reuse the execution plan, he only needs to add a prompt to the SQL statement, that is, select COUNT (1) from Test20160810 where CreateDate > '2016-6-1' and CreateDateOPTION (RECOMPILE)

The reason is that after adding the query hint of OPTION (RECOMPILE), the execution plan cache of SQL is not cached, and without the execution plan cache, there is no reuse.

For example, in this query, add an OPTION (RECOMPILE) query prompt to the query statement to recompile the SQL statement before execution, and he can correctly estimate the data rows.

Through an actual case to illustrate what is the automation parameters in the simple parameter mode, what problems the automation parameters will bring, and how to solve them. The problem itself is very simple, and there will be occasional confusion if you do not pay attention to it.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, 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.