Optimizer Timeouts with XQuery
I was looking at performance of a database and I noticed a few of the plans were very large and timing out in the optimizer. This made me wonder, just how many of them were timing out? This sounds like a job for XQuery! There’s really nothing to it. Once you start plucking stuff out of the execution plans using XQuery, it’s kind of hard to stop. So here’s my little bit of code. WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'), QueryPlans AS ( SELECT RelOp.pln.value(N'@StatementOptmEarlyAbortReason', N'varchar(50)') AS TerminationReason,        RelOp.pln.value(N'@StatementOptmLevel', N'varchar(50)') AS OptimizationLevel,        --dest.text,        SUBSTRING(dest.text, (deqs.statement_start_offset / 2) + 1,                  (deqs.statement_end_offset - deqs.statement_start_offset)                  / 2 + 1) AS StatementText,        deqp.query_plan,        deqp.dbid,        deqs.execution_count,        deqs.total_elapsed_time,        deqs.total_logical_reads,        deqs.total_logical_writes FROM   sys.dm_exec_query_stats AS deqs        CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle)…