存储过程每次执行都编译与活动监视器高执行次数

分享两个标题相关问题处理经验，帮助面临相同问题的小伙伴

一、存储过程每次执行都编译

问题表象：手动多次执行存储过程耗时稳定慢，速度严重不符合预期

处理经过：首先做的是把计划缓存清空，再把存储过程逻辑用if改为跳过原始sql，仅输出常量1，此时多次执行仍然耗时超过五秒，速度不符合预期。

然后在SSMS窗口中执行set statistics time on，再次执行发现几乎所有耗时都在编译上，因此确定该问题在编译上。想到编译开销大头是在于生成执行计划上，复杂sql生成执行计划的耗时几秒也是常见，而sqlserver为了缓解慢的问题，因此有了缓存执行计划的机制，存储过程首次执行时编译会缓存执行计划以便下次执行时重用执行计划，而不用再耗时生成了。但也会因为一些条件而会发生重编译，例如大家常见的在存储过程中加with recompile，发生重编译的本次执行生成的执行计划不会缓存。

理论上首次执行后有缓存计划，下次执行时编译就不再长耗时，除非发生重编译，因此我判断当前是每次执行时都发生了重编译，查询联机丛书与书籍里提到的重编译因素，一一排查都不存在。然后再通过系统视图查询缓存中的执行计划相关，发现没有任何相关执行计划缓存，缺乏执行计划缓存就需要在下一次执行时生成执行计划，这给每次执行都要长时间编译有了合理的支撑。因此问题就在于为什么每次执行时没有缓存执行计划，只不过已经检查了没有重编译因素，理论上不应该发生重编译才对。

处理结果：最终确定为sqlserver2008r2的BUG，并寻找到相应补丁，如果不打补丁，这里还提供经过验证过的两种解决方式，改动后将能缓存执行计划并随之重用不再长编译：

1、将存储过程外部参数用本地变量接住再放入sql中

2、将存储过程外部参数的nvarchar(max)长度改为非max，例如1000

相应补丁：https://support.microsoft.com/en-us/topic/kb2380435-fix-the-query-plan-for-a-stored-procedure-is-not-cached-if-the-stored-procedure-uses-a-blob-variable-and-the-variable-is-used-in-a-string-function-in-microsoft-sql-server-2008-or-in-microsoft-sql-server-2008-r2-e264035d-3c67-0e45-6d55-82a8cee7e878e

二、活动监视器高频次执行sql无法追踪到

问题表象：活动监视器中看到存在 上亿执行次数/分钟 的sql，但用跟踪却没有找到如此大频次数执行记录。

处理结果：确定为活动监视器该字段的定义有歧义，它虽然显示的是 执行次数/分钟，但正确定义是该sql自上次编译以来执行的次数。例如第一天执行10次，第二天执行1次，中间没有发生重编译，那么第二天执行时活动监视器这里会显示11，而不是1。而其记录执行次数发生对象应该是基于执行计划缓存的使用次数，因此清理计划缓存就能解决使其重置，不再显示异常高频执行次数。