为什么我不使用SQL Server选项“针对临时工作负载进行优化”？

#1 楼

SQL Server开发团队的工作原理是最不令人惊讶-因此，为了保持行为的正常性，SQL Server通常禁用了新功能。
是的，针对即席工作负载进行优化可以极大地减少计划缓存膨胀-但总是先测试一下！

Kalen Delaney讲述了一个有趣的轶事，她问她的一位Microsoft工程师朋友，是否在某些情况下不适合启用此功能。几天后他回来说-想象一个应用程序有很多不同的查询，而每个查询总共运行两次。那么这可能是不合适的。可以说没有那么多应用程序！
如果您的大多数查询执行了一次以上（不完全是两次）；这可能是不合适的。一般规则是，如果数据库上有许多一次性使用的临时查询，则将其关闭；但是，仍然没有像这样的应用程序。

#2 楼

以下是一些小的代码，可以帮助您确定“针对临时工作负载的开/关优化切换”是否有用。通常，我们将其作为内部和客户端服务器运行状况检查的一部分进行检查。

这是最安全的启用选项，Brad和Glenn Berry对此都进行了详细描述。

--- for 2008 and up .. Optimize ad-hoc for workload 
IF EXISTS (
        -- this is for 2008 and up
        SELECT 1
        FROM sys.configurations
        WHERE NAME = 'optimize for ad hoc workloads'
        )
BEGIN
    DECLARE @AdHocSizeInMB DECIMAL(14, 2)
        ,@TotalSizeInMB DECIMAL(14, 2)
        ,@ObjType NVARCHAR(34)

    SELECT @AdHocSizeInMB = SUM(CAST((
                    CASE 
                        WHEN usecounts = 1
                            AND LOWER(objtype) = 'adhoc'
                            THEN size_in_bytes
                        ELSE 0
                        END
                    ) AS DECIMAL(14, 2))) / 1048576
        ,@TotalSizeInMB = SUM(CAST(size_in_bytes AS DECIMAL(14, 2))) / 1048576
    FROM sys.dm_exec_cached_plans

    SELECT 'SQL Server Configuration' AS GROUP_TYPE
        ,' Total cache plan size (MB): ' + cast(@TotalSizeInMB AS VARCHAR(max)) + '. Current memory occupied by adhoc plans only used once (MB):' + cast(@AdHocSizeInMB AS VARCHAR(max)) + '.  Percentage of total cache plan occupied by adhoc plans only used once :' + cast(CAST((@AdHocSizeInMB / @TotalSizeInMB) * 100 AS DECIMAL(14, 2)) AS VARCHAR(max)) + '%' + ' ' AS COMMENTS
        ,' ' + CASE 
            WHEN @AdHocSizeInMB > 200
                OR ((@AdHocSizeInMB / @TotalSizeInMB) * 100) > 25 -- 200MB or > 25%
                THEN 'Switch on Optimize for ad hoc workloads as it will make a significant difference. Ref: http://sqlserverperformance.idera.com/memory/optimize-ad-hoc-workloads-option-sql-server-2008/. http://www.sqlskills.com/blogs/kimberly/post/procedure-cache-and-optimizing-for-adhoc-workloads.aspx'
            ELSE 'Setting Optimize for ad hoc workloads will make little difference !!'
            END + ' ' AS RECOMMENDATIONS
END

#3 楼

当您打开“优化临时工作负载”选项时，您将导致第二次运行的临时查询与第一次运行一样慢，因为您将编译执行计划并提取相同的数据（没有缓存）这前2次。
没什么大不了的，但是您在测试查询时会注意到它。
那么，如果不启用此选项并且缓存已满，现在会发生什么情况1-Off Ad-Hoc查询？

缓存管理算法：

随着此优化功能的引入，缓存管理算法也得到了更新。
Kimberly Tripp的本文还引用了Kalen Delaney的有关此算法更改的文章。
她对此进行了最好的解释：


更改实际上计算了SQL Server
识别出的计划缓存大小存在内存压力，它将开始从缓存中删除计划。要删除的计划是尚未重用的便宜计划，这是一件好事。


这意味着那些讨厌的一次性计划将是第一个当您需要释放资源时可以使用。

所以现在的问题变成了：

