DBCC FREEPROCCACHE (Transact-SQL)DBCC FREEPROCCACHE (Transact-SQL)

适用对象: yesSQL ServernoAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

删除计划高速缓存中的所有元素,通过指定计划句柄或 SQL 句柄从计划高速缓存中删除特定计划,或者删除与指定资源池相关联的所有高速缓存条目。Removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool.

备注

DBCC FREEPROCCACHE 不清除本机编译的存储过程的执行统计信息。DBCC FREEPROCCACHE does not clear the execution statistics for natively compiled stored procedures. 过程高速缓存不包含有关本机编译的存储过程的信息。The procedure cache does not contain information about natively compiled stored procedures. 从过程执行中收集的任何执行统计信息都将显示在执行统计信息 DMV 中:sys.dm_exec_procedure_stats (Transact-SQL)sys.dm_exec_query_plan (Transact-SQL)Any execution statistics collected from procedure executions will appear in the execution statistics DMVs: sys.dm_exec_procedure_stats (Transact-SQL) and sys.dm_exec_query_plan (Transact-SQL).

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

SQL Server 语法:Syntax for SQL Server:

DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ]  

Azure SQL 数据仓库和并行数据仓库语法:Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse:

DBCC FREEPROCCACHE [ ( COMPUTE | ALL ) ] 
     [ WITH NO_INFOMSGS ]   
[;]  

参数Arguments

( { plan_handle | sql_handle | pool_name } )( { plan_handle | sql_handle | pool_name } )
plan_handle 用于唯一标识已执行并且其计划驻留在计划缓存中的批处理的查询计划。plan_handle uniquely identifies a query plan for a batch that has executed and whose plan resides in the plan cache. plan_handle 为 varbinary(64),可以从下列动态管理对象中获得计划句柄:plan_handle is varbinary(64) and can be obtained from the following dynamic management objects:

sql_handle 是要清除的批处理的 SQL 句柄。sql_handle is the SQL handle of the batch to be cleared. sql_handle 为 varbinary(64),可以从下列动态管理对象中获得计划句柄:sql_handle is varbinary(64) and can be obtained from the following dynamic management objects:

pool_name 是资源调控器资源池的名称。pool_name is the name of a Resource Governor resource pool. pool_name 的数据类型为 sysname,可通过查询 sys.dm_resource_governor_resource_pools 动态管理视图获得此参数。pool_name is sysname and can be obtained by querying the sys.dm_resource_governor_resource_pools dynamic management view.
若要将资源调控器工作负荷组与资源池相关联,请查询 sys.dm_resource_governor_workload_groups 动态管理视图。To associate a Resource Governor workload group with a resource pool, query the sys.dm_resource_governor_workload_groups dynamic management view. 有关会话的工作负荷组的信息,请查询 sys.dm_exec_sessions 动态管理视图。For information about the workload group for a session, query the sys.dm_exec_sessions dynamic management view.

WITH NO_INFOMSGSWITH NO_INFOMSGS
取消显示所有信息性消息。Suppresses all informational messages.

COMPUTECOMPUTE
从每个“计算”节点,清除查询计划缓存。Purge the query plan cache from each Compute node. 这是默认值。This is the default value.

ALLALL
从每个“计算”节点和“管理”节点,清除查询计划缓存。Purge the query plan cache from each Compute node and from the Control node.

备注

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,使用 ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE 清除范围内数据库的过程(计划)缓存。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), the ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE to clear the procedure (plan) cache for the database in scope.

RemarksRemarks

小心使用 DBCC FREEPROCCACHE 清除计划高速缓存。Use DBCC FREEPROCCACHE to clear the plan cache carefully. 清除过程(计划)缓存会逐出所有计划,并且传入查询执行将编译新计划,而不是重复使用任何以前缓存的计划。Clearing the procedure (plan) cache causes all plans to be evicted, and incoming query executions will compile a new plan, instead of reusing any previously cached plan.

由于新编译数量增加,这可能导致查询性能骤降。This can cause a sudden, temporary decrease in query performance as the number of new compilations increases. 对于计划缓存中每个已清除的缓存存储区,SQL ServerSQL Server 错误日志将包含以下信息性消息:“由于 'DBCC FREEPROCCACHE' 或 'DBCC FREESYSTEMCACHE' 操作,SQL ServerSQL Server 经历了 '%s' 缓存存储区(计划缓存的一部分)的 %d 次刷新。”For each cleared cachestore in the plan cache, the SQL ServerSQL Server error log will contain the following informational message: " SQL ServerSQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations." 每隔五分钟,只要缓存在这段时间间隔内得到刷新,此消息就记录一次。This message is logged every five minutes as long as the cache is flushed within that time interval.

