SQL SERVER-存储参数嗅探(parameter sniffing)
Q
存储过程会缓存执行计划,进行复用,但是可能数据分布极不均匀,导致缓存的计划不适合一些参数,当带入这些特殊参数按以前的计划执行时,出现了性能问题。
以下SQL查询存储的执行计划,可以找出有问题的SQL
--查看执行时间,次数,平均时间 SELECT d.object_id , d.database_id , OBJECT_NAME(object_id, database_id) 'proc name' , d.cached_time , d.last_execution_time , d.total_elapsed_time , d.total_elapsed_time / d.execution_count AS [avg_elapsed_time] , d.last_elapsed_time , d.execution_count FROM sys.dm_exec_procedure_stats AS d WHERE OBJECT_NAME(object_id, database_id) = 'usp_xx' ORDER BY [total_worker_time] DESC; --查看执行计划 SELECT d.object_id , DB_NAME(d.database_id) DBName , OBJECT_NAME(object_id, database_id) 'SPName' , d.cached_time , d.last_execution_time , d.total_elapsed_time/1000000 AS total_elapsed_time, d.total_elapsed_time / d.execution_count/1000000 AS [avg_elapsed_time] , d.last_elapsed_time/1000000 AS last_elapsed_time, d.execution_count , d.total_physical_reads , d.last_physical_reads , d.total_logical_writes , d.last_logical_reads , et.text SQLText , eqp.query_plan executionplan FROM sys.dm_exec_procedure_stats AS d CROSS APPLY sys.dm_exec_sql_text(d.sql_handle) et CROSS APPLY sys.dm_exec_query_plan(d.plan_handle) eqp WHERE OBJECT_NAME(object_id, database_id) = 'usp_xx' ORDER BY [total_worker_time] DESC;
A
解决方法:
在问题的SQL后加option,这2种都可以,
INSERT INTO [dbo].[jjjj] VALUES(13) OPTION (OPTIMIZE FOR UNKNOWN) INSERT INTO [dbo].[jjjj] VALUES(13) OPTION (RECOMPILE)
使用哪一种原则如下:
1:执行不频繁的存储过程,使用OPTION(RECOMPILE)要优先与OPTION (OPTIMIZE FOR UNKNOWN)
2:执行频繁的存储过程,使用OPTION (OPTIMIZE FOR UNKNOWN)要优先于OPTION(RECOMPILE)
3:数据分布倾斜的厉害的情况下,优先使用OPTION(RECOMPILE)
4: 使用OPTION (OPTIMIZE FOR UNKNOWN)会生成一个稳定、统一的执行计划,如果这个执行计划的效率基本能满足用户需求,那么优先使用OPTION (OPTIMIZE FOR UNKNOWN)
参考资料:https://www.cnblogs.com/kerrycode/p/9684192.html
在存储中加入with recompile,但是这种每次执行都要重新编译,所以不建议使用
ALTER PROCEDURE [dbo].[usp_ddd] with recompile AS BEGIN SELECT @@SERVERNAME END