Jun

SQL 2008: Querying query plans

Say that 5 times fast…
Sometimes, you have checked all the tables and views and you cannot find the information you are looking for. You know someone ran a command that messed things up, you don’t want to place blame, you just want to know WHEN it happened. Your last resort is searching the cached query plans. You are hoping this was an ad-hoc plan. Here is a quick way to find a value in the query plans:

SELECT top 10 *,
p.query_plan.value('
 declare namespace SHPL="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
   (/SHPL:ShowPlanXML/SHPL:BatchSequence/SHPL:Batch/SHPL:Statements/SHPL:StmtSimple/@StatementText)[1]
','varchar(max)') as StatementText
FROM sys.dm_exec_query_stats s
   CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) AS p
WHERE
p.query_plan.value('
 declare namespace SHPL="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
   (/SHPL:ShowPlanXML/SHPL:BatchSequence/SHPL:Batch/SHPL:Statements/SHPL:StmtSimple/@StatementText)[1]
','varchar(max)')
like '%CREATE TABLE%'
ORDER BY s.creation_time DESC