29 — Query Plans

-- Show estimated plan as text (legacy, avoid)
SET SHOWPLAN_ALL ON;
GO
SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID = 1;
GO
SET SHOWPLAN_ALL OFF;
GO

-- Show estimated plan as XML (use this for automation/parsing)
SET SHOWPLAN_XML ON;
GO
SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID = 1;
GO
SET SHOWPLAN_XML OFF;
GO

-- Show actual plan as XML (query executes)
SET STATISTICS XML ON;
GO
SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID = 1;
GO
SET STATISTICS XML OFF;
GO

-- SSMS shortcuts:
-- Ctrl+L  = estimated plan (graphical)
-- Ctrl+M  = include actual plan toggle (run query to see actual)
-- Ctrl+K  = live query statistics

-- Requires SHOWPLAN permission on all referenced objects
-- Or sysadmin / db_owner membership
GRANT SHOWPLAN TO [username];

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

SELECT soh.SalesOrderID, soh.TotalDue
FROM   Sales.SalesOrderHeader soh
WHERE  soh.OrderDate >= '2014-01-01';
GO

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 12 ms.

SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 14 ms.

Table 'SalesOrderHeader'. Scan count 1, logical reads 689,
physical reads 0, page server reads 0,
read-ahead reads 0, page server read-ahead reads 0,
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

-- Convert logical reads to approximate MB
-- 689 logical reads × 8 KB = 5.5 MB scanned
SELECT 689 * 8.0 / 1024 AS MB_Scanned;

-- In SSMS: hover over any operator in the actual plan
-- "Estimated Number of Rows" vs "Actual Number of Rows"
-- 10× difference = CE problem
-- 100× difference = serious CE problem (bad statistics, parameter sniffing, etc.)

-- Detect table scans against large tables
SELECT  qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
        qs.execution_count,
        SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
            ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
              ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS query_text
FROM    sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE   qs.total_logical_reads / qs.execution_count > 10000
ORDER BY avg_logical_reads DESC;

-- SARGable: uses seek
WHERE CustomerID = 42
WHERE OrderDate >= '2023-01-01' AND OrderDate < '2024-01-01'
WHERE LastName = 'Smith'

-- NOT SARGable: forces scan
WHERE YEAR(OrderDate) = 2023          -- function wraps column
WHERE CustomerID + 0 = 42            -- expression on column
WHERE CAST(CustomerID AS VARCHAR) = '42'  -- implicit/explicit conversion
WHERE LEFT(LastName, 1) = 'S'        -- function wraps column (use LIKE 'S%' instead)

Nonclustered Index Seek → Key Lookup (nested loop)

-- Before: causes Key Lookup for TotalDue
CREATE INDEX IX_SOH_OrderDate ON Sales.SalesOrderHeader (OrderDate);

-- After: covering index
CREATE INDEX IX_SOH_OrderDate_Covering
    ON Sales.SalesOrderHeader (OrderDate)
    INCLUDE (CustomerID, TotalDue);

-- Detect Key Lookups in plan cache
SELECT  TOP 20
        qs.total_logical_reads / qs.execution_count AS avg_reads,
        qs.execution_count,
        qp.query_plan
FROM    sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE   CAST(qp.query_plan AS NVARCHAR(MAX)) LIKE '%Key Lookup%'
ORDER BY avg_reads DESC;

Nested Loops: good for OLTP, bad for large analytical joins
Hash Match:   memory grant required; spills to tempdb if insufficient
Merge Join:   "free" if inputs already sorted; pay Sort cost otherwise

-- Check for hash spills
SELECT  qs.sql_handle,
        qs.total_spills / qs.execution_count AS avg_spills,
        qs.total_rows / qs.execution_count AS avg_rows
FROM    sys.dm_exec_query_stats qs
WHERE   qs.total_spills > 0
ORDER BY avg_spills DESC;

SELECT  a.col1, b.col2
FROM    TableA AS a
INNER LOOP JOIN TableB AS b ON a.ID = b.ID;  -- force Nested Loops

INNER HASH JOIN  -- force Hash Match
INNER MERGE JOIN -- force Merge Join

-- Stream Aggregate is cheapest: ensure GROUP BY columns are leading index keys
-- Hash Aggregate: acceptable for large aggregations; watch for spills

-- Force stream aggregate by pre-sorting:
SELECT  CustomerID, SUM(TotalDue)
FROM    Sales.SalesOrderHeader
GROUP BY CustomerID
OPTION (ORDER GROUP);   -- hint to prefer stream aggregate (undocumented but effective)

-- Sort is blocking and requires memory grant
-- Signal: look for Sort with high cost % in estimated plan
-- Fix: create/modify index that produces the right order

-- Force serial execution
SELECT * FROM BigTable OPTION (MAXDOP 1);

-- Check current MAXDOP
SELECT value_in_use FROM sys.configurations WHERE name = 'max degree of parallelism';

Reading order: right-to-left, top-to-bottom (data flows left to root operator)
Each arrow thickness scales with estimated row count