以下重新配置操作也将清除过程缓存:The following reconfigure operations also clear the procedure cache:

  • 访问检查缓存存储桶计数access check cache bucket count
  • 访问检查缓存配额access check cache quota
  • clr enabledclr enabled
  • 并行的开销阈值cost threshold for parallelism
  • cross db ownership chainingcross db ownership chaining
  • 索引创建内存index create memory
  • 最大并行度max degree of parallelism
  • max server memorymax server memory
  • max text repl sizemax text repl size
  • 最大工作线程数max worker threads
  • min memory per querymin memory per query
  • min server memorymin server memory
  • 查询调控器开销限制query governor cost limit
  • 查询等待query wait
  • remote query timeoutremote query timeout
  • user optionsuser options

结果集Result Sets

如果未指定 WITH NO_INFOMSGS 子句,DBCC FREEPROCCACHE 将返回:“DBCC 执行完毕。When the WITH NO_INFOMSGS clause is not specified, DBCC FREEPROCCACHE returns: "DBCC execution completed. 如果 DBCC 输出了错误消息,请与系统管理员联系。”If DBCC printed error messages, contact your system administrator."

PermissionsPermissions

适用范围:SQL ServerSQL Server并行数据仓库Parallel Data WarehouseApplies to: SQL ServerSQL Server, 并行数据仓库Parallel Data Warehouse

  • 需要对服务器的 ALTER SERVER STATE 权限。Requires ALTER SERVER STATE permission on the server.

适用范围:SQL 数据仓库SQL Data WarehouseApplies to: SQL 数据仓库SQL Data Warehouse

  • 要求具有 DB_OWNER 固定服务器角色中的成员资格。Requires membership in the DB_OWNER fixed server role.

SQL 数据仓库SQL Data Warehouse并行数据仓库Parallel Data Warehouse 的一般备注General Remarks for SQL 数据仓库SQL Data Warehouse and 并行数据仓库Parallel Data Warehouse

可以同时运行多个 DBCC FREEPROCCACHE 命令。Multiple DBCC FREEPROCCACHE commands can be run concurrently. SQL 数据仓库SQL Data Warehouse并行数据仓库Parallel Data Warehouse 中,清除计划缓存可能导致查询性能暂时性降低,因为传入查询编译新计划,而不是重复使用任何以前缓存的计划。In SQL 数据仓库SQL Data Warehouse or 并行数据仓库Parallel Data Warehouse, clearing the plan cache can cause a temporary decrease in query performance as incoming queries compile a new plan, instead of reusing any previously cached plan.

在计算节点上运行时,DBCC FREEPROCCACHE (COMPUTE) 仅会导致 SQL ServerSQL Server 重新编译查询。DBCC FREEPROCCACHE (COMPUTE) only causes SQL ServerSQL Server to recompile queries when they are run on the Compute nodes. 它不会导致 SQL 数据仓库SQL Data Warehouse并行数据仓库Parallel Data Warehouse 重新编译在控制节点上生成的并行查询计划。It does not cause SQL 数据仓库SQL Data Warehouse or 并行数据仓库Parallel Data Warehouse to recompile the parallel query plan that is generated on the Control node. 可在执行期间取消 DBCC FREEPROCCACHE。DBCC FREEPROCCACHE can be cancelled during execution.

SQL 数据仓库SQL Data Warehouse并行数据仓库Parallel Data Warehouse 的限制与局限Limitations and Restrictions for SQL 数据仓库SQL Data Warehouse and 并行数据仓库Parallel Data Warehouse

无法在事务中运行 DBCC FREEPROCCACHE。DBCC FREEPROCCACHE can not run within a transaction. EXPLAIN 语句中不支持使用 DBCC FREEPROCCACHE。DBCC FREEPROCCACHE is not supported in an EXPLAIN statement.

SQL 数据仓库SQL Data Warehouse并行数据仓库Parallel Data Warehouse 的元数据Metadata for SQL 数据仓库SQL Data Warehouse and 并行数据仓库Parallel Data Warehouse

