Inspecting the SQL Server Plan Cache

While investigating an insidious case of SQL Server query plan cache poisoning, I looked for a way to see the cached plans. Here is what I used:

WITH data AS (
  SELECT
    c.usecounts AS uses
  , s.max_worker_time AS max_time
  , CAST(
      p.query_plan.query('
        declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
        for $d in distinct-values(//ColumnReference/@Database)
        order by $d
        return data($d)
      ') AS NVARCHAR(MAX)) AS databases
  , CAST(
      p.query_plan.query('
        declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
        for $t in distinct-values(//ColumnReference/@Table)
        order by $t
        return data($t)
      ') AS NVARCHAR(MAX)) AS tables
  , c.objtype
  , t.text AS sql
  , p.query_plan
  , s.last_execution_time AS executed
  , s.execution_count AS runs
  , s.creation_time AS created
  , c.plan_handle
  , s.sql_handle
  , s.query_hash
  , s.query_plan_hash
  FROM sys.dm_exec_cached_plans  AS c
  LEFT JOIN sys.dm_exec_query_stats AS s
    ON s.plan_handle = c.plan_handle
  CROSS APPLY sys.dm_exec_sql_text(c.plan_handle) AS t
  CROSS APPLY sys.dm_exec_query_plan(c.plan_handle) AS p
)
SELECT TOP 100 *
FROM data
ORDER BY max_time DESC

Note the final two hash columns. Their presence is to help identify when different plans have been cached for a single SQL statement that has been executed with different parameter values.

Cached plans can be cleared using DBCC FREEPROCCACHE or DBCC FREESYSTEMCACHE. When benchmarking SQL, it can be useful to discard SQL Server's internal buffers. This can be done without restarting the server thus:

CHECKPOINT
GO
DBCC DROPCLEANBUFFERS

On a related note, the query hint OPTIMIZE FOR UNKNOWN can be used to essentially eliminate parameter-sniffing -- reducing non-deterministic optimizer behaviour.