缓存 负载均衡
When you run a query in SQL Server, the SQL Server Query Optimizer will draw the road map for that query, specifying the optimal way to execute it, which is called the query execution plan.
在SQL Server中运行查询时，SQL Server Query Optimizer将为该查询绘制路线图，并指定执行该查询的最佳方式，这称为查询执行计划。
Generating the execution plan will take few milliseconds from the CPU cycles, which is negligible for one query or small load, but it will be considerable for a very heavy transactional workload. Because of this, SQL Server caches these generated plans in a special type of memory called the Plan Cache to eliminate the overhead generated by the query plan if the same query is executed again. When you submit your query to the SQL Server Engine, it will search in the plan cache if there is any existing execution plan that can be reused, if an available execution plan is found in the plan cache, the plan will be used to execute that query, otherwise, the SQL Server Query Optimizer will create a new plan and keep it in the plan cache for future use.
生成执行计划需要占用CPU周期数毫秒的时间，对于一个查询或较小的负载而言，这可以忽略不计，但是对于非常繁重的事务性工作而言，这将是相当可观的。 因此，SQL Server将这些生成的计划缓存在一种称为“ 计划缓存”的特殊类型的内存中，以消除再次执行同一查询时查询计划所产生的开销。 当您向SQL Server Engine提交查询时，它将在计划缓存中搜索是否有任何现有的可重复使用的执行计划，如果在计划缓存中找到了可用的执行计划，该计划将用于执行该计划。查询，否则，SQL Server查询优化器将创建一个新计划并将其保留在计划缓存中以备将来使用。
There will be some cases, where the query is called one time and never executed again. Like any other SQL query, the SQL Server Query Optimizer will create an execution plan for that query and save it in the plan cache for further reuse. If there are many query plans for ad hoc queries that run one time and never executed again, these plans will consume the plan cache memory although these plans will not be visited again. In this case, we are wasting an important SQL Server resource, which is memory, to save a few milliseconds of time and CPU cycles that are consumed while creating new plans.
在某些情况下，查询将被调用一次，而不再执行。 与其他任何SQL查询一样，SQL Server查询优化器将为该查询创建一个执行计划，并将其保存在计划缓存中以供进一步重用。 如果有很多针对一次性查询的查询计划，这些查询计划一次运行并且从未执行过，则这些计划将消耗计划高速缓存内存，尽管不会再次访问这些计划。 在这种情况下，我们浪费了重要SQL Server资源（即内存），以节省创建新计划时所消耗的几毫秒时间和CPU周期。
Starting from SQL Server 2008, a new server level setting is introduced that can be used to improve the plan cache efficiency for such workload type that has many ad hoc queries that are not called frequently. When setting the Optimize for Ad hoc Workloads option to 1, the SQL Server Database Engine will store a small compiled plan stub value in the plan cache when the query is compiled for the first time, rather than storing the full execution plan for that query. If the query is executed again, the stub value will help the SQL Server Database Engine to determine that this ad hoc query has been complied one time before, so this stub value will be removed from the plan cache and replaced by the full query execution plan for further reuse after compiling the query again. In this way, the memory pressure that is caused by storing the execution plans for all running queries will be eliminated without performing any change in your applications, as the plan cache will not be filled with many execution plans for ad hoc queries that are executed once and will not be called again and keep that memory to store the plans that are really executed frequently.
从SQL Server 2008开始，引入了新的服务器级别设置，该设置可用于提高具有许多不经常调用的临时查询的工作负载类型的计划缓存效率。 将“ 优化临时工作负载”选项设置为1时，SQL Server数据库引擎将在第一次编译查询时在计划缓存中存储一个小的已编译计划存根值，而不是存储该查询的完整执行计划。 如果再次执行查询，则存根值将帮助SQL Server数据库引擎确定该临时查询已被编译一次，因此将从计划缓存中删除此存根值，并用完整的查询执行计划代替以便在再次编译查询后进一步重用。 这样，无需为应用程序执行任何更改，就可以消除存储所有正在运行的查询的执行计划所引起的内存压力，因为计划缓存不会充满很多一次性执行的临时查询的执行计划并且不会再次被调用并保留该内存以存储真正经常执行的计划。
Let us go through our demo to understand how the Optimize for Ad hoc Workloads option works practically. We will clear the plan cache first using the DBCC FREEPROCCACHE command and run a simple SELECT query from the Employees table in the SQLShackDemo test database:
让我们来看一下演示，以了解“ 优化临时工作负载”选项实际上如何工作。 我们将首先使用DBCC FREEPROCCACHE命令清除计划缓存，并从SQLShackDemo测试数据库中的Employees表运行一个简单的SELECT查询：
USE SQLShackDemo
GO
DBCC FREEPROCCACHE
GO
SELECT * FROM [SQLShackDemo].[dbo].[Employees]
WHERE [EmpName]='Emily'
Then we will use the sys.dm_exec_cached_plans dynamic management object to return the cached execution plans in the plan cache memory storage and view the number of reuse of these execution plans, the amount of memory taken by each cached plan and the cached query text by CROSS APPLY that dynamic management object with the sys.dm_exec_sql_text dynamic management object passing the plan handle as parameter as follows:
然后，我们将使用sys.dm_exec_cached_plans动态管理对象返回计划缓存内存存储中的缓存执行计划，并通过CROSS查看这些执行计划的重用次数，每个缓存计划占用的内存量以及缓存的查询文本。将该动态管理对象与sys.dm_exec_sql_text动态管理对象传递给计划句柄作为参数，如下所示：
SELECT usecounts AS NumOfExecutions,cacheobjtype,objtype AS ObjectType,size_in_bytes AS PlanSize,[text] AS QueryText
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
The result of the previous query will show us that, the execution plan for our simple ad hoc SELECT statement is stored in the plan cache memory as a full compiled plan, executed only once and consuming 16 KB from the plan cache total storage, which is not too much if we have large memory on our SQL Server or the load in our server is not heavy. However, you need to take that into consideration if your system is a heavy transactional system with thousands of ad hoc queries and that they will eventually consume the plan cache part of the memory affecting the overall performance of your SQL server. In this case, it is time to think about using the Optimize for Ad hoc Workloads option.
上一个查询的结果将向我们显示，我们简单的临时SELECT语句的执行计划以完整编译的计划的形式存储在计划缓存中，仅执行一次，并且消耗了计划缓存的总存储空间16 KB如果我们SQL Server上的内存很大或服务器上的负载并不沉重，则不要过多。 但是，如果您的系统是一个具有数千个临时查询的繁重事务系统，并且它们最终将占用内存的计划缓存部分，从而影响SQL Server的整体性能，则需要考虑到这一点。 在这种情况下，是时候考虑使用“ 为临时工作负载优化”选项了。
To have a good indication that our workload requires enabling the Optimize for Ad hoc Workloads option we will query sys.dm_exec_cached_plans dynamic management object again for the total number of execution plans stored in the plan cache for each plan cache type, and the total size of memory consumed by each plan cache type as below:
为了很好地表明我们的工作量需要启用“ 优化临时工作量”选项，我们将再次查询sys.dm_exec_cached_plans动态管理对象，以获取每种计划缓存类型中计划缓存中存储的执行计划总数。每种计划缓存类型消耗的内存如下：
SELECT objtype AS [PlanCacheType],
COUNT_BIG(1) AS [NumOfPlans],
SUM(CAST(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS [PlanSizeInMB]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY NumOfPlans desc
GO
If the result of the previous query shows that, you have huge number of execution plans of Ad hoc plan cache type consuming huge amount of your plan cache memory, then this is a good indication that you should enable the Optimize for Ad hoc Workloads server option. The below result is from one of our production servers showing that more than 8 GB of memory are consumed to keep the execution plans of 154045 ad hoc plans:
如果上一个查询的结果表明，您有大量的临时计划缓存类型的执行计划消耗了大量的计划缓存内存，那么这表明您应该启用“ 优化临时工作负载”服务器选项。 以下结果来自我们的其中一台生产服务器，该结果表明消耗超过8 GB的内存来保留154045临时计划的执行计划：
The Optimize for Ad hoc Workloads option is an advanced server configuration that you will not be able to configure without enabling the show advanced options using sp_configure command. After being able to show the advanced server options, we can enable the Optimize for Ad hoc Workloads option using the sp_configure command setting its value to 1 and perform the RECONFIGURE command to take effect as below:
“ 优化临时工作负载”选项是高级服务器配置，如果不使用sp_configure命令启用show advanced options ，将无法配置该服务器。 能够显示高级服务器选项后，我们可以使用sp_configure命令将其值设置为1来启用“ 优化临时工作负载”选项，并执行RECONFIGURE命令以使生效，如下所示：
SP_CONFIGURE 'Show Advanced Options', 1
GO
RECONFIGURE
GO
EXEC sys.sp_configure N'Optimize for Ad hoc Workloads ', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
The below message will be displayed after executing the previous command showing that the show advanced and Optimize for Ad hoc Workloads option options are enabled as their values are changed from 0 to 1:
执行上一条命令后，将显示以下消息，表明将高级显示和优化临时工作负载选项选项启用，因为它们的值从0更改为1：
Enabling the Optimize for Ad hoc Workloads option will affect only the new cached plans, without affecting the plans that are already stored in the plan cache storage. You can also enable the Optimize for Ad hoc Workloads option using the SQL Server Management Studio from the Advanced page of the Server Properties window by setting the value to True as in the below snapshot:
启用“ 针对临时工作负载优化”选项将仅影响新的缓存计划，而不会影响已存储在计划缓存存储中的计划。 您还可以通过在“服务器属性”窗口的“高级”页面中使用SQL Server Management Studio启用“ 优化临时工作负载”选项，方法是将值设置为True，如以下快照所示：
Now the Optimize for Ad hoc Workloads option is enabled. If we clear the plan cache and perform the previous SELECT statement again:
现在，启用了“ 优化临时工作负载”选项。 如果我们清除计划缓存并再次执行前面的SELECT语句：
USE SQLShackDemo
GO
DBCC FREEPROCCACHE
GO
SELECT * FROM [SQLShackDemo].[dbo].[Employees]
WHERE [EmpName]='Emily'
Then check if that plan is stored in the plan cache by querying the sys.dm_exec_cached_plans dynamic management object:
然后通过查询sys.dm_exec_cached_plans动态管理对象来检查该计划是否存储在计划缓存中：
SELECT usecounts AS NumOfExecutions,cacheobjtype,objtype AS ObjectType,size_in_bytes AS PlanSize,[text] AS QueryText
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
The result will show us that only a Compiled Plan Stub is stored for that query consuming only 384 bytes from the plan cache storage compared to the 16 KB size when storing it from the first execution as a full plan before enabling the Optimize for Ad hoc Workloads option:
结果会告诉我们，只有编译计划存根保存该查询消费从计划缓存存储只有384字节使优化的Ad hoc工作负载之前，从第一个执行一个完整的计划，存储时相比，16 KB大小选项：
Let us run the SELECT statement again:
让我们再次运行SELECT语句：
USE SQLShackDemo
GO
SELECT * FROM [SQLShackDemo].[dbo].[Employees]
WHERE [EmpName]='Emily'
Then check what is stored now in the plan cache for that query by querying the sys.dm_exec_cached_plans dynamic management object:
然后通过查询sys.dm_exec_cached_plans动态管理对象来检查该查询现在存储在计划缓存中的内容：
SELECT usecounts AS NumOfExecutions,cacheobjtype,objtype AS ObjectType,size_in_bytes AS PlanSize,[text] AS QueryText
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
The result will show us that the Compiled Plan Stub for that query is replaced by full compiled plan with size 16 KB that is executed only once. Running the query again and again, the number of executions will be increased indicating that the query that is executed frequently will use the same query plan that is stored in the plan cache, so we are not wasting the plan cache storage for a query that is used once:
结果将向我们显示该查询的“已编译计划”存根被大小为16 KB的完整已编译计划所取代，该计划仅执行一次。 一次又一次地运行查询，执行次数将增加，这表明频繁执行的查询将使用与计划缓存中存储的查询计划相同的查询，因此我们不会浪费查询的计划缓存存储使用一次：
If the cached execution plans in your system still not fitting the plan cache storage although you enabled the Optimize for Ad hoc Workloads option and you are not able to extend your SQL Server memory or perform changes in your application, it is better to periodically clear the plan cache storage or minimum clear the ad hoc queries plans from the plan cache using the below DBCC command:
如果您启用了“ 优化临时工作负载”选项，但是系统中的缓存执行计划仍不适合计划缓存存储，并且您无法扩展SQL Server内存或在应用程序中执行更改，则最好定期清除计划缓存存储或使用以下DBCC命令从计划缓存中清除临时查询计划：
DBCC FREESYSTEMCACHE('SQL Plans')
结论 (Conclusion)
The SQL Server Database Engine caches the execution plans for the running queries in a special type of memory storage called the plan cache, including the queries that are running only once. Such plans for the queries that will not be executed again will waste plan cache storage resources, as these stored plans will not be visited again. To overcome such issue, SQL Server 2008 introduced the Optimize for Ad hoc Workloads option in which only a small compiled plan stub will be stored in the plan cache when the query is executed at the first time and will be replaced with the full execution plan if it is executed again. In this way, you will save the plan cache storage from being consumed by less frequently used plans and keep it for the queries that are running frequently.
SQL Server数据库引擎将正在运行的查询的执行计划缓存在一种称为计划缓存的特殊类型的内存中，包括仅运行一次的查询。 这种不会再次执行的查询计划将浪费计划缓存存储资源，因为将不会再次访问这些存储的计划。 为解决此问题，SQL Server 2008引入了“ 针对临时工作负载的优化”选项，该选项在第一次执行查询时仅将一个小的编译计划存根存储在计划缓存中，并且在执行该查询时将替换为完整的执行计划。它会再次执行。 这样，您可以保存计划缓存存储，以免被不经常使用的计划占用，并将其保留给经常运行的查询。
缓存 负载均衡
扫一扫
373
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
