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