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