Sqlserver Query Optimization

SQL Server Query Optimization

Use this skill when you have a specific stored procedure, view, or T-SQL batch that needs to be rewritten or improved. This is about fixing the code — for hardware/index problems, start with sqlserver-diagnostics and sqlserver-indexes.

Stored Procedure Anti-Patterns

1. DELETE + INSERT Per Request (Most Common)

Problem: A stored procedure deletes and re-inserts the same row every time it runs, even when nothing changed.

-- ❌ ANTI-PATTERN
CREATE PROCEDURE usp_UpsertUserPreference
    @UserID INT, @Key NVARCHAR(50), @Value NVARCHAR(500)
AS
    DELETE FROM UserPreferences WHERE UserID = @UserID AND PreferenceKey = @Key;
    INSERT INTO UserPreferences (UserID, PreferenceKey, PreferenceValue)
    VALUES (@UserID, @Key, @Value);
GO

Problems:

• Two DML statements = two log writes, two index operations
• Triggers fire on DELETE even when nothing really changed
• Breaks foreign keys that point to this row
• Causes unnecessary index fragmentation

Fix — MERGE:

-- ✅ CORRECT — MERGE (upsert)
CREATE PROCEDURE usp_UpsertUserPreference
    @UserID INT, @Key NVARCHAR(50), @Value NVARCHAR(500)
AS
    MERGE UserPreferences AS target
    USING (VALUES (@UserID, @Key, @Value)) AS source (UserID, PreferenceKey, PreferenceValue)
        ON target.UserID = source.UserID AND target.PreferenceKey = source.PreferenceKey
    WHEN MATCHED AND target.PreferenceValue != source.PreferenceValue THEN
        UPDATE SET target.PreferenceValue = source.PreferenceValue, 
                   target.UpdatedAt = GETDATE()
    WHEN NOT MATCHED THEN
        INSERT (UserID, PreferenceKey, PreferenceValue, CreatedAt)
        VALUES (source.UserID, source.PreferenceKey, source.PreferenceValue, GETDATE());
GO

Alternative — staleness check:

-- ✅ CORRECT — Only update if value actually changed
IF EXISTS (SELECT 1 FROM UserPreferences WHERE UserID = @UserID AND PreferenceKey = @Key)
BEGIN
    UPDATE UserPreferences 
    SET PreferenceValue = @Value, UpdatedAt = GETDATE()
    WHERE UserID = @UserID AND PreferenceKey = @Key
      AND PreferenceValue != @Value;  -- Skip if no change
END
ELSE
BEGIN
    INSERT INTO UserPreferences (UserID, PreferenceKey, PreferenceValue, CreatedAt)
    VALUES (@UserID, @Key, @Value, GETDATE());
END

2. Cursor Loops (Replace with Set-Based Operations)

-- ❌ ANTI-PATTERN — processing rows one at a time
DECLARE @OrderID INT;
DECLARE cur CURSOR FOR SELECT OrderID FROM Orders WHERE Status = 'Pending';
OPEN cur;
FETCH NEXT FROM cur INTO @OrderID;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC usp_ProcessOrder @OrderID;
    FETCH NEXT FROM cur INTO @OrderID;
END
CLOSE cur; DEALLOCATE cur;

Fix — set-based:

-- ✅ CORRECT — process all rows at once
UPDATE Orders
SET ProcessedAt = GETDATE(), Status = 'Processing'
WHERE Status = 'Pending';

If usp_ProcessOrder has complex logic that can't be inlined, consider:

• Rewriting the proc to accept a table-valued parameter (TVP)
• Using a temp table as a queue and processing via a single bulk statement
• Using STRING_AGG / FOR XML PATH for string concatenation (not cursors)

3. Non-SARGable Predicates

A SARGable predicate (Search ARGument able) allows SQL Server to use an index seek. Non-SARGable predicates force scans.

-- ❌ NON-SARGable — function wraps the column, index can't be used
WHERE YEAR(OrderDate) = 2024
WHERE CONVERT(VARCHAR, OrderDate, 101) = '01/15/2024'
WHERE LEN(CustomerName) > 10
WHERE OrderTotal + 100 > 5000

-- ✅ SARGable equivalents
WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01'
WHERE OrderDate = '2024-01-15'
WHERE CustomerName LIKE 'A%'          -- LIKE with suffix wildcard is SARGable
WHERE OrderTotal > 4900

-- ❌ NON-SARGable — leading wildcard, can't use index
WHERE CustomerName LIKE '%Smith%'

-- ✅ If full-text search needed, use SQL Server Full-Text or a search table

4. Implicit Conversions

When a column and a parameter have different data types, SQL Server converts the entire column — forcing an index scan.

-- Table column: CustomerCode NVARCHAR(20)
-- ❌ Passing VARCHAR causes implicit conversion of the whole column
WHERE CustomerCode = 'ACME123'     -- VARCHAR literal, column is NVARCHAR

-- ✅ Use N'' prefix for NVARCHAR columns
WHERE CustomerCode = N'ACME123'

-- Table column: Status TINYINT
-- ❌ Passing string causes type conversion
WHERE Status = '1'

