/*
** Author:	Rainer Unwin
** Purpose:	This script is designed to drop all single use plans more than 1 hour old
** Warning:	You use this script at your own risk and take responsibility for what it does. No warranty is made implied or otherwise.
*/

/*
** Create and populate a table to hold the plan handles to be dropped
*/
DECLARE @DropTable TABLE (
	ID			INT IDENTITY PRIMARY KEY,
	PlanHandle	VARBINARY( 64 )
);

INSERT INTO @DropTable( PlanHandle )
SELECT plan_handle
FROM sys.dm_exec_query_stats
WHERE execution_count = CAST( 1 AS BIGINT )
	AND creation_time < DATEADD( HOUR, -1, GETDATE() );

/*
** Declare and set some variables for the loop
*/
DECLARE @Index		INT,
	@Max			INT,
	@SQLCommand		NVARCHAR( 2000 ),
	@Parameters		NVARCHAR( 2000 ),
	@PlanHandle		VARBINARY( 64 ),
	@WaitTime		CHAR( 12 );

SELECT @Index	= 1,
	@Max		= MAX( ID ),
	@SQLCommand	= N'DBCC FREEPROCCACHE( @Plan )',
	@Parameters = N'@Plan VARBINARY( 64 )',
	@WaitTime	= '00:00:00.001'
FROM @DropTable;

/*
** Critical section, extra comments
*/
WHILE @Index <= @Max
BEGIN

	-- Get the next plan to be dropped
	SELECT	@PlanHandle = PlanHandle
	FROM	@DropTable
	WHERE	ID = @Index;

	-- Drop the plan for this loop iteration
	EXEC sp_executesql
		@SQLCommand,			-- The FreeProcCache command
		@Parameters,			-- Declare the parameter for the plan to be dropped
		@Plan = @PlanHandle;	-- Set the plan to be dropped

	-- Increment the counter to get the next plan
	SELECT @Index += 1;

	-- Don't hit the plan cache too hard, drop only 4 plans per quantum
	WAITFOR DELAY @WaitTime;

END; -- End of while loop to drop plans

/*
** Log what we've just done to the error log
*/
DECLARE @Message NVARCHAR( 500 );
SELECT @Message = CAST( ISNULL( @Max, 0 ) AS NVARCHAR )
				+ N' plans have been dropped at '
				+ CAST( GETDATE() AS NVARCHAR )
				+ N' by '
				+ SUSER_SNAME();

RAISERROR( @Message, 10, 1 ) WITH LOG;
GO