SQL Server SQL Performance Optimization--Database in "simple" parameterized mode, the problem of automatic parameterization of SQL

database parameterized Mode

There are two ways to parameterize a database, simple and mandatory (forced), and the default parameterization is "simple" by default.
In simple mode, if the SQL is sent every time, unless it is exactly the same, it will be recompiled (special circumstances will be automatically parameterized, it is the focus of this article)
The mandatory mode is to force the Adhoc SQL to be parameterized, to avoid the time of each run because the parameter value of the different and recompile, here is not explained in detail.

This first of all to thank the "Xiaoxiang hermit" the Great God's hint,
There was also a practical problem,
Find the execution plan on the data row estimates, how is wrong, have observed no matter how to change the parameters, the SQL statement is not recompiled before execution, puzzled for a while,
This problem is the simple parameterization mode, some SQL automatic parameterization caused by the implementation of plan reuse, is also the focus of this article to express.
This question has been written before, at that time was only read in theory so, did not think of its impact

This parameter is a data-level option that can be set up to refer to

　　

What is the automatic parameterization of the case?

In a simple parameterized mode, SQL Server automatically parameterize a adhoc SQL statement with only one execution mode, thus achieving the purpose of reusing the execution plan.
What types of SQL are automatically parameterized and are illustrated later.

What are the problems of automatic parameterization

In simple mode, SQL automatically parameterize some SQL to avoid recompiling every time.
SQL Server automatically parameterize the behavior of SQL statements, can avoid some recompilation, originally also out of "good intentions", but this "kindness" often does not always bring us benefits.

Give an example of what is automatically parameterized

Create a simple test environment first

CREATE TABLE testauotparameter (    ID int not NULL,    col2 varchar ()) Godeclare @i int=0while @i<100000begin    INSERT into testauotparameter values (@i, NEWID ())    set @[email protected]+1endgocreate unique index idx_id on Tes Tauotparameter (ID) GO

The SQL statement is automatically parameterized because of the SQL statement from the SELECT * from Testauotparameter where id=33333 (66666,99999).
The characteristics of the current data volume and the unique index determine that there is only one efficient way to execute (that is, index lookup)
There is only one way to say that there is a relatively large amount of data in the table, and because the idx_id index is unique. If it's not unique, then the situation is different.

Here's an explanation of what is there and only one efficient execution plan

As follows: The same test, I delete the unique index on the ID, created as a non-unique index, and then do the same test, you will find that the same SQL is not automatically parameterized

Here's why, how does the index type relate to the execution plan cache?
For non-unique indexes, it is possible to make a lookup is efficient, it is possible to do a full-table scan is efficient (such as a particular ID of the data distribution is particularly many)
At this point the execution plan may be diverse, not just one way, so there is no automatic parameterization of SQL

Problems with automatic parameterization

Automatic parameterization benefits do not need to be said, because the cached execution plan can be reused, avoiding the problem of recompiling each time the parameter value is not the same
When it comes to the reuse of execution plans, one of the topics that has to be said is that parameter sniff, lip are worn out.
Yes, automatic parameterization because different parameters reuse the execution plan generated by the first compilation, it is likely to cause parameter sniff problems, and other problems parameter sniff derive from

　　

Also use an example to do the demonstration, the problem is a recent observation of the Execution Plan statistics (statistics) estimate problem encountered a problem, let me confused for a while,
Here again to thank Xiaoxiang hermit.
The problem is also due to the automatic parameterization of SQL statements, resulting in the execution plan reuse, resulting in an extremely simple SQL execution efficiency in some cases lower conditions,
Why the automation parameter is similar to the above, there is only one way of Execution (index lookup), the different parameters of the execution plan reuse resulting in a false estimate of the data rows.

　　

Clear the cache execution plan before testing, and observe the estimates of the data rows for the actual execution plan under different query conditions

The following query criteria:

1, the initial query condition is:createdate> ' 2016-6-1 ' and createdate< ' 2016-6-2 ', observe the execution plan, the actual number of rows is 37903, the estimated number of rows is 37117, the estimate is accurate

2, update the query criteria to:createdate> ' 2016-6-1 ' and createdate< ' 2016-6-5 ', observe the execution plan, the actual number of rows is 150706, the estimated number of rows is unchanged, or 37117

3, update the query criteria to:createdate> ' 2016-6-1 ' and createdate< ' 2016-6-9 ', observe the execution plan, the actual number of rows is 302114, the estimated number of rows is unchanged, or 37117

,

Not found, because the query time period changes, the actual number of rows also varies, but regardless of the actual number of rows, the estimated number of rows is always the first time to execute the estimated number of rows.

That must be wrong, right? Whatever conditions are taken, the estimated number of rows is 37117, then all of a sudden, how each execution of SQL data row estimates are the same?
In fact, the problem is the same as in the first example, the SQL statement is automatically parameterized, resulting in the implementation of plan reuse,
Execution plan reuse results in an incorrect estimate of the number of data rows for the actual query.

How to resolve an issue where automatic parameterization results in incorrect reuse of execution plans

Many problems find the real reason, it is not difficult to solve, this problem is caused by the implementation of plan reuse, then we only need to solve the problem of implementation plan reuse
That is, to keep him from reusing the execution plan, just add a hint to the SQL statement,
Also: Select COUNT (1) from Test20160810 where createdate> ' 2016-6-1 ' and createdate< ' 2016-6-9 ' OPTION (RECOMPILE)
The reason is that with option (RECOMPILE) This query hint, the SQL execution plan cache is not cached, there is no execution plan cache, there is no reuse

For example, this query, which adds an option (RECOMPILE) query hint to the query statement, allows it to recompile the SQL statement before it executes, and he can correctly estimate the data row.

Summarize

In this paper, a practical case is presented to illustrate what are 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, if you do not notice still occasionally will appear confused.

Off Topic

Have a little feeling very deep, that is, more and more practical problems, have to have theoretical knowledge to support,
But it is often theoretically said that the situation does not appear frequently or even if there is no attention, there is time to neglect some theoretical knowledge.
For the problems encountered, if you really want to find out, still have some theoretical knowledge to do the groundwork. Many times, often after encountering problems, recalled once good seems to have seen this aspect of theoretical knowledge.
This is why we need to keep reading and understand some theoretical knowledge.

SQL Server SQL Performance Optimization--Database in "simple" parameterized mode, the problem of automatic parameterization of SQL