运行 DBCC FREEPROCCACHE 时,会向 sys.pdw_exec_requests 系统视图中添加新行。A new row is added to the sys.pdw_exec_requests system view when DBCC FREEPROCCACHE is run.

示例:SQL ServerSQL ServerExamples: SQL ServerSQL Server

A.A. 从计划高速缓存中清除查询计划Clearing a query plan from the plan cache

以下示例通过指定查询计划句柄从计划高速缓存中清除查询计划。The following example clears a query plan from the plan cache by specifying the query plan handle. 为了确保示例查询在计划高速缓存中,首先执行该查询。To ensure the example query is in the plan cache, the query is first executed. 将查询 sys.dm_exec_cached_planssys.dm_exec_sql_text 动态管理视图以返回查询的计划句柄。The sys.dm_exec_cached_plans and sys.dm_exec_sql_text dynamic management views are queried to return the plan handle for the query.

然后,将结果集中的计划句柄值插入 DBCC FREEPROCACHE 语句,以从计划高速缓存中仅删除该计划。The plan handle value from the result set is then inserted into the DBCC FREEPROCACHE statement to remove only that plan from the plan cache.

USE AdventureWorks2012;  
GO  
SELECT * FROM Person.Address;  
GO  
SELECT plan_handle, st.text  
FROM sys.dm_exec_cached_plans   
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st  
WHERE text LIKE N'SELECT * FROM Person.Address%';  
GO  

下面是结果集:Here is the result set.

plan_handle                                         text  
--------------------------------------------------  -----------------------------  
0x060006001ECA270EC0215D05000000000000000000000000  SELECT * FROM Person.Address;  
  
(1 row(s) affected)
-- Remove the specific plan from the cache.  
DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000);  
GO  

B.B. 清除计划高速缓存中的所有计划Clearing all plans from the plan cache

以下示例清除计划高速缓存中的所有元素。The following example clears all elements from the plan cache. 指定了 WITH NO_INFOMSGS 子句来阻止显示信息消息。The WITH NO_INFOMSGS clause is specified to prevent the information message from being displayed.

DBCC FREEPROCCACHE WITH NO_INFOMSGS;  

C.C. 清除与资源池相关联的所有高速缓存条目Clearing all cache entries associated with a resource pool

以下示例清除与指定资源池相关联的所有高速缓存条目。The following example clears all cache entries associated with a specified resource pool. sys.dm_resource_governor_resource_pools 视图首先被查询,以便获取 pool_name 的值。The sys.dm_resource_governor_resource_pools view is first queried to obtain the value for pool_name.

SELECT * FROM sys.dm_resource_governor_resource_pools;  
GO  
DBCC FREEPROCCACHE ('default');  
GO  

示例:SQL 数据仓库SQL Data Warehouse并行数据仓库Parallel Data WarehouseExamples: SQL 数据仓库SQL Data Warehouse and 并行数据仓库Parallel Data Warehouse

D.D. DBCC FREEPROCCACHE 基本语法示例DBCC FREEPROCCACHE Basic Syntax Examples

以下示例从计算节点中删除所有现有的查询计划缓存。The following example removes all existing query plan caches from the Compute nodes. 虽然上下文设置为 UserDbSales,但所有数据库的计算节点查询计划缓存都将删除。Although the context is set to UserDbSales, the Compute node query plan caches for all databases will be removed. WITH NO_INFOMSGS 子句可防止结果中显示信息性消息。The WITH NO_INFOMSGS clause prevents informational messages from appearing in the results.

USE UserDbSales;  
DBCC FREEPROCCACHE (COMPUTE) WITH NO_INFOMSGS;

以下示例的结果与上一个示例的结果相同,只是将在结果中显示信息性消息。The following example has the same results as the previous example, except that informational messages will show in the results.

USE UserDbSales;  
DBCC FREEPROCCACHE (COMPUTE);  

如果请求了信息性消息且执行成功,则查询结果中每个计算节点各具一行。When informational messages are requested and the execution is successful, the query results will have one line per Compute node.

E.E. 授予权限运行 DBCC FREEPROCCACHEGranting Permission to run DBCC FREEPROCCACHE

以下示例为登录名 David 提供权限,允许其运行 DBCC FREEPROCCACHE。The following example gives the login David permission to run DBCC FREEPROCCACHE.

GRANT ALTER SERVER STATE TO David; 
GO

另请参阅See Also

DBCC (Transact-SQL)DBCC (Transact-SQL)
资源调控器Resource Governor
ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)