SQL Server Query Optimization (test 02) parameter sniffing-Execution Plan Selection, sniffing execution plan

SQL Server Query Optimization (test 02) parameter sniffing-Execution Plan Selection, sniffing execution plan

Recently, I often see the word "parameter sniffing" and read several articles, so I tried to make a test to help my impression! Go to the official website to download the database: AdventureWorks2012 and test it directly! Associate several familiar tables and use ProductID as the condition to find the value with a large difference in the number of rows returned by two IDs. ProductID = 870 (4688 rows) ProductID = 897 (2 rows)

Test 1]

-- Clear the plan cache dbcc freeproccache first -- open the Counter Monitoring view before execution (separately execute the following query) 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 = 870 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. productivity = 897

First look at the counter. There are two green peaks: 1. The number of compilations that occurred during the preceding execution.

-- View the cache object execution type: Adhoc (instant query) SELECT cacheobjtype, objtype, refcounts, usecounts, [SQL] FROM sys. syscacheobjectsWHERE [SQL] LIKE '% SalesOrderID %' AND [SQL] NOT LIKE '% sys %' -- view the cache query plan AND plan size in the 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 %'

Two different query plans (query_plan) are generated and the cache is occupied (size_in_bytes ).


The advantages and disadvantages of the above method are:
Disadvantage: If the query condition value changes, it will be compiled for the first time as a new query statement, which not only consumes CPU, but also generates a new query plan, which will occupy the cache.
Advantage: Each execution plan is optimal.

Test 2]
It is now in the form of a parameter.

-- First clear the plan cache dbcc freeproccache -- ProductID = 870 (4688 rows) ProductID = 897 (2 rows) DECLARE @ ProductID INTSET @ ProductID = 870 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 = @ maid @ ProductID INTSET @ ProductID = 897 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 = @ ProductID

Looking at the counter, there are also two green peaks: 1. Two compilations occurred.

-- View the cache query plan and plan size in the 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 %'

Two identical query plans (query_plan) are generated, and the cache size (size_in_bytes) is 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.
Therefore, the second part only uses the same query plan.

The advantages and disadvantages of the above method are:
Disadvantage: If the query condition value changes, it will be compiled for the first time as a new query statement, which not only consumes CPU, but also generates a new query plan, which will occupy the cache.
In addition, because the query plan is the same, when the number of returned rows is significantly different, some query performance is not good.
Advantage: When the returned data volume is almost the same, the query optimizer estimates a better query plan based on the parameters, which is conducive to the control of the query plan.
(But it is found that this method is worse than the previous one! Test again)

Test 3]

-- Put the execution statement in 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 = @ ProductIDEND -- ProductID = 870 (4688 rows) ProductID = 897 (2 rows) -- execute the Stored Procedure dbcc freeproccacheexec P_Test @ ProductID = 870 EXEC P_Test @ ProductID = 897 -- view the cache object execution type: Proc (Stored Procedure) SELECT cacheobjtype, objtype, refcounts, usecounts, [SQL] FROM sys. syscacheobjectsWHERE [SQL] LIKE '% SalesOrderID %' AND [SQL] NOT 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 time, we found that there is only one cache plan! No matter how the parameter changes, only one query plan is cached, saving the memory usage.
However, the advantages and disadvantages of this method are more obvious.


The advantages and disadvantages of this method are:
Disadvantage: If the query condition value changes, it will be compiled once each time, consuming CPU.
The most important drawback is that the generation of a query plan is determined by the parameter value passed during the first execution of the stored procedure!
That is to say, after a stored procedure is created and a parameter is passed for the first time to execute the stored procedure, the number of rows returned by this parameter will more or less affect the permanent determination of the execution plan.

Dbcc freeproccache-plan Cache
EXEC P_Test @ ProductID = 870 -- change to 870 and run the command first.
EXEC P_Test @ ProductID = 897 -- the first execution of the stored procedure for 897

The query plan is different after execution!
Therefore, it is important to note Why table statistics are okay for the same stored procedure, but some queries are fast and some slow.
The trail finds the specific statements and runs normally, just like Test 1 above.



Advantage: Memory saved! (However, memory is usually not used much)

Test 4]

-- Put the execution statement in the stored procedure create procedure P_Test2 (@ ProductID INT) ASBEGINDECLARE @ id intset @ ID = @ ProductID -- difference here 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 = @ id end -- ProductID = 870 (4688 rows) ProductID = 897 (2 rows) dbcc freeproccacheexec P_Test2 @ ProductID = 870 EXEC P_Test2 @ ProductID = 897 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 %'

The advantages and disadvantages of this method are almost the same as that of Test 3. The only difference is that the execution plan generated for the first time is not affected by parameters.
The following two stored procedures, after the creation of the stored procedure, no matter who executes it first, the query plan is the same!
EXEC P_Test2 @ maid = 870
EXEC P_Test2 @ maid = 897


The so-called parameter sniffing is actually used here "! When the Optimization engine first determines the query plan, it does not know the parameter value of the execution.
Therefore, only the passed parameters are sniffed. The system determines the query plan of the stored procedure based on the parameters.
This is also a bad point, that is, the number of returned parameters may also affect the performance.

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

Summary:

The above methods have advantages and disadvantages. The worst is Test 2.
Another phenomenon is that in all the tests above, I did not find "recompilation" in the Performance Monitor, and only "Compilation" was found at a time ".
Although compilation includes re-compilation, the re-compilation has never been performed once. It appears only when the statement is re-compiled (such as option (recompile.

Test 1: optimal. A new plan cache is generated each time.

Test 2: It is not good. In the same cache plan, the performance is different when the returned result set is large.

Test 3: Saving cache. As the data size increases, it is best to recompile the stored procedure.

Test 4: Save the cache and the query plan is fixed and cannot be changed.

Finally, the overall test shows the above four situations:
There is a large amount of data, but it cannot be summarized as follows:

ProductID	Query type	Format	Total logical reads	CPU	Memory	Time	Each Compilation	Cache size	Query overhead
870 (4688 rows)	Instant Query	Where P. ProductID = 870	1305	20	952	20	Yes	56 KB	26%
870 (4688 rows)	Instant parameter query	Where P. ProductID = @ ProductID	1305	16	1016	16	Yes	56 KB	24%
870 (4688 rows)	Stored Procedure	Proc: @ ProductID = 870	1305	17	928	17	Yes	56 KB	26%
870 (4688 rows)	Stored Procedure Declaration	Proc: where P. ProductID = @ ID	1305	18	984	18	Yes	56 KB	24%
897 (2 rows)	Instant Query	Where P. ProductID = 897	20	10	792	10	Yes	48 KB	13%
897 (2 rows)	Instant parameter query	Where P. ProductID = @ ProductID	1305	17	1016	17	Yes	56 KB	37%
897 (2 rows)	Stored Procedure	Proc: P_Test @ ProductID = 897	20	8	760	8	Yes	56 KB	13%
897 (2 rows)	Stored Procedure Declaration	Proc: where P. ProductID = @ ID	1305	18	984	18	Yes	56 KB	37%