SQL Server Query Performance Tuning — Execution Plans, Index Strategies, and Query Rewrites

 1. How SQL Server Executes a Query

Before you can tune a query, you need to understand what SQL Server does between the moment you press F5 and the moment rows appear. The pipeline has four main stages.

1.1 Parsing

The text of your T-SQL is tokenised and parsed into a parse tree — a hierarchical representation of the SQL syntax. At this stage SQL Server checks for syntax errors only. It does not yet know whether the tables or columns exist.

1.2 Algebrization (Binding)

The algebrizer resolves names against the system catalog — it checks that dbo.Orders exists, that CustomerID is a valid column, that data types are compatible, and that the calling user has permission. The output is an algebrized tree (also called a query tree or logical tree).

1.3 Query Optimisation

This is where performance is won or lost. The Cost-Based Optimizer (CBO) explores a search space of possible physical plans — different join orders, join algorithms (Nested Loops, Hash Match, Merge Join), index choices, and parallelism options. For each candidate it estimates a cost in abstract units, heavily influenced by statistics (histograms of column value distributions). The optimizer picks the plan with the lowest estimated cost, not necessarily the plan that will run fastest in wall-clock time.

1.4 Execution

The chosen plan is compiled to machine code (or retrieved from the plan cache if an identical plan was compiled before). The Execution Engine drives the plan: each operator pulls rows from its child operators in a demand-driven (pull) model called the Volcano Iterator Model. Pages are read from the Buffer Pool (in-memory cache); if a page is not in memory, a physical I/O from disk is required.

1.5 Logical vs Physical Operators

The optimizer works in two layers. A logical operator describes what to do — Inner Join, Filter, Aggregate. A physical operator describes how to do it — Nested Loops Join, Hash Match Aggregate, Clustered Index Seek. The plan you see in SSMS shows physical operators.

-- Capture an ACTUAL execution plan for a query
-- Option A: turn on SET STATISTICS IO and TIME
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT o.OrderID, o.OrderDate, c.CompanyName
FROM   dbo.Orders     o
JOIN   dbo.Customers  c ON c.CustomerID = o.CustomerID
WHERE  o.OrderDate >= '2024-01-01';

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
-- Output in Messages tab shows logical reads and CPU/elapsed ms

-- Option B: capture the XML plan for later analysis
SET STATISTICS XML ON;
SELECT o.OrderID, o.OrderDate FROM dbo.Orders WHERE OrderDate >= '2024-01-01';
SET STATISTICS XML OFF;

-- Option C: use sys.dm_exec_query_stats (post-execution, plan in cache)
SELECT TOP 20
    qs.total_elapsed_time  / qs.execution_count AS avg_elapsed_us,
    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,
    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_elapsed_us DESC;

 2. Reading Execution Plans in SSMS

In SSMS, press Ctrl+M before running a query to enable the Actual Execution Plan tab. The graphical plan reads right-to-left, top-to-bottom: data flows from leaf operators (table/index access) on the right toward the SELECT on the left. Arrow width indicates relative row count — fat arrows mean many rows, thin arrows mean few.

2.1 Key Operators to Recognise

2.2 Cost Percentages

Each operator shows a cost percentage relative to the whole query batch. High-percentage operators are your tuning targets. But note: cost percentages are estimates — they reflect the optimizer's model, not real wall-clock time. Always cross-check with SET STATISTICS TIME ON to see actual milliseconds.

2.3 Tooltip Warnings — What to Look For

Hover over any operator to see its tooltip. Watch for these warnings — they appear as exclamation marks on the operator icon:

-- Enable actual execution plan capture via T-SQL (useful in automated scripts)
-- Include actual execution plan XML in the result set
SELECT *
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE qp.query_plan LIKE '%TableScan%'   -- find plans containing Table Scans
ORDER BY cp.usecounts DESC;

-- Find queries with implicit conversions in the plan cache
SELECT  TOP 20
        SUBSTRING(st.text,1,200)     AS query_snippet,
        qp.query_plan
FROM    sys.dm_exec_cached_plans          cp
CROSS APPLY sys.dm_exec_sql_text(cp.sql_handle)   st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE   CAST(qp.query_plan AS NVARCHAR(MAX)) LIKE '%PlanAffectingConvert%'
ORDER   BY cp.usecounts DESC;

 3. The Five Most Damaging Query Patterns