。“为什么当SQL Server照顾到我们时，我们需要'针对临时工作负载进行优化'必要时删除未使用的计划吗？“
我的回答是，如果您经常有大量的动态SQL生成非参数化即席查询，那么打开此功能非常有意义。 。
您要避免对系统资源造成压力，以免在用完最大缓存内存空间后强制执行缓存计划/数据删除。

我怎么知道

这是我写的一个查询，用来向您显示您当前缓存了多少个临时计划以及正在消耗多少磁盘空间（结果将在整个过程中发生变化一天-因此，请在繁重的时间内进行测试）：

--Great query for making the argument to use "Optimize for Ad Hoc Workloads":
SELECT S.CacheType, S.Avg_Use, S.Avg_Multi_Use,
       S.Total_Plan_3orMore_Use, S.Total_Plan_2_Use, S.Total_Plan_1_Use, S.Total_Plan,
       CAST( (S.Total_Plan_1_Use * 1.0 / S.Total_Plan) as Decimal(18,2) )[Pct_Plan_1_Use],
       S.Total_MB_1_Use,   S.Total_MB,
       CAST( (S.Total_MB_1_Use   * 1.0 / S.Total_MB  ) as Decimal(18,2) )[Pct_MB_1_Use]
  FROM
  (
    SELECT CP.objtype[CacheType],
           COUNT(*)[Total_Plan],
           SUM(CASE WHEN CP.usecounts > 2 THEN 1 ELSE 0 END)[Total_Plan_3orMore_Use],
           SUM(CASE WHEN CP.usecounts = 2 THEN 1 ELSE 0 END)[Total_Plan_2_Use],
           SUM(CASE WHEN CP.usecounts = 1 THEN 1 ELSE 0 END)[Total_Plan_1_Use],
           CAST((SUM(CP.size_in_bytes * 1.0) / 1024 / 1024) as Decimal(12,2) )[Total_MB],
           CAST((SUM(CASE WHEN CP.usecounts = 1 THEN (CP.size_in_bytes * 1.0) ELSE 0 END)
                      / 1024 / 1024) as Decimal(18,2) )[Total_MB_1_Use],
           CAST(AVG(CP.usecounts * 1.0) as Decimal(12,2))[Avg_Use],
           CAST(AVG(CASE WHEN CP.usecounts > 1 THEN (CP.usecounts * 1.0)
                         ELSE NULL END) as Decimal(12,2))[Avg_Multi_Use]
      FROM sys.dm_exec_cached_plans as CP
     GROUP BY CP.objtype
  ) AS S
 ORDER BY S.CacheType

结果：


我不会说“当您有X MB时”或“如果X％的Ad Hoc是一次性使用”来启用此功能。
它不会影响Sproc，触发器，视图或参数化/准备好的SQL-仅是临时查询。
我个人的建议是仅在您的Prod环境中打开，但考虑在您的Dev环境中将其保留。
我只对Dev这么说，因为如果您要优化一个需要花一分钟或更长时间才能运行的查询，那么您不希望对其运行3次，就可能无法查看该缓存的运行速度-每次编辑该查询来查找最佳的优化设计。
如果您的工作不涉及一整天的工作，那么请发疯并要求您的DBA在任何地方打开它。

#4 楼

假设一个生产服务器仅提供5个不同的查询，但是每秒可以处理数千个查询。您是Microsoft SQL Server开发团队。您将摆弄计划缓存。当您知道某些最大和最关键的客户端（例如Microsoft的内部SAP实施）在同一校园中工作并使用相同的自助餐厅时，默认情况下是否打开此行为？

#5 楼

“为什么我不应该使用...。”
在进行某些性能调查的过程中，几乎实时地将计划从计划缓存中拉出，同时观察资源利用率可能会非常有帮助。 “针对即席工作负载进行优化”可能会破坏这种情况，因为即席存根计划在查询缓存时不会返回计划。
在这种情况下，如果无法确定查询和计划，则设置可以为了进行调查，将其再次打开和关闭。请注意，设置的更改会影响从此刻开始编译的查询。另外，每当更改“服务器”属性时，请检查相同版本的非生产实例，以确认更改是否将刷新计划缓存。我个人讨厌对此感到惊讶。 （例如，在服务器级别更改maxdop通常会刷新计划缓存，而在Resource Governor中更改dop不会。）

”已编译的计划存根没有与之关联的执行计划，并查询了计划句柄将不会返回XML Showplan。”
http://technet.microsoft.com/zh-cn/library/cc645587.aspx