DBCC FREEPROCCACHE (Azure SQL Data Warehouse, Parallel Data Warehouse)

THIS TOPIC APPLIES TO:SQL Server Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse

Removes all query plan cache entries for all databases from the Compute nodes. Use this statement to force SQL Data Warehouse or Parallel Data Warehouse to recompile all queries the next time they are run.

For example, when an existing query plan was generated with out-of-date statistics, you can improve query performance by updating statistics and then recompiling the query. To force SQL Data Warehouse or Parallel Data Warehouse to recompile the query instead of using the existing query plan, you can use DBCC FREEPROCCACHE to remove the existing query plans. Upon the next execution of the query, SQL Data Warehouse or Parallel Data Warehouse will recompile the query with the latest statistics.

This topic applies to SQL Data Warehouse and Parallel Data Warehouse. For SQL Server, see DBCC FREEPROCCACHE (Transact-SQL).

 Transact-SQL Syntax Conventions (Transact-SQL)

Syntax

-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse
DBCC FREEPROCCACHE ( COMPUTE | ALL )
     [ WITH NO_INFOMSGS ] 
[;]

Arguments

• COMPUTE
  Purge the query plan cache from each Compute node.

• ALL
  Purge the query plan cache from each Compute node and from the Control node. This is the default if you do not specify a value.

• WITH NO_INFOMSGS
  Results do not contain informational messages.

Permissions

Requires ALTER SERVER STATE permission.

General Remarks

Multiple DBCC FREEPROCCACHE commands can be run concurrently.

In SQL Data Warehouse or Parallel Data Warehouse, clearing the query plan cache can cause a temporary decrease in query performance when queries are recompiled. DBCC FREEPROCCACHE (COMPUTE) only causes SQL Server to recompile queries when they are run on the Compute nodes. It does not cause SQL Data Warehouse or Parallel Data Warehouse to recompile the parallel query plan that is generated on the Control node.

DBCC FREEPROCCACHE can be cancelled during execution.

Error Handling

Errors are reported in the sys.dm_pdw_errors (SQL Data Warehouse) system view.

Limitations and Restrictions

DBCC FREEPROCCACHE can not run within a transaction.

DBCC FREEPROCCAHCE is not supported in an EXPLAIN statement.

Metadata

A new row is added to the sys.pdw_exec_requests system view when DBCC FREEPROCCACHE is run.

Examples: SQL Data Warehouse and Parallel Data Warehouse

A. DBCC FREEPROCCACHE Basic Syntax Examples

The following example removes all existing query plan caches from the Compute nodes. Although the context is set to UserDbSales, the Compute node query plan caches for all databases will will be removed. 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.

B. Granting Permission to run DBCC FREEPROCCACHE

The following example gives the login David permission to run DBCC FREEPROCCACHE.

GRANT ALTER SERVER STATE TO David;