These five patterns account for the majority of performance problems seen in the field. Each example shows the problematic query, explains why it is slow, and gives a corrected rewrite.

Pattern 1 — Implicit Type Conversion

SQL Server must compare values of the same type. When types differ, it silently converts one to the other. If the column is the thing being converted, SQL Server cannot use the index on that column — it has to convert every row before comparing.

-- ❌ BAD: CustomerCode is VARCHAR(20), but the parameter is NVARCHAR (Unicode literal N'')
-- SQL Server converts every row in the table to NVARCHAR before comparing → Table Scan
SELECT OrderID, OrderDate, Total
FROM   dbo.Orders
WHERE  CustomerCode = N'ALFKI';   -- N prefix → NVARCHAR literal

-- ✅ GOOD: Match the literal type to the column type
SELECT OrderID, OrderDate, Total
FROM   dbo.Orders
WHERE  CustomerCode = 'ALFKI';   -- no N prefix → VARCHAR literal → Index Seek

-- ❌ BAD: Application sends NVARCHAR parameter to a VARCHAR column (common in .NET)
-- Fix in application code: use SqlDbType.VarChar (not NVarChar) for the parameter
-- SqlParameter p = new SqlParameter("@code", SqlDbType.VarChar, 20);

-- How to detect implicit conversions in the cache:
SELECT  SUBSTRING(st.text,1,300) AS sql_text,
        qs.total_logical_reads,
        qs.execution_count
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 '%CONVERT_IMPLICIT%'
ORDER   BY qs.total_logical_reads DESC;

Pattern 2 — Functions on Indexed Columns in WHERE

When you wrap a column in a function inside a WHERE clause, SQL Server cannot use an index seek on that column — it has to evaluate the function for every row. This is the non-SARGable pattern (SARG = Search ARGument).

-- ❌ BAD: YEAR() applied to an indexed column → Index Scan / Table Scan
SELECT OrderID, OrderDate, CustomerID
FROM   dbo.Orders
WHERE  YEAR(OrderDate) = 2024;

-- ✅ GOOD: Range predicate on the raw column → Index Seek
SELECT OrderID, OrderDate, CustomerID
FROM   dbo.Orders
WHERE  OrderDate >= '2024-01-01'
  AND  OrderDate <  '2025-01-01';

-- ❌ BAD: DATEADD on a column
SELECT * FROM dbo.Sessions
WHERE  DATEADD(HOUR, 8, StartTime) > GETDATE();

-- ✅ GOOD: Move the function to the constant side
SELECT * FROM dbo.Sessions
WHERE  StartTime > DATEADD(HOUR, -8, GETDATE());

-- ❌ BAD: LEFT() on a column
SELECT * FROM dbo.Products
WHERE  LEFT(ProductCode, 3) = 'ABC';

-- ✅ GOOD: Use a range or LIKE with a leading wildcard-free pattern
SELECT * FROM dbo.Products
WHERE  ProductCode LIKE 'ABC%';

-- ❌ BAD: LOWER/UPPER on column — prevents seek even with case-insensitive collation
SELECT * FROM dbo.Users
WHERE  LOWER(Username) = 'admin';

-- ✅ GOOD: On CI (case-insensitive) collation, compare directly
SELECT * FROM dbo.Users
WHERE  Username = 'admin';   -- CI collation: case is ignored automatically

Pattern 3 — SELECT * with Key Lookups

When you request columns not covered by a nonclustered index, SQL Server performs a Key Lookup — one random I/O per qualifying row to fetch the missing columns from the clustered index. For a query returning 50,000 rows, that is 50,000 individual page lookups.

-- ❌ BAD: SELECT * forces a Key Lookup for every row found by the NC index
SELECT *
FROM   dbo.Orders
WHERE  CustomerID = 42;
-- Plan: NC Index Seek (CustomerID) → Key Lookup → Nested Loops

-- ✅ GOOD: Select only the columns you need (eliminates Key Lookup if index covers them)
SELECT OrderID, OrderDate, Total, Status
FROM   dbo.Orders
WHERE  CustomerID = 42;