-- This causes a Compute Scalar + implicit conversion warning:
SELECT * FROM Customers WHERE CustomerID = '12345';
-- CustomerID is INT; '12345' is VARCHAR → converts the column, not the param
-- Result: cannot seek, forces scan

-- Fix: use the correct type
SELECT * FROM Customers WHERE CustomerID = 12345;

-- Or: fix the stored procedure parameter type
CREATE PROC GetCustomer @ID INT   -- not VARCHAR

SELECT  TOP 20
        qs.total_logical_reads / qs.execution_count AS avg_reads,
        CAST(qp.query_plan AS NVARCHAR(MAX)) AS plan_xml,
        SUBSTRING(st.text, 1, 200) AS query_text
FROM    sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE   CAST(qp.query_plan AS NVARCHAR(MAX)) LIKE '%PlanAffectingConvert%'
   OR   CAST(qp.query_plan AS NVARCHAR(MAX)) LIKE '%CONVERT_IMPLICIT%'
ORDER BY avg_reads DESC;

-- Check estimated vs actual rows in cached plans (simplified)
-- Best done visually in SSMS actual plan

-- Force legacy CE (compat level 70) for a single query
SELECT * FROM T WHERE col = @val OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

-- Force new CE (2014+)
SELECT * FROM T WHERE col = @val OPTION (USE HINT('ENABLE_QUERY_OPTIMIZER_HOTFIXES'));

-- Enable in SSMS: Query menu → Include Live Query Statistics
-- Or: Ctrl+Shift+Q

-- Useful for:
-- Long-running queries where you want to know where time is being spent
-- Identifying whether a Sort/Hash is blocking
-- Watching row counts grow to validate estimates

-- Step 1: capture the good plan XML from cache or SSMS
DECLARE @plan_xml XML;
SELECT  @plan_xml = qp.query_plan
FROM    sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE   qs.sql_handle = <known_handle>;

-- Step 2: force it via hint (paste plan XML inline or use Query Store)
SELECT col1, col2
FROM   BigTable
WHERE  col1 = @param
OPTION (USE PLAN N'<ShowPlanXML xmlns=...> ... </ShowPlanXML>');

-- Query Store plan forcing (preferred)
-- Find query_id and plan_id in Query Store views, then:
EXEC sys.sp_query_store_force_plan @query_id = 42, @plan_id = 7;

-- Find plans for a specific query text fragment
SELECT  qs.execution_count,
        qs.total_logical_reads,
        qs.total_worker_time / 1000 AS total_cpu_ms,
        qs.total_elapsed_time / 1000 AS total_elapsed_ms,
        SUBSTRING(st.text,
            (qs.statement_start_offset/2)+1,
            ((CASE qs.statement_end_offset
              WHEN -1 THEN DATALENGTH(st.text)
              ELSE qs.statement_end_offset END
              - qs.statement_start_offset)/2)+1) AS statement_text,
        qp.query_plan
FROM    sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE   st.text LIKE '%SalesOrderHeader%'
ORDER BY qs.total_logical_reads DESC;

-- Find most expensive queries by CPU
SELECT  TOP 20
        qs.total_worker_time / qs.execution_count AS avg_cpu_us,
        qs.execution_count,
        SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 200) AS stmt,
        qp.query_plan
FROM    sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY avg_cpu_us DESC;

-- Single-use plans (not parameterized — cache bloat)
SELECT  COUNT(*) AS single_use_plan_count,
        SUM(CAST(size_in_bytes AS BIGINT)) / 1024 / 1024 AS MB_wasted
FROM    sys.dm_exec_cached_plans
WHERE   usecounts = 1
  AND   objtype = 'Adhoc';

<ShowPlanXML>
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementEstRows="..." StatementSubTreeCost="...">
          <QueryPlan>
            <Warnings>
              <PlanAffectingConvert ... />
              <SpillToTempDb SpillLevel="1" />
              <NoJoinPredicate />
            </Warnings>
            <RelOp NodeId="0" PhysicalOp="Hash Match" LogicalOp="Inner Join"
                   EstimateRows="..." EstimateCPU="..." EstimateIO="...">
              <RunTimeInformation>
                <RunTimeCountersPerThread ActualRows="..." ActualExecutions="..." />
              </RunTimeInformation>
              ...
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

-- Extract operator list from a cached plan XML
DECLARE @plan XML = (
    SELECT TOP 1 qp.query_plan
    FROM sys.dm_exec_cached_plans cp
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
    WHERE cp.objtype = 'Proc'
    -- add filter
);

SELECT  n.value('@PhysicalOp', 'VARCHAR(50)') AS PhysicalOp,
        n.value('@EstimateRows', 'FLOAT')      AS EstimateRows,
        n.value('@EstimateCPU', 'FLOAT')       AS EstimateCPU,
        n.value('@EstimateIO', 'FLOAT')        AS EstimateIO
FROM    @plan.nodes('//RelOp') AS t(n)
ORDER BY EstimateCPU + EstimateIO DESC;