T-SQL参数嗅探重编译计划

SELECT TOP (10) * FROM mytableView WHERE ([Name]) LIKE (''%'' + (@Value0) + ''%'') ORDER BY [Id] DESC这里传入了一个变量，并以这样一种方式使用:对于不同的值，可能没有一致的匹配行数的指示，特别是由于前导 导致无法使用索引%.这是上面讨论的 OPTION (OPTIMIZE FOR UNKNOWN) 提示的好机会.如果存在这样一种情况，即传入的变量的匹配行分布差异很大，但传入的可能值并不多，并且传入的值被频繁重复使用，然后可以将这些(在对其执行 REPLACE(@var, '''', '''''') 之后)直接连接到动态 SQL 中.这允许这些值中的每一个都有自己独立但可重用的查询计划.其他变量应该像往常一样作为参数发送.例如，[StatusID] 的查找值将只有几个可能的值，它们将被频繁重用，但每个特定值可以匹配非常不同的行数.在这种情况下，类似以下内容将允许单独的执行计划不需要 RECOMPILE 或 OPTIMIZE FOR UNKNOWN 提示，因为每个执行计划都将针对该特定值进行优化:IF (TRY_CONVERT(INT, @StatusID) IS NULL)开始;THROW 50505, '@StatusID 不是有效的 INT', 55;结尾;声明@SQL NVARCHAR(MAX);SET @SQL = N'SELECT TOP (10) * FROM myTableView WHERE [StatusID] = '+ REPLACE(@StatusID, N'''', N'''''') -- 实际上只需要字符串+ N' AND [OtherField] = @OtherFieldVal;';EXEC sp_executesql@SQL，N'@OtherFieldVal VARCHAR(50)',@OtherFieldVal = @OtherField;假设传入了两个不同的@StatusID 值(例如 1 和 2)，将会缓存两个匹配以下查询的执行计划:SELECT TOP (10) * FROM myTableView WHERE [StatusID] = 1 AND [OtherField] = @OtherFieldVal;SELECT TOP (10) * FROM myTableView WHERE [StatusID] = 2 AND [OtherField] = @OtherFieldVal;I have the SQL command exec sp_executesql N'SELECT TOP (10) * FROM mytableView WHERE ([Name]) LIKE (''%'' + (@Value0) + ''%'') ORDER BY [Id] DESC',N'@Value0 varchar(5)',@Value0='value'this sql command execute near 22 seconds. I fount that it happens because I have a parameter sniffing..If add to end of SQL command option(recompile) it's work fast: 0 seconds was shown in Managements studioexec sp_executesql N'SELECT TOP (10) * FROM mytableView WHERE ([Name]) LIKE (''%'' + (@Value0) + ''%'') ORDER BY [Id] DESC option(recompile)',N'@Value0 varchar(5)',@Value0='value'Is it possible to recompile/recreate/erase/update execution plan for my SQL command to work without option(recompile)?I have tried to applyUPDATE STATISTICSsp_recompileDBCC FREEPROCCACHEDBCC UPDATEUSAGE (0)DBCC FREESYSTEMCACHE ('ALL')ALTER INDEX and REBUILD WITHunfortunately all this actions didn't help me. 解决方案 You could try the OPTIMIZE FOR UNKNOWN hint instead of RECOMPILE:exec sp_executesql N'SELECT TOP (10) * FROM mytableView WHERE ([Name]) LIKE (''%'' + (@Value0) + ''%'') ORDER BY [Id] DESC option(OPTIMIZE FOR UNKNOWN);', N'@Value0 varchar(5)', @Value0 = 'value';The MSDN page for Query Hints states that OPTIMIZE FOR UNKNOWN:This hint instructs the optimizer to use the total number of rows for the specified table divided by the number of distinct values for the specified column (i.e. average rows per value) as the row estimate instead of using the statistics of any particular value. As pointed out by @GarethD in a comment below: since this will possibly benefit some queries and possibly hurt others, it needs to be tested to see if the overall gain from this is a net savings over the cost of doing the RECOMPILE. For more details check out: How OPTIMIZE FOR UNKNOWN Works.And just to have it stated, depending on the distribution of the data and what values are passed in, if there is a particular value being used that has a distribution that is fairly representative of most of the values that could be passed in (even if wildly different from some values that won't ever be passed in), then you can target that value by using OPTIMIZE FOR (@Value0 = 'representative value') rather than OPTIMIZE FOR UNKNOWN.Please note that this query hint is only needed for queries that have:parameters supplied by variablesthe field(s) in question do not have a fairly even distribution of values (and hence different values passed in via the variable could generate different plans)The following scenarios were identified in comments below and do not all require this hint, so here is how to address each situation:select top 80 * from table order by id descThere is no variable being passed in here so no query hint needed.select top 80 * from table where id < @lastid order by id descThere is a variable being passed in here, but the [id] field, by its very nature, is evenly distributed, even if sparse due to some deletes, hence no query hint needed (or at least should not be needed).SELECT TOP (10) * FROM mytableView WHERE ([Name]) LIKE (''%'' + (@Value0) + ''%'') ORDER BY [Id] DESCThere is a variable being passed in here, and used in such a way that there could be no indication of consistent numbers of matching rows for different values, especially due to not being able to use an index as a result of the leading %. THIS is a good opportunity for the OPTION (OPTIMIZE FOR UNKNOWN) hint as discussed above.If there is a situation where a variable is passed in that has a greatly varying distribution of matching rows, but not many possible values to get passed in, and the values that are passed in are re-used frequently, then those can be concatenated (after doing a REPLACE(@var, '''', '''''') on it) directly into the Dynamic SQL. This allows for each of those values to have their own separate yet reusable query plan. Other variables should be sent in as parameters as usual.For example, a lookup value for [StatusID] will only have a few possible values and they will get reused frequently but each particular value can match a vastly different number of rows. In that case, something like the following will allow for separate execution plans that do not need either the RECOMPILE or OPTIMIZE FOR UNKNOWN hints as each execution plan will be optimized for that particular value:IF (TRY_CONVERT(INT, @StatusID) IS NULL)BEGIN ;THROW 50505, '@StatusID was not a valid INT', 55;END;DECLARE @SQL NVARCHAR(MAX);SET @SQL = N'SELECT TOP (10) * FROM myTableView WHERE [StatusID] = ' + REPLACE(@StatusID, N'''', N'''''') -- really only needed for strings + N' AND [OtherField] = @OtherFieldVal;';EXEC sp_executesql @SQL, N'@OtherFieldVal VARCHAR(50)', @OtherFieldVal = @OtherField;Assuming two different values of @StatusID are passed in (e.g. 1 and 2), there will be two execution plans cached matching the following queries:SELECT TOP (10) * FROM myTableView WHERE [StatusID] = 1 AND [OtherField] = @OtherFieldVal;SELECT TOP (10) * FROM myTableView WHERE [StatusID] = 2 AND [OtherField] = @OtherFieldVal; 这篇关于T-SQL参数嗅探重编译计划的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！