解決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)