SQL server-Execution Plan

1. each execution of the SQL statement generates an execution plan and is cached because the execution plan is generated with time overhead, so reusing the execution plan can improve performance and save buffer space. We can use Sys.dm_exec_cached_plans, sys.dm_exec_sql_text, sys.dm_exec_query_plan to query the cache execution plan.

The following experimental statement needs to execute DBCC FREEPROCCACHE to clear the plan cache before each execution

2. Query execution Plan statements

Select      B. [text] ,    a.usecounts,    a.cacheobjtype,    a.objtype,    C.query_plan,    a.size_in_bytes  from sys.dm_exec_cached_plans a cross APPLY sys.dm_exec_sql_text (a.plan_handle) b   Cross APPLY sys.dm_exec_query_plan (A.plan_handle) c

Description

Text: Code SQL statement

Usecounts: Cache usage Count

ObjType

Prepared pre-defined statements, SQL statements using parameterized queries

Adhoc ad hoc queries, queries without arguments

3. Testing

3.1 Execute 3 times the following statement

Select *  from where = 1

As a result, see 2nd, 3, ADHOC and prepared were executed 3 times and 1 times respectively, indicating that the execution plan was reused.

3.2 Execution of the following 3 statements

 select  *   person.address where  addressid =  1  go  select  *  from  person.address Span style= "color: #0000ff;" >where  addressid 2  go  select  *  from  Person.Address where  addressid 3  go  

As a result, a plan cache is generated for each adhoc query, respectively

3.3 Perform the following parameters query 2 times

exec sp_executesql n'select * from person.address where addressid = @AddressID', n ' @AddressID int ',@AddressID=1

As a result Kusakabe, the plan cache was used 2 times

3.4 Execute the following statement

execsp_executesql N'SELECT * from person.address where addressid = @AddressID'N'@AddressID int',@AddressID=1Goexecsp_executesql N'SELECT * from person.address where addressid = @AddressID'N'@AddressID int',@AddressID=2Go

The results are as follows, and when the query parameter values are not the same, the plan is reused.

3.5 Execute the following statement

Select *  from where = ' San Francisco ' Go Select *  from where = ' Dallas ' Go

As a result, the Adhoc plan is regenerated. So in the background code when the SQL statement, if it is a concatenation of the statement, if it is a string, and the length is inconsistent, the execution plan will not be reused, because the SQL statement is hashed, based on the results of the calculation to find out whether there is an execution plan, so a character difference will cause the plan can not be reused.

3.6 Execute the following statement

execsp_executesql N'SELECT * from person.address where city = @City'N'@City nvarchar (+)',@City='San Francisco'Goexecsp_executesql N'SELECT * from person.address where city = @City'N'@City nvarchar (+)',@City='Dallas'Go

As a result, the plan is reused when querying with parameters.

SQL server-Execution Plan