-- Even better: add a covering index that includes those columns
CREATE NONCLUSTERED INDEX IX_Orders_Customer_Cover
ON dbo.Orders (CustomerID)
INCLUDE (OrderDate, Total, Status);
-- Plan: NC Index Seek only — no Key Lookup

-- Check which queries are triggering the most Key Lookups:
SELECT  TOP 20
        SUBSTRING(st.text,1,200)  AS sql_text,
        qs.total_logical_reads,
        qs.execution_count,
        qs.total_elapsed_time / qs.execution_count AS avg_elapsed_us
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 '%Lookup%'
ORDER   BY qs.total_logical_reads DESC;

Pattern 4 — Leading Wildcard LIKE

A LIKE pattern that starts with % cannot use a B-tree index seek because SQL Server does not know which part of the sorted index to navigate to. It must scan the entire index.

-- ❌ BAD: Leading wildcard → Index Scan
SELECT ProductID, ProductName
FROM   dbo.Products
WHERE  ProductName LIKE '%bicycle%';

-- ✅ OPTION A: Trailing wildcard → Index Seek (if you can live with prefix matching)
SELECT ProductID, ProductName
FROM   dbo.Products
WHERE  ProductName LIKE 'bicycle%';

-- ✅ OPTION B: Full-Text Search — for genuine keyword search across text columns
-- Step 1: enable full-text indexing
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
CREATE FULLTEXT INDEX ON dbo.Products(ProductName)
    KEY INDEX PK_Products;

-- Step 2: query with CONTAINS or FREETEXT
SELECT ProductID, ProductName
FROM   dbo.Products
WHERE  CONTAINS(ProductName, '"bicycle"');
-- FREETEXT does stemming: 'bicycles', 'biking' etc.
SELECT ProductID, ProductName
FROM   dbo.Products
WHERE  FREETEXT(ProductName, 'bicycle');

-- ✅ OPTION C: Persisted computed column for suffix search
-- Store the reversed string in a persisted computed column + index it
ALTER TABLE dbo.Products
    ADD ProductNameRev AS REVERSE(ProductName) PERSISTED;
CREATE INDEX IX_Products_NameRev ON dbo.Products(ProductNameRev);

-- Then query using the reversed pattern:
SELECT ProductID, ProductName
FROM   dbo.Products
WHERE  ProductNameRev LIKE REVERSE('%bicycle');  -- becomes 'elcycib%' → seek

Pattern 5 — Scalar UDF in the SELECT Clause

A scalar user-defined function called in SELECT is executed once per row. SQL Server cannot inline the logic into the query — it calls the function 100,000 times for a 100,000-row result set. This is one of the single biggest hidden performance killers in legacy SQL Server codebases. SQL Server 2019+ introduced Scalar UDF Inlining for eligible functions, but many UDFs still do not qualify.

-- ❌ BAD: Scalar UDF called once per row
CREATE FUNCTION dbo.GetCustomerTier (@CustomerID INT)
RETURNS VARCHAR(20)
AS
BEGIN
    DECLARE @Total DECIMAL(18,2);
    SELECT @Total = SUM(Total)
    FROM   dbo.Orders
    WHERE  CustomerID = @CustomerID;

    RETURN CASE
        WHEN @Total > 10000 THEN 'Gold'
        WHEN @Total > 1000  THEN 'Silver'
        ELSE 'Bronze'
    END;
END;
GO

-- This executes the UDF once per customer row → row-by-row execution
SELECT CustomerID, CompanyName, dbo.GetCustomerTier(CustomerID) AS Tier
FROM   dbo.Customers;

-- ✅ GOOD: Rewrite as a single set-based query with a subquery or CTE
WITH CustomerTotals AS (
    SELECT CustomerID,
           SUM(Total) AS TotalSpend
    FROM   dbo.Orders
    GROUP  BY CustomerID
)
SELECT  c.CustomerID,
        c.CompanyName,
        CASE
            WHEN ct.TotalSpend > 10000 THEN 'Gold'
            WHEN ct.TotalSpend > 1000  THEN 'Silver'
            ELSE 'Bronze'
        END AS Tier
