EXISTS and CROSS APPLY instead of LEFT JOIN for existence checks

Stop joining whole child tables when you only need "any matching row."

2026-08-01

/*
================================================================================
Purpose:        Identifies the top 20 statements consuming the most CPU, 
                physical/logical reads, or having the longest elapsed durations.
Provides:       Execution counts, total/avg CPU, logical reads, duration, 
                query text, and associated execution plan XML.
Importance:     Pinpoints the "heavy hitters" in the workload that offer the 
                highest return on investment for query tuning and optimization.
Interpretation: Focus on queries with high "Avg_Logical_Reads" or "Avg_CPU_ms". 
                 Correlate with wait statistics to understand the wait type.
Action: Copy the query text from the top-ranked queries and analyze the execution plan in SSMS. Look for: (1) missing index warnings (green text), (2) table scans on large tables, (3) implicit conversions, (4) key lookups. For queries with high Avg_CPU_ms, create recommended indexes and re-evaluate. For large batches, consider breaking into smaller operations or optimizing the worst-performing steps.
Criticality:    High
Author:        Ravi Sharma
================================================================================
*/

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;

SELECT TOP (20)
    qs.execution_count AS [Execution_Count],
    qs.total_worker_time / 1000 AS [Total_CPU_ms],
    (qs.total_worker_time / qs.execution_count) / 1000 AS [Avg_CPU_ms],
    qs.total_logical_reads AS [Total_Logical_Reads],
    qs.total_logical_reads / qs.execution_count AS [Avg_Logical_Reads],
    qs.total_elapsed_time / 1000 AS [Total_Duration_ms],
    (qs.total_elapsed_time / qs.execution_count) / 1000 AS [Avg_Duration_ms],
    -- Extract the specific statement from the batch
    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],
    qp.query_plan AS [Query_Plan_XML],
    CAST('Displays worst performing queries from SQL Server plan cache. ' + 
         'Threshold: Queries with Avg_Logical_Reads > 10,000 or Avg_CPU_ms > 500ms are candidates for optimization. ' +
         'Recommendation: Open Query_Plan_XML to locate clustered index scans or key lookups. Add missing indexes, reduce sorting operations, or verify parameter statistics.'
         AS VARCHAR(1000)) AS [Metric_Context]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY [Total_CPU_ms] DESC; -- Can change to total_logical_reads or total_elapsed_time depending on diagnostic focus

Review the full script and confirm the connected SQL Server instance before execution. The run instructions and output guide appear later in this article.

EXISTS and CROSS APPLY instead of LEFT JOIN for existence checks

Dummy guide: LEFT JOIN ... WHERE child.id IS NOT NULL is usually an existence check in disguise.

Rewrite to EXISTS so the engine can short-circuit after the first match. Use CROSS APPLY (or OUTER APPLY) when you need columns from the "top 1 related row" without exploding the parent into many child rows.

Keep true left joins when you must project nullable child columns for every parent — that is a different problem than filtering parents that have children.

Why relational operator choice deserves a method

The central principle is that EXISTS, APPLY, and JOIN express different result shapes and should be selected from the required semantics. A single metric, recommendation, or successful command is not enough. The decision has to connect an observed symptom to a plausible cause, a controlled change, and a result that can be verified under the workload that matters.

Evidence to collect

Use evidence from the same time window whenever possible:

whether only existence is needed
whether one related row or many are returned
duplicate behavior
available predicates and indexes

A practical workflow

Write the required row shape first.
Choose the operator that states that intent.
Test NULL and duplicate cases.
Compare plans and resource use with representative data.

Common mistakes

Avoid these shortcuts:

using DISTINCT to hide join multiplication
assuming one syntax is always faster
changing semantics during a rewrite

Verification and completion

Define completion before making the change. Confirm correctness first, then compare latency, throughput, reads, CPU, waits, storage behavior, or recovery time as appropriate for EXISTS and CROSS APPLY instead of LEFT JOIN for existence checks. Test negative and boundary cases, not only the path expected to succeed. Watch the system long enough to cover the relevant workload pattern.

Download and run the diagnostic

Top resource-consuming queries

Download top_resource_queries.sql

Before you run it

Review every statement and confirm the connection points to the intended SQL Server database server.
Run through a non-production or read-only diagnostic account first when possible.
Required access: VIEW SERVER STATE.
Save the output with the server name, database name, collection time, and workload window so another engineer can reproduce the analysis.

What it does

Finds statements whose aggregate CPU, elapsed time, or reads make them worthwhile rewrite candidates. The script is diagnostic and does not apply an automatic remediation. Result values still need workload context, a second supporting signal, and a before-and-after comparison.

How to read the output values

Locate the candidate query by text and database, then calculate per-execution cost from totals and Execution_Count. After changing EXISTS, APPLY, or JOIN syntax, compare exact results, duplicate behavior, reads, CPU, duration, and the actual plan.