☰

如何从 SQL Server 查询缓存中删除特定的错误计划？

tag-icon • tag-icon sql-server sql-server-2008 cache query
如何从 SQL Server 查询缓存中删除特定的错误计划？

我们有一个特定的 SQL Server 2008 查询（不是存储过程，而是相同的 SQL 字符串 - 每 5 分钟执行一次），它会间歇性地缓存一个非常糟糕的查询计划。

此查询通常在几毫秒内运行，但是使用这个糟糕的查询计划，它需要 30 多秒。

我该如何进行手术删除一个错误的缓存查询计划从 SQL Server 2008 开始，而不会破坏生产数据库服务器上的整个查询缓存？

答案1

我想到了一些事情

select * from sys.dm_exec_query_stats

将显示所有缓存的查询计划。遗憾的是，没有显示任何 SQL 文本。

但是，你可以将 SQL 文本连接到计划中，如下所示：

select plan_handle, creation_time, last_execution_time, execution_count, qt.text
FROM 
   sys.dm_exec_query_stats qs
   CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt

WHERE从这里开始，添加一个子句来查找我知道在查询中的 SQL非常简单，然后我可以执行：

DBCC FREEPROCCACHE (plan_handle_id_goes_here)

从查询计划缓存中删除每个查询计划。这并不容易或方便，但它出现上班..

编辑：转储全部的查询缓存也可以工作，并且危险性没有听起来那么高，至少根据我的经验是这样：

DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;

答案2

如果你知道好的计划是什么样的，那么就使用计划提示。

您无法删除特定的缓存条目，但可以使用以下命令清除整个缓存池DBCC FREESYSTEMCACHE(cachename/poolname)。

如果你有计划句柄（来自sys.dm_exec_requests.plan_handle对于执行过程中出现问题的 session_id，或者来自sys.dm_exec_query_stats执行后):

select ce.name
from sys.dm_exec_cached_plans cp
join sys.dm_os_memory_cache_entries ce on cp.memory_object_address = ce.memory_object_address
where cp.plan_handle = @bad_plan

然而，所有 SQL 计划都有名称“SQL 计划”，这使得为 DBCC FREESYSTEMCACHE 选择正确的计划成为一个……困难的选择。

更新

没关系，忘了DBCC FREEPROCCACHE(plan_handle)，是的，这会起作用。

答案3

这缓存进程解决方案很好，但更直接的方法是使用选项（重新编译）在您的 SQL 字符串上（您提到它不是 SP），这会告诉引擎它是一个一次性使用计划，因为您可能怀疑存在参数嗅探或您的统计数据在每次运行之间有很大不同，并且您怀疑它是一个糟糕的缓存计划问题。

DECLARE @SQL NVARCHAR(4000)
SELECT @SQL = 'SELECT * FROM Table WHERE Column LIKE @NAME OPTION (RECOMPILE)'
EXEC sp_executesql @SQL, N'@NAME varchar(15)', 'MyName'

答案4

要找到正确的哈希，只需查看执行计划，将该计划保存为 xml 或将其打开为 xml，

如果你不知道在哪里找到执行计划：你可以在 SSMS 中激活该计划在执行计划的 xml 中搜索

QueryHash="

哈希位于该文本后面，并且在哈希后面的某个地方您可以看到 RetrievedFromCache="true"，如果这是真的，您可以在 sys.dm_exec_query_stats 中找到它

select  query_hash, sql_handle from sys.dm_exec_query_stats qs where convert(varchar(255),query_hash,1) = '[your found hash goes here]'

注意，转换很重要，因为哈希是二进制的！现在您得到了 sql_handle，是时候进行最后的 RUN 了

DBCC FREEPROCCACHE ([the sql_handle goes in here])

计划已经没了，现在要由引擎来创建一个新的计划，或者如果运气不好的话，你会得到旧的计划。一些（有风险的）技巧：-重写查询-更新统计信息-重建或重新组织索引-在索引中包含列-更改 maxdop 设置-使用一组代表性参数运行第一个查询（使用正确的练习来预热服务器

相关内容