存储过程在程序慢而在Management Studio快的原因


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

1,存储过程在程序慢而在Management Studio快,分析查询计划,发现生成了两个执行计划,说明二者用的不是同一个查询计划,因参数嗅探问题引发此种情况。
2,产生不同执行计划原因是:引起执行计划重新编译的原因之一是上下文环境发生改变,SET开关。这些开关会影响语句执行的行为,甚至带来不同的结果,而sqlserver要另生成新的编译计划。而在Management Studio中,默认打开了SET ARITHABORT ON 在查询执行过程中发生溢出或被零除错误时,终止查询,而程序中是不打开的,不同的SET开关,造成不同编译计划的生成,而不是重用。
最终设置:看存储过程用的什么set,将Manage Studio工具-选项-查询执行-高级 打开或关掉工具
3,故要重用程序所生成的执行计划,方式是:1)确定SET设置 2)在当前中设置上下文环境

--查找SET开关
方法1:
SELECT sql,setopts
FROM sys.syscacheobjects
WHERE OBJECT_NAME(objid,DB_ID()) = 'frmUser_sel'

方法2:
SELECT plan_handle,usecounts,pvt.set_options, pvt.sql_handle
FROM (
    SELECT plan_handle, epa.attribute, epa.value ,usecounts
    FROM sys.dm_exec_cached_plans 
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) qt
    OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
    WHERE cacheobjtype = 'Compiled Plan'
    AND  OBJECT_NAME(objectid,dbid) = 'frmUser_sel' AND dbid = DB_ID()
    ) AS ecpa 
PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("set_options", "sql_handle")) AS pvt;
GO



--将获取的值传入,获取具体SET项
declare @set_options INT
SET @set_options= 251
 
IF ( (1 & @options) = 1 ) PRINT 'DISABLE_DEF_CNST_CHK' 
IF ( (2 & @options) = 2 ) PRINT 'IMPLICIT_TRANSACTIONS' 
IF ( (4 & @options) = 4 ) PRINT 'CURSOR_CLOSE_ON_COMMIT' 
IF ( (8 & @options) = 8 ) PRINT 'ANSI_WARNINGS' 
IF ( (16 & @options) = 16 ) PRINT 'ANSI_PADDING' 
IF ( (32 & @options) = 32 ) PRINT 'ANSI_NULLS' 
IF ( (64 & @options) = 64 ) PRINT 'ARITHABORT' 
IF ( (128 & @options) = 128 ) PRINT 'ARITHIGNORE'
IF ( (256 & @options) = 256 ) PRINT 'QUOTED_IDENTIFIER' 
IF ( (512 & @options) = 512 ) PRINT 'NOCOUNT' 
IF ( (1024 & @options) = 1024 ) PRINT 'ANSI_NULL_DFLT_ON' 
IF ( (2048 & @options) = 2048 ) PRINT 'ANSI_NULL_DFLT_OFF' 
IF ( (4096 & @options) = 4096 ) PRINT 'CONCAT_NULL_YIELDS_NULL' 
IF ( (8192 & @options) = 8192 ) PRINT 'NUMERIC_ROUNDABORT' 
IF ( (16384 & @options) = 16384 ) PRINT 'XACT_ABORT'

–查看当前option select @@option 参考:Set Options

4,上述说明了相同的存储过程参数,但产生不同的执行计划的原因,但关于速度不一样呢,原因是发生了参数嗅探,解决参数嗅探方法有:1)重新编译存储过程 sp_recompile 'sp' 2)在关键语句加:option(recompile) 3)指定值OPTION (OPTIMIZE FOR (@pid = 897))