SQL Server Query Optimization (test 02) parameter sniffing-execution plan selection

Recently often see the word "parameter sniffing", read a few articles, so I try to do a test to deepen the impression! Go to the official website to download the database: AdventureWorks2012 Direct Test it! Find a few familiar tables to associate with, Use ProductID as a condition to find two IDs returns a value that has a large difference in the number of rows. productid=870 (4688 lines) productid=897 (2 lines)

"Test One"

---First empty the plan cache DBCC freeproccache--before execution opens the Counter monitoring view (execute the following query separately) Select SDH. SALESORDERID,SDH. Salesordernumber,p.productid,p.name,sod. Linetotalfrom [Sales]. [SalesOrderHeader] Sdhinner join [Sales]. [SalesOrderDetail] Sod on SDH. SalesOrderID = Sod. Salesorderidinner join [Production]. [Product] p on sod. ProductID = P.productidwhere P.productid =870select SDH. SALESORDERID,SDH. Salesordernumber,p.productid,p.name,sod. Linetotalfrom [Sales]. [SalesOrderHeader] Sdhinner join [Sales]. [SalesOrderDetail] Sod on SDH. SalesOrderID = Sod. Salesorderidinner join [Production]. [Product] p on sod. ProductID = P.productidwhere P.productid =897

Look at the counter first, with two green peaks of 1. Is the number of compilations that occurred when the above was executed separately.

--View Cache object Execution Type: adhoc (instant query) SELECT cacheobjtype,objtype,refcounts,usecounts,[sql]from sys.syscacheobjectswhere [SQL] Like '%salesorderid% ' and [SQL] don't like '%sys% '--then view the cached query plan and plan size with views Select Refcounts,usecounts,cacheobjtype,size_in_ Bytes,[text],query_planfrom Sys.dm_exec_cached_plans Cross Apply Sys.dm_exec_sql_text (plan_handle) cross apply sys.dm _exec_query_plan (plan_handle) WHERE [text] like '%salesorderid% ' and [text] don't like '%sys% '

You can see that two different query plans (Query_plan) were generated, and the cache (Size_in_bytes) was consumed.


The advantages and disadvantages of these formulations are:
Disadvantages: If the query condition value changes, it will be compiled for the first time as a new query statement, not only consuming the CPU, but also generating a new query plan that consumes the cache.
Pros: Every execution plan is optimal

"Test Two"
Now change to the form with parameters.

--empty plan cache DBCC FREEPROCCACHE--PRODUCTID=870 (4688 rows) productid=897 (2 rows) DECLARE @ProductID intset @ProductID = 870select Sdh. SALESORDERID,SDH. Salesordernumber,p.productid,p.name,sod. Linetotalfrom [Sales]. [SalesOrderHeader] Sdhinner join [Sales]. [SalesOrderDetail] Sod on SDH. SalesOrderID = Sod. Salesorderidinner join [Production]. [Product] p on sod. ProductID = P.productidwhere P.productid [email protected]declare @ProductID intset @ProductID = 897select SDH. SALESORDERID,SDH. Salesordernumber,p.productid,p.name,sod. Linetotalfrom [Sales]. [SalesOrderHeader] Sdhinner join [Sales]. [SalesOrderDetail] Sod on SDH. SalesOrderID = Sod. Salesorderidinner join [Production]. [Product] p on sod. ProductID = p.productidwhere P.productid [email protected]

look at the counter, the same two green peak is 1.2 compilations have occurred

--View cached query plan and plan size in view select Refcounts,usecounts,cacheobjtype,size_in_bytes,[text],query_planfrom sys.dm_exec_ Cached_plans Cross Apply Sys.dm_exec_sql_text (plan_handle) Cross apply sys.dm_exec_query_plan (plan_handle) WHERE [text ] Like '%salesorderid% ' and [text] not like '%sys% '

This will see that two identical query plans (Query_plan) have been generated, and the cache size (size_in_bytes) will be the same.
Another difference is that the execution plan is executed in two parts, the first part of the parameter assignment, and the second part of the query statement.
So the second part uses the same query plan.

The advantages and disadvantages of these formulations are:
Disadvantages: If the query condition value changes, it will be compiled for the first time as a new query statement, not only consuming the CPU, but also generating a new query plan that consumes the cache.
Also, because the query plan is the same. When the number of rows returned is large. Some query performance is not good.
Advantages: When the amount of data returned is similar, the query optimizer estimates a better query plan based on the parameters, which is advantageous to control the query plan.
(But the comparison found that this is worse than the last one!)

"Test Three"