FROM    dbo.Customers   c
LEFT JOIN CustomerTotals ct ON ct.CustomerID = c.CustomerID;

-- ✅ GOOD for SQL Server 2019+: check if UDF inlining is active
SELECT  name,
        is_inlineable,
        is_inline_enabled
FROM    sys.sql_modules m
JOIN    sys.objects     o ON o.object_id = m.object_id
WHERE   o.name = 'GetCustomerTier';
-- If is_inlineable = 1 and is_inline_enabled = 1, SQL Server 2019 will inline it automatically.

 4. Index Strategy

Indexes are the primary lever for query performance. The challenge is that every index you add speeds up reads but slows down writes (INSERT / UPDATE / DELETE must maintain each index). Good index strategy balances coverage against maintenance cost.

4.1 Clustered vs Nonclustered

A table has exactly one clustered index — the physical sort order of rows on disk. All other indexes are nonclustered — separate B-tree structures that store the key columns plus a pointer (the clustered key) back to the data row.

4.2 Covering Indexes and INCLUDE

A covering index includes all columns a query needs — eliminating the Key Lookup. Columns in the WHERE / JOIN ON clause go in the index key (they affect sort order and enable seeks). Columns only needed in the SELECT list or ORDER BY go in the INCLUDE clause (they are stored in the leaf pages but do not affect the B-tree sort order).

-- Query to cover:
SELECT OrderID, OrderDate, Total, Status
FROM   dbo.Orders
WHERE  CustomerID = @cid
  AND  OrderDate >= @from
ORDER  BY OrderDate;

-- ✅ Covering index: CustomerID + OrderDate in key (seek + sort), Total + Status in INCLUDE
CREATE NONCLUSTERED INDEX IX_Orders_Cust_Date_Cover
ON dbo.Orders (CustomerID, OrderDate)
INCLUDE (Total, Status);

-- ✅ Covering index for a report query with multiple filters:
CREATE NONCLUSTERED INDEX IX_Orders_Status_Date
ON dbo.Orders (Status, OrderDate DESC)
INCLUDE (CustomerID, Total, ShipCountry)
WHERE Status IN ('Pending', 'Processing');  -- filtered index (see 4.3)

4.3 Filtered Indexes

A filtered index covers only a subset of rows defined by a WHERE clause. For tables where most queries target a specific partition of data (e.g., active records, recent orders, open tickets), a filtered index is much smaller and faster than a full index.

-- Only index active customers (vast majority of queries target active records)
CREATE NONCLUSTERED INDEX IX_Customers_Active
ON dbo.Customers (LastName, FirstName)
INCLUDE (Email, Phone)
WHERE  IsActive = 1;

-- Only index unprocessed queue items
CREATE NONCLUSTERED INDEX IX_Queue_Unprocessed
ON dbo.JobQueue (Priority DESC, CreatedAt ASC)
INCLUDE (JobType, Payload)
WHERE  ProcessedAt IS NULL;

-- Note: filtered index is only used if the query predicate matches the filter
-- This query WILL use the index:
SELECT * FROM dbo.Customers WHERE LastName = 'Smith' AND IsActive = 1;
-- This query will NOT use the filtered index (IsActive not specified):
SELECT * FROM dbo.Customers WHERE LastName = 'Smith';

4.4 Index Maintenance — Rebuild vs Reorganize

As rows are inserted, updated, and deleted, index pages become fragmented. Fragmentation increases I/O because pages are no longer in logical order on disk.

-- Check fragmentation level for all indexes in a database
SELECT  OBJECT_NAME(ips.object_id)          AS table_name,
        i.name                               AS index_name,
        ips.index_type_desc,
        ROUND(ips.avg_fragmentation_in_percent, 1) AS frag_pct,
        ips.page_count
