Bits of .NET

🔍 Key Takeaways: Sometimes SQL Server chooses the wrong index. Index hints force the query optimizer to use a specific index.

SQL Server usually makes good choices. When it doesn’t, index hints save the day.

❌ When SQL Server Makes Bad Choices

-- ❌ SQL Server uses wrong index (table scan)
SELECT *
FROM Orders
WHERE OrderDate > '2024-01-01'
  AND CustomerId = 12345
  AND Status = 'Shipped';

-- Estimated execution plan: Table Scan (bad!)
-- Real execution: 5,000 reads, 2,500ms

✅ Using Index Hints

-- ✅ Force index usage
SELECT *
FROM Orders WITH (INDEX(IX_Orders_OrderDate_Status))
WHERE OrderDate > '2024-01-01'
  AND CustomerId = 12345
  AND Status = 'Shipped';

-- ✅ Force clustered index
SELECT *
FROM Orders WITH (INDEX(PK_Orders))
WHERE OrderDate > '2024-01-01'
  AND CustomerId = 12345
  AND Status = 'Shipped';

-- ✅ Use multiple indexes
SELECT *
FROM Orders WITH (INDEX(IX_Orders_OrderDate, IX_Orders_CustomerId))
WHERE OrderDate > '2024-01-01'
  AND CustomerId = 12345
  AND Status = 'Shipped';

💡 Advanced Index Hints

-- ✅ Force index with table hints
SELECT o.*
FROM Orders o WITH (FORCESEEK(IX_Orders_OrderDate_Status))
WHERE o.OrderDate > '2024-01-01'
  AND o.Status = 'Shipped';

-- ✅ FORCESCAN (force table/index scan)
SELECT *
FROM Orders WITH (FORCESCAN)
WHERE OrderDate > '2024-01-01';

-- ✅ Combine with join hints
SELECT o.*, c.*
FROM Orders o WITH (INDEX(IX_Orders_CustomerId))
JOIN Customers c WITH (INDEX(PK_Customers)) ON o.CustomerId = c.CustomerId
WHERE o.OrderDate > '2024-01-01';

-- ✅ NOEXPAND (force index view)
SELECT *
FROM vw_OrderSummary WITH (NOEXPAND)
WHERE Total > 1000;

💡 Pro Tip: Find Missing Indexes

-- ✅ Find recommended indexes
SELECT 
    migs.avg_total_user_cost,
    migs.avg_user_impact,
    migs.user_seeks,
    mid.statement,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE database_id = DB_ID()
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact DESC;