解决sql server 由于参数嗅探导致的执行性能问题思路:
1、查询存储过程执行时间,比较平均执行时间与最近执行时间差异,如果相差很大就有可能是参数嗅探导致
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
ORDER BY [total_worker_time] DESC;
2、解决参数嗅探:
a、执行不频繁的存储过程,使用OPTION(RECOMPILE)要优先与OPTION (OPTIMIZE FOR UNKNOWN)
b、执行频繁的存储过程,使用OPTION (OPTIMIZE FOR UNKNOWN)要优先于OPTION(RECOMPILE)
c、数据分布倾斜的厉害的情况下,优先使用OPTION(RECOMPILE)
d、使用OPTION (OPTIMIZE FOR UNKNOWN)会生成一个稳定、统一的执行计划,如果这个执行计划的效率基本能满足用户需求,那么优先使用OPTION (OPTIMIZE FOR UNKNOWN)