FROM    sys.dm_db_index_physical_stats(
            DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN    sys.indexes i
        ON i.object_id = ips.object_id
        AND i.index_id  = ips.index_id
WHERE   ips.page_count > 100           -- ignore tiny indexes
ORDER   BY frag_pct DESC;

-- REORGANIZE: online operation, low I/O impact, good for 10–30% fragmentation
ALTER INDEX IX_Orders_Cust_Date_Cover ON dbo.Orders REORGANIZE;

-- REBUILD: more thorough, updates statistics, can be done ONLINE (Enterprise)
-- Use for fragmentation > 30%
ALTER INDEX IX_Orders_Cust_Date_Cover ON dbo.Orders
    REBUILD WITH (ONLINE = ON, FILLFACTOR = 80);

-- Rebuild ALL indexes on a table (use with caution on large tables in production)
ALTER INDEX ALL ON dbo.Orders REBUILD;

-- ✅ Best practice: use Ola Hallengren's IndexOptimize instead of manual scripts
-- https://ola.hallengren.com — free, handles thresholds, online/offline, logging

4.5 Finding Unused and Duplicate Indexes

-- Unused indexes (no seeks, scans, or lookups since last SQL Server restart)
SELECT  OBJECT_NAME(i.object_id)    AS table_name,
        i.name                       AS index_name,
        i.type_desc,
        us.user_seeks,
        us.user_scans,
        us.user_lookups,
        us.user_updates              -- writes that maintained this index (wasted work)
FROM    sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats us
        ON us.object_id  = i.object_id
        AND us.index_id  = i.index_id
        AND us.database_id = DB_ID()
WHERE   i.type_desc <> 'HEAP'
  AND   i.is_primary_key = 0
  AND   i.is_unique = 0
  AND   (us.user_seeks  = 0 OR us.user_seeks IS NULL)
  AND   (us.user_scans  = 0 OR us.user_scans IS NULL)
  AND   (us.user_lookups = 0 OR us.user_lookups IS NULL)
ORDER   BY us.user_updates DESC;    -- indexes costing the most in write overhead

 5. Statistics and Cardinality Estimation

Statistics are the optimizer's crystal ball. SQL Server maintains a histogram for the leading column of each index and for columns referenced in WHERE clauses. The histogram records up to 200 step boundaries — for each step, SQL Server knows the number of equal rows, range rows, and average row count. When statistics are stale, the optimizer makes bad decisions: choosing the wrong join type, allocating the wrong memory grant, picking a scan when a seek would be faster.

-- View statistics for a specific index
DBCC SHOW_STATISTICS('dbo.Orders', 'IX_Orders_Cust_Date_Cover');
-- Output: header (rows, rows sampled, date), density vector, histogram

-- Check when statistics were last updated across the whole database
SELECT  OBJECT_NAME(s.object_id)    AS table_name,
        s.name                       AS stats_name,
        sp.last_updated,
        sp.rows,
        sp.rows_sampled,
        ROUND(100.0 * sp.rows_sampled / NULLIF(sp.rows,0), 1) AS pct_sampled,
        sp.modification_counter      AS rows_modified_since_last_update
FROM    sys.stats              s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE   OBJECT_NAME(s.object_id) = 'Orders'
ORDER   BY sp.modification_counter DESC;

-- Manually update statistics on a single table (FULLSCAN = 100% sample, most accurate)
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;

-- Update ALL statistics in the current database
EXEC sp_updatestats;    -- smart: only updates stats with changes since last update

-- Update with FULLSCAN for critical tables (more accurate but more I/O)
UPDATE STATISTICS dbo.Orders WITH FULLSCAN, ALL;

-- Auto-update statistics threshold (SQL Server default = 20% + 500 rows changed)
-- For large tables, 20% is a very high bar — stats can be stale for months
-- Enable trace flag 2371 (SQL 2014 and earlier) or use COMPAT_LEVEL 130+ for dynamic threshold
ALTER DATABASE CURRENT
SET AUTO_UPDATE_STATISTICS_ASYNC ON;   -- async: stats update in background, no query blocking

5.1 Filtered Statistics

For tables with highly skewed distributions (e.g., most orders are 'Completed', very few are 'Disputed'), the overall histogram may be misleading for minority predicates. Filtered statistics give the optimizer accurate estimates for specific value ranges.

-- Create filtered statistics for a skewed column
CREATE STATISTICS stat_Orders_Disputed
ON dbo.Orders (Status, OrderDate)
WHERE Status = 'Disputed'
WITH FULLSCAN;

-- The optimizer uses these filtered stats automatically when the query predicate matches

5.2 Cardinality Estimator Versions

 6. Query Store — Flight Data Recorder for SQL Server

Introduced in SQL Server 2016, Query Store persists query text, plans, and runtime statistics to the database itself (not just in-memory like the plan cache). It survives server restarts and is the single best tool for detecting plan regressions and forcing stable plans without schema or code changes.

6.1 Enable Query Store

-- Enable Query Store on a user database
ALTER DATABASE YourDatabaseName
SET QUERY_STORE = ON
(
    OPERATION_MODE  = READ_WRITE,
    CLEANUP_POLICY  = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS   = 900,    -- flush to disk every 15 min
    INTERVAL_LENGTH_MINUTES       = 60,     -- aggregate stats per 1-hour bucket
    MAX_STORAGE_SIZE_MB           = 1024,   -- 1 GB for Query Store data
    QUERY_CAPTURE_MODE            = AUTO,   -- only track significant queries
    SIZE_BASED_CLEANUP_MODE       = AUTO
);

-- Check Query Store configuration and current status
SELECT  actual_state_desc,
        desired_state_desc,
        current_storage_size_mb,
        max_storage_size_mb,
        query_capture_mode_desc,
        size_based_cleanup_mode_desc
FROM    sys.database_query_store_options;

-- Flush pending data to disk immediately (useful before analysis)
EXEC sys.sp_query_store_flush_db;

6.2 Find Top Resource Consumers via T-SQL

-- Top 20 queries by average CPU (last 24 hours)
SELECT TOP 20
    q.query_id,
    SUBSTRING(qt.query_sql_text, 1, 200)             AS sql_text,
    rs.avg_cpu_time                                   AS avg_cpu_us,
    rs.avg_duration                                   AS avg_duration_us,
    rs.avg_logical_io_reads,
    rs.count_executions,
    p.plan_id,
    TRY_CAST(p.query_plan AS XML)                     AS query_plan
FROM    sys.query_store_query           q
JOIN    sys.query_store_query_text      qt ON qt.query_text_id = q.query_text_id
JOIN    sys.query_store_plan            p  ON p.query_id        = q.query_id
JOIN    sys.query_store_runtime_stats   rs ON rs.plan_id        = p.plan_id
JOIN    sys.query_store_runtime_stats_interval rsi
        ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE   rsi.start_time >= DATEADD(HOUR, -24, GETUTCDATE())
ORDER   BY rs.avg_cpu_time DESC;

-- Detect plan regressions: same query_id, multiple plan_ids, second plan is slower
WITH PlanStats AS (
    SELECT  q.query_id,
            p.plan_id,
            rs.avg_duration,
            ROW_NUMBER() OVER (PARTITION BY q.query_id ORDER BY p.plan_id) AS plan_seq
    FROM    sys.query_store_query         q
    JOIN    sys.query_store_plan          p  ON p.query_id = q.query_id
    JOIN    sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
)
SELECT  a.query_id,
        a.plan_id           AS old_plan,
        a.avg_duration      AS old_avg_us,
        b.plan_id           AS new_plan,
        b.avg_duration      AS new_avg_us,
        ROUND(100.0 * (b.avg_duration - a.avg_duration) / NULLIF(a.avg_duration,0), 1) AS pct_regression
FROM    PlanStats a
JOIN    PlanStats b ON b.query_id = a.query_id AND b.plan_seq = a.plan_seq + 1
WHERE   b.avg_duration > a.avg_duration * 1.5   -- 50%+ slower
ORDER   BY pct_regression DESC;

6.3 Force and Unforce Plans

-- Force the optimizer to always use a specific good plan
-- (query_id and plan_id from the queries above)
EXEC sys.sp_query_store_force_plan
    @query_id = 142,
    @plan_id  = 87;

-- Unforce a plan (let the optimizer choose freely again)
EXEC sys.sp_query_store_unforce_plan
    @query_id = 142,
    @plan_id  = 87;

-- Remove a specific query from Query Store (e.g. one-off ad hoc query polluting the store)
EXEC sys.sp_query_store_remove_query @query_id = 999;

-- Reset runtime statistics for a plan (useful after schema or data changes)
EXEC sys.sp_query_store_reset_exec_stats @plan_id = 87;

-- SQL Server 2022: attach a hint via Query Store (no code change required)
EXEC sys.sp_query_store_set_hints
    @query_id   = 142,
    @query_hints = N'OPTION(MAXDOP 1, RECOMPILE)';

 7. Ten Quick Wins — Apply Today

These are configuration and code-level changes that can be applied with minimal risk and often produce immediate, measurable improvements.

Quick Win 1 — Enable RCSI (Read Committed Snapshot Isolation)

-- Eliminates reader-writer blocking by using row versions in tempdb
-- Check current isolation level
SELECT name, is_read_committed_snapshot_on FROM sys.databases WHERE name = DB_NAME();

-- Enable RCSI (requires brief exclusive access — run during low-traffic window)
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE YourDatabase SET MULTI_USER;
-- No application code changes required — READ COMMITTED behaviour is unchanged from app perspective

Quick Win 2 — Set MAXDOP Correctly

-- MAXDOP = Max Degree Of Parallelism
-- Recommended: number of physical cores per NUMA node, capped at 8
-- Too high → excessive thread thrashing; too low → underutilises hardware

-- Check current value:
EXEC sp_configure 'max degree of parallelism';

-- Set to 4 (adjust to your core count)
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'max degree of parallelism', 4; RECONFIGURE;

-- Also set MAXDOP per database (SQL Server 2019+):
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;

Quick Win 3 — Raise Cost Threshold for Parallelism

-- Default = 5 (absurdly low — almost every query goes parallel)
-- Recommended starting point: 50 (tune up from there based on workload)
EXEC sp_configure 'cost threshold for parallelism', 50; RECONFIGURE;
-- This prevents trivial queries from spawning parallel plans unnecessarily

Quick Win 4 — Use SET NOCOUNT ON in Stored Procedures

-- Every DML statement sends a "n rows affected" message to the client
-- In stored procedures, this generates unnecessary network round-trips
CREATE OR ALTER PROCEDURE dbo.UpdateOrderStatus
    @OrderID INT,
    @Status  VARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;    -- suppress "1 rows affected" messages

    UPDATE dbo.Orders
    SET    Status = @Status,
           ModifiedAt = GETUTCDATE()
    WHERE  OrderID = @OrderID;
END;

Quick Win 5 — Avoid SELECT * in Application Queries

-- ❌ BAD: fetches all columns, prevents index coverage, adds network payload
SELECT * FROM dbo.Products WHERE CategoryID = 5;

-- ✅ GOOD: fetch only what the application actually needs
SELECT ProductID, ProductName, Price, StockQty
FROM   dbo.Products
WHERE  CategoryID = 5;

Quick Win 6 — Update Statistics on Critical Tables

-- If you have tables receiving millions of rows per day, the auto-update threshold
-- (20% of rows changed) may mean statistics are perpetually stale.
-- Add a nightly job to update stats on high-churn tables:
UPDATE STATISTICS dbo.Orders        WITH FULLSCAN;
UPDATE STATISTICS dbo.OrderItems    WITH FULLSCAN;
UPDATE STATISTICS dbo.Inventory     WITH FULLSCAN;

-- Or use sp_updatestats for the whole database (uses sample rate, faster):
EXEC sp_updatestats;

Quick Win 7 — Avoid Re-Compiling Hot Queries with OPTION (OPTIMIZE FOR UNKNOWN)

-- Parameter sniffing: SQL Server compiles a plan for the first parameter value seen.
-- If that value is atypical (e.g. a bulk user with 10,000 orders vs a typical user with 5),
-- the plan may be wrong for everyone else.

-- Option A: OPTIMIZE FOR UNKNOWN — ignores sniffed value, uses average density
SELECT * FROM dbo.Orders WHERE CustomerID = @cid
OPTION (OPTIMIZE FOR (@cid UNKNOWN));

-- Option B: RECOMPILE hint — recompiles on every execution (correct plan, higher CPU)
SELECT * FROM dbo.Orders WHERE CustomerID = @cid
OPTION (RECOMPILE);

-- Option C: Query Store hint (SQL 2022, no code change)
EXEC sys.sp_query_store_set_hints @query_id = 77,
    @query_hints = N'OPTION(OPTIMIZE FOR UNKNOWN)';

Quick Win 8 — Fix Implicit Conversions at the Application Layer

-- In .NET (ADO.NET): always specify SqlDbType explicitly
-- BAD:  cmd.Parameters.AddWithValue("@code", "ALFKI");  -- infers NVARCHAR
-- GOOD: cmd.Parameters.Add("@code", SqlDbType.VarChar, 20).Value = "ALFKI";

-- In Entity Framework: configure column type in OnModelCreating
-- modelBuilder.Entity().Property(c => c.CustomerCode)
//     .HasColumnType("varchar(20)");

-- Identify implicit conversions across the whole instance:
SELECT  DB_NAME(qp.dbid)                             AS db_name,
        SUBSTRING(st.text, 1, 200)                   AS sql_text,
        qs.total_logical_reads / qs.execution_count  AS avg_reads
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 '%CONVERT_IMPLICIT%'
ORDER   BY avg_reads DESC;

Quick Win 9 — Drop or Disable Unused Indexes

-- Indexes with zero reads but many writes are pure overhead.
-- Use the unused index query from Section 4.5, review carefully, then:

-- DISABLE first (reversible) to validate nothing breaks
ALTER INDEX IX_Orders_OldRedundant ON dbo.Orders DISABLE;

-- After validating for a week, DROP permanently
DROP INDEX IX_Orders_OldRedundant ON dbo.Orders;

-- Note: never disable a PRIMARY KEY or UNIQUE constraint index via ALTER INDEX DISABLE
-- Use ALTER TABLE DROP CONSTRAINT instead (and only if you are certain)

Quick Win 10 — Enable Query Store and Set Up Baseline

-- See Section 6 for full Query Store setup.
-- Minimum viable setup on every user database:
ALTER DATABASE YourDatabase
SET QUERY_STORE = ON
(OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO,
 MAX_STORAGE_SIZE_MB = 512, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 14));

