Parametric processing of SQLText

Speaking of SQL Parameterization, I am also drunk, because the SQL engine is really a very powerful system, we usually do when the system will be added to the cache, I think if there is no cache, there will be no

Big website can run up, and big companies generally will do in a thing of more intentions, relatively thin, SQL Server also used the cache, including the data cache and plan cache two big head.

Now we also know that the plan cache includes the previous generated XML structure and SQL text, and more interestingly, SQL text can also be parameterized ... That is, templating.

One: SQL parameterization

<1> first to do a person table, insert 1000 data, then empty the cache, then select a data,

1 DROP TABLE dbo. Person2 CREATE TABLE person (ID INT identity,name CHAR (5) The DEFAULT ' AAAAA ') 3 INSERT into dbo. Person DEFAULT VALUES4 Go 10005 6 DBCC freeproccache7 SELECT * FROM dbo. Person WHERE id=100

The <2> data has been queried, so let's look at the SQL in Dm_exec_sql_text.

1 SELECT usecounts,objtype,cacheobjtype, plan_handle,query_plan,text from Sys.dm_exec_cached_plans2 cross APPLY sys.dm _exec_query_plan (plan_handle) 3 cross APPLY sys.dm_exec_sql_text (plan_handle) 4 WHERE text like '%person% '

As can be seen from the above figure, when I select a bit, there are two SQL text, the first called Adhoc (instant query), a call prepared (parameterized), and then I click on the second record

Query_plan, a graphical execution plan will appear, such as:

Following the curiosity, I continued to click on the third record of the Query_plan will be what???

With these two SQL text execution plans, I don't know if you've observed the following four points:

(1) My SQL is performing a table scan, this is no problem, the problem is in my two SQL text, the first plan actually did not have a complete execution plan, but just a graphical select,

The second parameterized SQL, its plan is a complete execution plan ... So what does that mean??? Since prepared is a complete execution plan, why do you have to adhoc this

SQL cache it up??? In fact, I do not know ... I guess it must be for the engine to quickly find prepared this complete execution plan ...

(2) is to think why SQLText to do parameterization, think carefully should understand that the purpose of parameterization is to reuse the execution plan, because this time the XML has been generated, otherwise, you

Each execution of SQL as long as the parameters are different to generate a query_plan of XML, will not pull the query speed of the hind legs it???

(3) Did you notice that the parameterized type is tinyint, see this tinyint I immediately want to break it, we know that tinyint is a byte type, the scope of the expression is 256 ... Maybe

The engine looked at me where 100 just thought I was good bully ... So I think now is where 500, see what will be the effect???

1 SELECT * FROM dbo. Person WHERE id=5002 3 SELECT usecounts,objtype,cacheobjtype, plan_handle,query_plan,text from Sys.dm_exec_cached_ PLANS4 Cross Apply Sys.dm_exec_query_plan (plan_handle) 5 cross apply Sys.dm_exec_sql_text (plan_handle) 6 WHERE text like ' %person% '

As you can see, when I was 500, the engine would generate a prepared sqltext again, so there were two prepared, and I was wondering, why not just

to a (@1 int)??? Like the current way SQL engine processing, there will be a few prepared records of XML and SQLText, is not a bit of a waste of memory?

(4) Think carefully you will know that the SQL engine is still very sedan, because the prepared record has been generated, the execution plan is generated .... What does that mean??? Explain this time

XML is already dead, it also means that the execution plan is also dead, is not the @1 parameter will not lead to change in the execution plan??? If there's a change, do I have to do the same

Does this table scan the execution plan??? A bit of wonderful, well, I'm going to say it carefully below.

Second: The influence of parameter change on prepared

If you have read the previous blog post, you should understand that there is a thing called Bookmark search ... It is based on the non-clustered index through the B-tree lookup, when the target key to find the time to get this

Key, and then use key to fetch data records, if your nonclustered index key value is relatively high, then the SQL engine will go to bookmark lookup, but if your key value

If the uniqueness is low, or if the data volume is small, the SQL engine will not go to the bookmark lookup and turn to the clustered index scan ... So what does that mean? Indicates that the execution plan is sometimes followed

(@1 int) This value has a relationship ... That would not seem like a way to reuse the execution plan, right? To verify how the SQL engine handles it, let's do a test.

1. Clear the cache first, then index on the Name column, and then we select, such as:

1 DBCC freeproccache2 CREATE INDEX idx_name on dbo. Person (NAME) 3 SELECT * FROM dbo. Person WHERE name= ' aaaaa '

2. Then continue to look at XML and SQLText

Do you have any findings? There is no prepared record in the record, which indicates what ... Explains that SQL Server is smart and knows that name may have a "table scan" to

"Bookmark Scan" to switch back and forth, in order to verify the problem, I continue to insert the person table 1w data, and then insert a unique data. Such as:

1 INSERT into dbo. Person DEFAULT VALUES2 go 100003 INSERT into dbo. Person (NAME) VALUES (' CCCCC ')

Indeed, as I suspect, SQL Server is smart ... If it feels this name is not reliable, it is absolutely not afraid to give this sqltext generated prepared ... Turn over

Head to think about the first why there is sqltext, that is because a column no matter how much value, can not change the reality of the table scan, so the SQL engine dare to be so bold ... Suddenly feel life

Isn't that what it is???? A lot of people are not some chop things are absolutely not afraid to do ...

Parametric processing of SQLText