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

適用於: 是SQL Server 否Azure SQL Database 是Azure Synapse Analytics (SQL DW) 是平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database yesAzure Synapse Analytics (SQL DW) 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_handlevarbinary(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_handlevarbinary(64) ,並可從下列動態管理物件中取得:sql_handle is varbinary(64) and can be obtained from the following dynamic management objects:

pool_name 是 Resource Governor 資源集區的名稱。pool_name is the name of a Resource Governor resource pool. pool_namesysname,並可以藉由查詢 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.
若要將 Resource Governor 工作負載群組與資源集區產生關聯,請查詢 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:

  • 存取檢查快取 Bucket 計數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."

權限Permissions

適用於: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 (計算) 只會在於計算節點上執行時,使 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 FREEPROCCACHE 的權限Granting 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)