--then put the execution statement into the stored procedure create PROCEDURE p_test (@ProductID INT) asbeginselect SDH. SALESORDERID,SDH. Salesordernumber,p.productid,p.name,sod. Linetotalfrom [Sales]. [SalesOrderHeader] Sdhinner join [Sales]. [SalesOrderDetail] Sod on SDH. SalesOrderID = Sod. Salesorderidinner join [Production]. [Product] p on sod. ProductID = P.productidwhere P.productid [email protected]end--productid=870 (4688 lines) productid=897 (2 rows)--Execute stored procedure DBCC Freeproccacheexec p_test @ProductID = 870EXEC p_test @ProductID = 897--View Cache object Execution Type: Proc (stored procedure) SELECT Cacheobjtype,objtype , Refcounts,usecounts,[sql]from sys.syscacheobjectswhere [SQL] like '%salesorderid% ' and [SQL] don't like '%sys% ' SELECT Refcounts,usecounts,cacheobjtype,size_in_bytes,[text],query_planfrom Sys.dm_exec_cached_plans Cross APPLY sys.dm_ Exec_sql_text (plan_handle) Cross APPLY sys.dm_exec_query_plan (plan_handle) WHERE [text] like '%salesorderid% ' and [text ] not like '%sys% '

at this point, only 1 cache plans were found! Regardless of how the parameter is changed, only one query plan is cached, thus eliminating the memory footprint.
But the pros and cons of this approach are more pronounced.


the pros and cons of this notation are:
Cons: If the query condition value changes, it compiles 1 times each time and consumes the CPU.
The most important disadvantage is that the query plan is generated with the parameter values passed for the first execution of the stored procedure!
in other words, after a stored procedure is created, the pass-through parameter executes the stored procedure for the first time, and the number of rows returned by this parameter affects more or less the execution plan's permanent determination.

DBCC Freeproccache --situation plan cache
exec p_test @ProductID = 870 --now change 870 first execute
exec p_test @ProductID = 897 --just executed the stored procedure for 897 first time

after the implementation of the query plan, it is not the same!
so note why the same stored procedure, table statistics are fine, but some queries are fast and some are slow.
trace the specific statement to run and normal, just as above "test one".



advantages: Save the memory! (However, memory generally does not use much)

"Test Four"

--then put the execution statement into the stored procedure create PROCEDURE p_test2 (@ProductID INT) asbegindeclare @ID intset @ID = @ProductID--The difference here is the select SDH. SALESORDERID,SDH. Salesordernumber,p.productid,p.name,sod. Linetotalfrom [Sales]. [SalesOrderHeader] Sdhinner join [Sales]. [SalesOrderDetail] Sod on SDH. SalesOrderID = Sod. Salesorderidinner join [Production]. [Product] p on sod. ProductID = p.productidwhere P.productid [email protected] end--productid=870 (4688 lines) productid=897 (2 rows) DBCC Freeproccacheexec p_test2 @ProductID = 870EXEC p_test2 @ProductID = 897SELECT refcounts,usecounts,cacheobjtype,size_in_ Bytes,[text],query_planfrom Sys.dm_exec_cached_plans Cross Apply Sys.dm_exec_sql_text (plan_handle) cross apply sys.dm _exec_query_plan (plan_handle) WHERE [text] like '%salesorderid% ' and [text] don't like '%sys% '

The advantages and disadvantages of this approach are almost identical to "test three" , except that the first generation of execution plans is not affected by the parameters.
The following two stored procedures, after the creation of a stored procedure, no matter who executes first, the query plan is the same!
EXEC P_test2 @ProductID = 870
EXEC P_test2 @ProductID = 897


This is the real use of the so-called "parameter sniffing"! When the optimization engine determines the query plan for the first time, it does not know what parameter values are executed.
Therefore, only the parameters that are passed are detected, and the system is based on the parameters to determine the query plan of the stored procedure.
The downside here is that the number of parameters returned can also affect performance.

---------------------------------------------------------------------
---------------------------------------------------------------------

Summarize:

The above are pros and cons, the most bad is "test two" kind.
Another phenomenon is that all the tests above, the individual in the Performance Monitor did not find the "recompile" situation, each time only "compile".
Although the compilation includes recompiling, the recompilation did not occur once. Unless the display lets the statement recompile (such as option (RECOMPILE)) appears.

Test one: Best, a new plan cache is generated each time

Test two: Not good, same cache plan, return result set large performance is not the same

Test Three: Save the cache, and the stored procedure is best recompiled as the volume of data grows

Test four: Save cache, query plan fixed, cannot change.

Finally, the overall test of the above table in these four cases:
More data, no, summarized as follows:

ProductID	Query type	Format	Total logical Read	Cpu	Memory	Time	Each compilation	Cache size	Query overhead
870 (4688 lines)	Instant Query	where P.productid =870	1305	20	952	20	Is	KB	26%
870 (4688 lines)	Instant parameter query	where P.productid [email protected]	1305	16	1016	16	Is	KB	24%
870 (4688 lines)	Stored Procedures	Proc: @ProductID = 870	1305	17	928	17	Is	KB	26%
870 (4688 lines)	In-stored procedure declaration	Proc:where P.productid [email protected]	1305	18	984	18	Is	KB	24%
897 (2 lines)	Instant Query	where P.productid =897	20	10	792	10	Is	KB	13%
897 (2 lines)	Instant parameter query	where P.productid [email protected]	1305	17	1016	17	Is	KB	37%
897 (2 lines)	Stored Procedures	Proc:p_test @ProductID = 897	20	8	760	8	Is	KB	13%
897 (2 lines)	In-stored procedure declaration	Proc:where P.productid [email protected]	1305	18	984	18	Is	KB	37%

SQL Server Query Optimization (test 02) parameter sniffing-execution plan selection