Viewing the Plan Cache

I’m working on a script and wanted to know how SQL was caching the query plan. So I wrote this little snippet. The only real value I’m adding here is getting the XQuery correct to pull out the SQL itself.

DBCC FREEPROCCACHE;
-- Run your query here
SELECT
        qp.query_plan,
        qp.query_plan.value(N'(//@StatementText)[1]', N'NVARCHAR(MAX)')
    FROM sys.dm_exec_cached_plans AS cp
        CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp;

Of course, I don’t recommend that DBCC in a production environment. But in a dev environment, it makes things easy.