upc brno internet Removing Plans from Cache in VB.NET

Generating UPCA in VB.NET Removing Plans from Cache

In addition to needing to recompile a plan based on schema or statistics changes, SQL Server needs to compile plans for batches if all previous plans have been removed from the plan cache. Plans are removed from cache based on memory pressure, which we talk about in the

9

section entitled Cache Size Management, later in this chapter. However, other operations can cause plans to be removed from cache. Some of these operations remove all the plans from a particular database, and others remove all the plans for the entire SQL Server instance. The following operations ush the entire plan cache so that all batches submitted afterwards will need a fresh plan. Note that although some of these operations affect only a single database, the entire plan cache is cleared.

Upgrading any database to SQL Server 2008 Running the DBCC FREEPROCCACHE or DBCC FREESYSTEMCACHE commands Changing any of the following con guration options:

cross db ownership chaining index create memory cost threshold for parallelism max degree of parallelism max text repl size min memory per query min server memory max server memory query governor cost limit query wait remote query timeout user options

The following operations clear all plans associated with a particular database:

Running the DBCC FLUSHPROCINDB command Detaching a database Closing or opening an auto-close database Modifying a collation for a database using the ALTER DATABASE . . . COLLATE command Altering a database with any of the following commands:

ALTER DATABASE . . . MODIFY_NAME ALTER DATABASE . . . MODIFY FILEGROUP ALTER DATABASE . . . SET ONLINE ALTER DATABASE . . . SET OFFLINE ALTER DATABASE . . . SET EMERGENCY

ALTER DATABASE . . . SET READ_ONLY ALTER DATABASE . . . SET READ_WRITE ALTER DATABASE . . . COLLATE

Clearing a single plan from cache can be done in a couple of different ways. First, you can create a plan guide that exactly matches the SQL text for the cached plan, and then all plans with that text will be removed automatically. SQL Server 2008 provides an easy way of creating a plan guide from plan cache. We look at plan guides in detail later in the chapter. The second method of removing a single plan from cache is new in SQL Server 2008 and uses new options for DBCC FREEPROCCACHE. The syntax is illustrated in the following code:

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

This command now allows you to specify one of three parameters to indicate which plan or plans you want to remove from cache:

plan_handle By specifying a plan_handle, you can remove the plan with that handle from cache. (The plan_handle is guaranteed to be unique for all currently existing plans.) sql_handle By specifying a sql_handle, you can remove the plans with that handle from cache. You can have multiple plans for the same SQL text if any of the cache key values are changed, such as SET options. The following code illustrates this:

USE Northwind2; GO DBCC FREEPROCCACHE; GO SET ANSI_NULLS ON GO SELECT * FROM orders WHERE customerid = 'HANAR'; GO SELECT * FROM Orders WHERE CustomerID = 'CENTC'; GO SET ANSI_NULLS OFF GO SELECT * FROM orders WHERE customerid = 'HANAR'; GO SET ANSI_NULLS ON GO -- Now examine the sys.dm_exec_query_stats view and notice two different rows for the -- query searching for 'HANAR' SELECT execution_count, text, sql_handle, query_plan FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS TXT CROSS APPLY sys.dm_exec_query_plan(plan_handle)AS PLN; GO

9

-- The two rows containing 'HANAR'should have the same value for sql_handle; -- Copy that sql_handle value and paste into the command below: DBCC FREEPROCCACHE(0x02000000CECDF507D9D4D70720F581172A42506136AA80BA); GO -- If you examine sys.dm_exec_query_stats again, you see the rows for this query -- have been removed SELECT execution_count, text, sql_handle, query_plan FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS TXT CROSS APPLY sys.dm_exec_query_plan(plan_handle)AS PLN; GO

pool_name By specifying the name of a Resource Governor pool, you can clear all the plans in cache that are associated with queries that were assigned to workload group using the speci ed resource pool. (The Resource Governor, workload groups, and resource pools were discussed in 1, SQL Server 2008 Architecture and Con guration. )