-- ✅ Pass the correct type
WHERE Status = 1

Detect implicit conversions in execution plans:

• Look for a CONVERT_IMPLICIT function in the predicate tooltip
• Or query: sys.dm_exec_query_stats + sys.dm_exec_sql_text and filter on CONVERT_IMPLICIT

5. SELECT * in Production Code

-- ❌ Never use SELECT * in stored procedures or views
SELECT * FROM Orders JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

-- ✅ Always name columns explicitly
SELECT 
    o.OrderID, o.OrderDate, o.TotalAmount, o.Status,
    c.CompanyName, c.ContactEmail
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID;

Why it matters:

• Breaks if a column is added, renamed, or reordered in the base table
• Fetches columns the application doesn't need (extra I/O, network)
• Prevents SQL Server from using covering index optimization

View Optimization

When Views Expand Inline

By default, SQL Server expands a view definition inline during query compilation. The optimizer sees the full base table query — this is usually fine.

-- This view is expanded inline — optimizer sees the base tables
CREATE VIEW vw_ActiveOrders AS
    SELECT o.*, c.CompanyName
    FROM Orders o JOIN Customers c ON o.CustomerID = c.CustomerID
    WHERE o.Status = 'Active';

Indexed Views (Materialized)

An indexed view stores the result set physically. Dramatically speeds up aggregation queries.

-- Requirements for indexed views:
-- 1. SCHEMABINDING
-- 2. UNIQUE CLUSTERED INDEX on the view
-- 3. Strict SET options (ANSI_NULLS ON, QUOTED_IDENTIFIER ON, etc.)
CREATE VIEW vw_SalesByRegion
WITH SCHEMABINDING AS
    SELECT RegionID, COUNT_BIG(*) AS OrderCount, SUM(TotalAmount) AS TotalSales
    FROM dbo.Orders
    GROUP BY RegionID;
GO

CREATE UNIQUE CLUSTERED INDEX IX_vw_SalesByRegion ON vw_SalesByRegion (RegionID);

On Enterprise Edition, SQL Server automatically uses the indexed view even when the query references the base table. On Standard Edition, use WITH (NOEXPAND) hint:

SELECT * FROM vw_SalesByRegion WITH (NOEXPAND) WHERE RegionID = 5;

CTE vs Temp Table vs Table Variable

-- CTE (single-use, readability)
WITH RankedOrders AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS rn
    FROM Orders
)
SELECT * FROM RankedOrders WHERE rn = 1;

-- Temp Table (multiple uses, large set)
SELECT OrderID, CustomerID, TotalAmount
INTO #LargeOrderSet
FROM Orders
WHERE TotalAmount > 10000;

CREATE INDEX IX_Temp_CustomerID ON #LargeOrderSet (CustomerID);  -- Can add index!

SELECT o.*, c.CompanyName FROM #LargeOrderSet o
JOIN Customers c ON o.CustomerID = c.CustomerID;

DROP TABLE #LargeOrderSet;

-- Table Variable (small set)
DECLARE @SmallSet TABLE (OrderID INT, CustomerID INT);
INSERT INTO @SmallSet SELECT TOP 10 OrderID, CustomerID FROM Orders;

Statistics Management

-- Update statistics for a specific table (full scan = accurate, slower)
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;

-- Update all statistics in the current database
EXEC sp_updatestats;

-- Check if auto-update statistics is enabled (should be ON)
SELECT name, is_auto_update_stats_on, is_auto_create_stats_on
FROM sys.databases
WHERE name = DB_NAME();

-- Check statistics for a specific table
DBCC SHOW_STATISTICS ('dbo.Orders', 'IX_Orders_CustomerID');

When to run manually:

• After a large bulk load (auto-update threshold is 20% of rows modified — can lag on large tables)
• After index creation (statistics are created automatically but may not be full scan)
• When execution plans look wrong (row estimates far off from actuals)

Query Hints — When Justified

Query hints override optimizer decisions. Use sparingly — they lock in a behavior regardless of data changes.

-- NOLOCK — dirty reads (accepts uncommitted data). Use for reporting only; NEVER for financial/transactional data
SELECT * FROM Orders WITH (NOLOCK) WHERE CustomerID = 42;

-- OPTION (RECOMPILE) — recompile this statement every execution; solves parameter sniffing
SELECT * FROM Orders WHERE CustomerID = @CustomerID OPTION (RECOMPILE);

-- OPTION (OPTIMIZE FOR UNKNOWN) — use average statistics, not sniffed parameter value
SELECT * FROM Orders WHERE CustomerID = @CustomerID OPTION (OPTIMIZE FOR (@CustomerID UNKNOWN));

-- MAXDOP — limit parallelism for this query
SELECT SUM(TotalAmount) FROM Orders OPTION (MAXDOP 2);

-- FORCE ORDER — force join order as written (rarely needed; usually means missing statistics)
SELECT * FROM Orders o JOIN Customers c ON o.CustomerID = c.CustomerID OPTION (FORCE ORDER);

Reference

• Optimization Patterns Reference — Before/after SQL examples for all major anti-patterns