SQL Server Query Plan Caching

What is in the SQL Server Query Plan Cache ?

SQL 2000

SELECT * FROM syscacheobjects 

SQL 2005+

SELECT * FROM SYS.DM_EXEC_CACHED_PLANS 

Summing it up (SQL 2005+)

SELECT
OBJTYPE AS Object_Type,
COUNT(*) as Plan_Count,
SUM(CAST(SIZE_IN_BYTES AS BIGINT))/1024/1024 AS Size_MB,
AVG(usecounts) as Average_Use_Count
FROM
SYS.DM_EXEC_CACHED_PLANS
GROUP BY
OBJTYPE

How do I clear those caches?
(CAREFUL! Don't use on production servers!)

-- drop cached data
DBCC DROPCLEANBUFFERS

-- clear procedure cache
DBCC FREEPROCCACHE   

-- only clear plans for adhoc sql
DBCC FREESYSTEMCACHE('SQL Plans')

Brilliant article on plans here >

http://sqlblogcasts.com/blogs/maciej/archive/2008/09/21/clearing-your-ad-hoc-sql-plans-while-keeping-your-sp-plans-intact.aspx

also - SQL Handle and Plan handle explained