-- Once enabled, the SSMS "Top Resource Consuming Queries" report is available in:
-- Object Explorer → [Database] → Query Store → Top Resource Consuming Queries

 8. A Practical Tuning Workflow

When a query is slow, follow this diagnostic sequence rather than guessing:

1. Measure first. Capture SET STATISTICS IO, TIME ON output. Note logical reads and elapsed ms.
2. Capture the actual execution plan (Ctrl+M in SSMS). Read right to left. Find the highest-cost operator.
3. Check for warnings — implicit conversions, missing index suggestions, spill warnings on Sort/Hash Match.
4. Compare estimated vs actual row counts — large discrepancies mean stale statistics. Run UPDATE STATISTICS and re-test.
5. Look for Table Scans or Index Scans on large tables. Is the predicate non-SARGable? Is there a missing index?
6. Look for Key Lookups. Add an INCLUDE to the existing NC index to cover the missing columns.
7. Check for implicit type conversions in the plan XML (CONVERT_IMPLICIT). Fix the parameter type.
8. Apply the rewrite or index change in a dev environment. Measure again. Confirm improvement.
9. Use Query Store to monitor in production and force a good plan if needed.

-- Complete diagnostic script: capture IO, time, and plan for a suspect query
SET NOCOUNT ON;
SET STATISTICS IO   ON;
SET STATISTICS TIME ON;

-- Paste your suspect query here:
SELECT  o.OrderID,
        o.OrderDate,
        c.CompanyName,
        SUM(oi.Quantity * oi.UnitPrice) AS OrderTotal
FROM    dbo.Orders      o
JOIN    dbo.Customers   c  ON c.CustomerID = o.CustomerID
JOIN    dbo.OrderItems  oi ON oi.OrderID   = o.OrderID
WHERE   o.OrderDate >= '2024-01-01'
  AND   o.Status     = 'Completed'
GROUP   BY o.OrderID, o.OrderDate, c.CompanyName
ORDER   BY OrderTotal DESC;

SET STATISTICS IO   OFF;
SET STATISTICS TIME OFF;
-- Check Messages tab for: Table 'Orders'. Scan count 1, logical reads 8234
-- Check Messages tab for: CPU time = 312 ms,  elapsed time = 445 ms.