Did you know that with SQL Server 2008 you can now clear the query plan for a specific statement you are testing? This is a wonderful addition for SQL Server 2008 as you do not have to drop everything in cache anymore. You can just drop the query you are working on. Look at the following code below from Books Online (BOL) and try it out yourself to see how it works:
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. 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. 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 AdventureWorks;
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
