Bits of .NET

Created an index but your query still takes 30 seconds? SQL Server might be ignoring it due to implicit conversions, functions, or wildcard positioning.

Index Killer #1 – Functions on Indexed Columns:

-- Index exists on CreatedDate
CREATE INDEX IX_Orders_CreatedDate ON Orders(CreatedDate);

-- ❌ BAD: Index ignored, full table scan
SELECT * FROM Orders 
WHERE YEAR(CreatedDate) = 2024;

-- Execution plan:
-- Table Scan on Orders (cost: 1000, rows scanned: 10M)
-- Why? YEAR() function must be evaluated for every row

-- ✅ GOOD: Index used
SELECT * FROM Orders
WHERE CreatedDate >= '2024-01-01' 
  AND CreatedDate < '2025-01-01';

-- Execution plan:
-- Index Seek on IX_Orders_CreatedDate (cost: 5, rows scanned: 50K)
-- 200x faster!

Index Killer #2 - Implicit Conversion:

-- Column is VARCHAR(50), index exists
CREATE INDEX IX_Users_Email ON Users(Email);

-- ❌ BAD: Passed as NVARCHAR, causes conversion
DECLARE @email NVARCHAR(50) = 'user@example.com';
SELECT * FROM Users WHERE Email = @email;

-- Execution plan shows:
-- CONVERT_IMPLICIT(nvarchar(50), Email) = @email
-- Index cannot be used on converted column!

-- ✅ GOOD: Match the column type
DECLARE @email VARCHAR(50) = 'user@example.com';
SELECT * FROM Users WHERE Email = @email;

-- Index seek, instant result

How to Detect Implicit Conversions:

-- Check execution plan XML for warnings
SELECT 
    query_plan,
    text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
WHERE CAST(query_plan AS NVARCHAR(MAX)) LIKE '%CONVERT_IMPLICIT%';

-- Look for: 

Index Killer #3 - Leading Wildcards:

CREATE INDEX IX_Products_Name ON Products(Name);

-- ❌ BAD: Cannot use index
SELECT * FROM Products WHERE Name LIKE '%Widget%';
-- Scans entire table

-- ✅ GOOD: Can use index
SELECT * FROM Products WHERE Name LIKE 'Widget%';
-- Index seek on "Widget" range

-- Why? Indexes are sorted alphabetically:
-- Apple
-- Banana  
-- Cherry
-- Widget Black
-- Widget Blue

-- 'Widget%': Binary search to "Widget", read consecutive rows
-- '%Widget%': Must check every row, no way to jump to middle

Index Killer #4 - OR Conditions on Different Columns:

CREATE INDEX IX_Orders_CustomerId ON Orders(CustomerId);
CREATE INDEX IX_Orders_Status ON Orders(Status);

-- ❌ BAD: Cannot efficiently use either index
SELECT * FROM Orders 
WHERE CustomerId = 123 OR Status = 'Pending';

-- Execution plan:
-- Table Scan (must check both conditions for every row)

-- ✅ GOOD: Use UNION
SELECT * FROM Orders WHERE CustomerId = 123
UNION
SELECT * FROM Orders WHERE Status = 'Pending';

-- Execution plan:
-- Index Seek on IX_Orders_CustomerId
-- + Index Seek on IX_Orders_Status
-- + Concatenation (removes duplicates)

Index Killer #5 - NOT IN / NOT EXISTS:

-- ❌ BAD: Hard to optimize
SELECT * FROM Orders
WHERE CustomerId NOT IN (SELECT Id FROM BlockedCustomers);

-- SQL Server must:
-- 1. Build full list of blocked customers
-- 2. Check every order against the list
-- 3. Can't use index effectively

-- ✅ GOOD: LEFT JOIN with NULL check
SELECT o.*
FROM Orders o
LEFT JOIN BlockedCustomers b ON o.CustomerId = b.Id
WHERE b.Id IS NULL;

-- Can use indexes on both tables
-- Often 10-100x faster

Composite Index Column Order Matters:

-- Common query pattern:
SELECT * FROM Orders 
WHERE CustomerId = 123 AND Status = 'Pending';

-- ❌ WRONG ORDER:
CREATE INDEX IX_Orders_Status_Customer ON Orders(Status, CustomerId);

-- Query can partially use index:
-- 1. Seek to Status = 'Pending' (fast)
-- 2. Scan all pending orders for CustomerId 123 (slower)

-- ✅ RIGHT ORDER:
CREATE INDEX IX_Orders_Customer_Status ON Orders(CustomerId, Status);

-- Query fully uses index:
-- 1. Seek to CustomerId = 123 (fast)
-- 2. Within that customer, seek to Status = 'Pending' (fast)
-- Both parts use index seek!

-- Rule: Most selective column first (fewer unique values = higher selectivity)
-- CustomerId: 10,000 unique values
-- Status: 5 unique values
-- CustomerId is more selective → put it first

Include Columns for Covering Index:

-- Query always needs OrderDate and Total
SELECT OrderDate, Total 
FROM Orders 
WHERE CustomerId = 123;

-- ❌ OK but not optimal:
CREATE INDEX IX_Orders_CustomerId ON Orders(CustomerId);

-- Execution plan:
-- 1. Index Seek on IX_Orders_CustomerId (fast)
-- 2. Key Lookup to get OrderDate, Total (slower - reads from table)

-- ✅ BEST: Covering index
CREATE INDEX IX_Orders_CustomerId 
ON Orders(CustomerId) 
INCLUDE (OrderDate, Total);

-- Execution plan:
-- 1. Index Seek on IX_Orders_CustomerId (fast)
-- 2. Data already in index - no table lookup needed!
-- 50% faster than non-covering index

Find Missing Indexes:

-- SQL Server tracks missing indexes automatically
SELECT 
    OBJECT_NAME(mid.object_id) AS TableName,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.avg_user_impact,  -- Performance improvement %
    migs.user_seeks,
    migs.user_scans
FROM sys.dm_db_missing_index_details mid
JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
WHERE migs.avg_user_impact > 50  -- Focus on high impact
ORDER BY migs.avg_user_impact DESC;

-- Example output:
-- TableName: Orders
-- equality_columns: [CustomerId]
-- avg_user_impact: 87.5%
-- → Create this index for 87% faster queries!