sql - DBCC freeproccache？

Question

我们最近遇到了性能问题，通过执行 DBCC freeproccache 解决了这个问题...现在，我们还有更多问题需要回答；

• 是什么导致过程缓存过时了？
• 如果索引或统计信息已过时，为什么查询没有过时重新编译自己？
• 将 DBCC freeproccache 安排为作业是否是一个好习惯？
• 有办法识别潜在过时的查询计划吗？
• 有办法识别有问题的查询吗？

感谢任何帮助!

Answer 1

您的问题五花八门，所以我会尽力解决所有问题。过程缓存就这么大。您的过程缓存可能已充满一次性计划(这对统计信息没有影响，尽管统计信息可能会影响计划缓存)。您可以在 Kimberly Tripp 的博客文章“Plan cache and optimizing for adhoc workloads”中阅读有关一次性计划的大量详细信息 - 包括针对 sys.dm_exec_cached_plans 的查询这将有助于识别缓存何时填充了大量一次性计划。正如她建议的那样，您可以通过对临时工作负载进行优化来防止这种膨胀。如果您发现需要经常这样做，我会说将 freeproccache 安排为一项作业只是一个创可贴，而不是解决方案。

为了清除“坏”计划，首先您需要识别“坏”计划。这可能是一个超过一定大小和/或在一段时间内未执行的计划，或者您已通过长时间运行的查询等识别出的计划。不幸的是，识别受参数影响的计划并不容易嗅探，除非您已经知道受影响的一个或多个查询。假设您想要在缓存中查找一周多没有运行的最旧的计划:

;WITH x AS 
(
    SELECT TOP 10 
        qs.[sql_handle], qs.plan_handle,
        txs = qs.statement_start_offset, 
        txe = qs.statement_end_offset,
        [size] = cp.size_in_bytes, 
        [uses] = SUM(cp.usecounts), 
        [last] = MAX(qs.last_execution_time)
    FROM 
        sys.dm_exec_query_stats AS qs
    INNER JOIN 
        sys.dm_exec_cached_plans AS cp 
        ON qs.plan_handle = cp.plan_handle
    WHERE 
        qs.last_execution_time < DATEADD(DAY, -7, CURRENT_TIMESTAMP)
    GROUP BY 
        qs.[sql_handle], qs.plan_handle, cp.size_in_bytes,
        qs.statement_start_offset, qs.statement_end_offset
    ORDER BY 
        [size] DESC
) 
SELECT 
    x.plan_handle, 
    size, uses, [last],
    [statement] = COALESCE(NULLIF(
        SUBSTRING(t.[text], x.txs/2, 
          CASE WHEN x.txe = -1 THEN 0 ELSE (x.txe - x.txs)/2 END
          ), ''), t.[text]) 
FROM x 
CROSS APPLY sys.dm_exec_sql_text(x.[sql_handle]) AS t;

现在您需要验证您是否确实想要清除该计划。例如，如果您认为首席执行官明天可能会运行该查询，那么最好将其留在那里。如果你想清除计划，可以直接清除:

DBCC FREEPROCCACHE([paste plan handle from above query here]);

这听起来比全局运行 DBCC FREEPROCCACHE 要多做很多工作，但如果缓存中有很多好的计划，那肯定会更好为您的整体用户提供帮助。

不过，这听起来确实像是一个创可贴。如果您的缓存充满了垃圾，并且在释放缓存之前性能会下降，那么您需要查看更高级别的架构、查询的提交方式等。这是我期望的行为LINQ2SQL 的第一次迭代，它将为每个不同长度的字符串参数的查询缓存一个版本的计划。因此，如果您有“January”参数，您将获得与“February”参数不同的计划，因为它将数据类型定义为 VARCHAR(7) 与 VARCHAR (8)。很确定该行为是固定的，但我对您的环境/应用程序了解不够，无法建议在哪里准确地寻找“坏主意”。