SQL Server Indexing Strategies That Actually Work in Production

SQL Server Indexing Strategies That Actually Work in Production

After optimizing SQL Server databases handling payroll, accounting, and inventory for enterprise clients, I've learned that textbook indexing advice often fails in production.

This post covers what actually works when you're dealing with millions of transactions, complex queries, and zero-downtime requirements.

The Reality Check

Textbook advice:

"Add indexes on all foreign keys and frequently queried columns."

Production reality:

"That index slowed down our payroll batch by 40% because of page splits."

Here's what I've learned from the trenches.

Understanding Your Workload

Before adding any index, understand your workload:

OLTP vs OLAP

OLTP (Online Transaction Processing):

• Many small writes
• Point lookups
• Example: Processing individual payroll entries

OLAP (Online Analytical Processing):

• Few large reads
• Aggregations
• Example: Monthly financial reports

Key insight: Optimize indexes for your dominant workload. You can't optimize equally for both.

Query Pattern Analysis

-- Find your most expensive queries
SELECT TOP 20
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    qs.execution_count,
    SUBSTRING(qt.text, (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset) / 2) + 1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY avg_elapsed_time DESC;

Index Types and When to Use Them

Clustered Indexes

What: Determines physical order of data in the table.

Rule: One per table. Choose wisely.

Good choices:

• Primary keys (usually)
• Columns used in range queries
• Columns with high selectivity

Example:

-- Good for payroll transactions (often queried by date range)
CREATE CLUSTERED INDEX IX_PayrollTransactions_Date
ON PayrollTransactions (TransactionDate DESC, EmployeeId);

Non-Clustered Indexes

What: Separate structure pointing to data rows.

When:

• Covering frequently queried columns
• Supporting ORDER BY / GROUP BY
• Enforcing uniqueness

Example:

-- Covering index for employee lookup
CREATE NONCLUSTERED INDEX IX_Employees_Department_Status
ON Employees (DepartmentId, EmploymentStatus)
INCLUDE (EmployeeName, Email, HireDate);

Filtered Indexes

What: Index on a subset of rows.

When:

• Queries often filter on specific values
• Column has skewed data distribution
• Want smaller, faster indexes

Example:

-- Index only on active employees (90% of queries)
CREATE NONCLUSTERED INDEX IX_Employees_Active
ON Employees (EmployeeName)
WHERE EmploymentStatus = 'Active';

Result: 70% smaller index, faster seeks.

Columnstore Indexes

What: Column-based storage for analytics.

When:

• Data warehouse / reporting queries
• Large table scans
• Aggregations over millions of rows

Example:

-- For financial reporting queries
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FinancialTransactions
ON FinancialTransactions;

Warning: Terrible for OLTP workloads. Use on reporting replicas only.

Real-World Optimization Scenarios

Scenario 1: Slow Payroll Batch Processing

Problem: End-of-month payroll batch taking 4 hours.

Root cause: Missing index on foreign key causing table scans.

Solution:

-- Before: Table scan on every employee lookup
SELECT * FROM PayrollDetails WHERE EmployeeId = @id;

-- After: Add index
CREATE NONCLUSTERED INDEX IX_PayrollDetails_EmployeeId
ON PayrollDetails (EmployeeId)
INCLUDE (BasicSalary, Allowances, Deductions);

Result: Batch time reduced from 4 hours to 45 minutes.

Scenario 2: Reporting Query Timeout

Problem: Monthly financial report timing out after 30 minutes.

Root cause: Multiple table scans and hash joins.

Solution:

-- Covering index for report query
CREATE NONCLUSTERED INDEX IX_FinancialTransactions_Report
ON FinancialTransactions (CompanyId, TransactionDate, AccountId)
INCLUDE (Amount, DebitCredit, Description);

Plus: Update statistics before report runs:

UPDATE STATISTICS FinancialTransactions WITH FULLSCAN;

Result: Report completes in 2 minutes.

Scenario 3: Index Fragmentation Killing Performance

Problem: Queries getting slower over time.

Root cause: Index fragmentation from frequent inserts/deletes.

Diagnosis:

SELECT 
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 30
ORDER BY ips.avg_fragmentation_in_percent DESC;

Solution:

-- Rebuild heavily fragmented indexes
ALTER INDEX IX_PayrollTransactions_Date 
ON PayrollTransactions 
REBUILD WITH (ONLINE = ON, SORT_IN_TEMPDB = ON);

-- Reorganize moderately fragmented indexes
ALTER INDEX IX_Employees_Department 
ON Employees 
REORGANIZE;

Maintenance plan: Schedule weekly index maintenance during off-hours.

Index Design Principles

1. Key Column Order Matters

-- Good: High selectivity first
CREATE INDEX IX_Good ON Orders (CustomerId, OrderDate);

-- Bad: Low selectivity first
CREATE INDEX IX_Bad ON Orders (OrderDate, CustomerId);

Why: SQL Server can only use the index efficiently from left to right.

2. Include Columns for Covering

-- Query
SELECT EmployeeName, Email, Phone 
FROM Employees 
WHERE DepartmentId = 5;

-- Covering index (no key lookup needed)
CREATE INDEX IX_Employees_Department_Covering
ON Employees (DepartmentId)
INCLUDE (EmployeeName, Email, Phone);

Benefit: Query satisfied entirely from index.

3. Avoid Over-Indexing

Problem: Too many indexes slow down writes.

Rule of thumb:

• OLTP tables: 5-10 indexes max
• Reporting tables: More acceptable

Monitor:

-- Find unused indexes
SELECT 
    OBJECT_NAME(s.object_id) AS TableName,
    i.name AS IndexName,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0
AND s.user_updates > 0  -- Index is being maintained but never used
ORDER BY s.user_updates DESC;

4. Consider Write Impact

Every index adds overhead to INSERT, UPDATE, DELETE.

Example:

-- This index helps reads but hurts writes
CREATE INDEX IX_Employees_Name ON Employees (EmployeeName);

-- Every insert now requires:
-- 1. Insert into table
-- 2. Insert into index
-- 3. Index page splits if necessary

Trade-off: Measure read improvement vs write penalty.

Monitoring and Maintenance

Index Usage Monitoring

-- Track index usage over time
SELECT 
    OBJECT_NAME(object_id) AS TableName,
    index_id,
    user_seeks,
    user_scans,
    user_lookups,
    user_updates,
    last_user_seek,
    last_user_scan
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID()
ORDER BY user_seeks DESC;

Missing Index Recommendations

-- SQL Server's missing index suggestions
SELECT 
    OBJECT_NAME(mid.object_id) AS TableName,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.avg_user_impact,
    migs.user_seeks
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
ORDER BY migs.avg_user_impact DESC;

Warning: Use as guidance, not gospel. Test before creating.

Statistics Maintenance

-- Update statistics with sampling
UPDATE STATISTICS Employees WITH SAMPLE 50 PERCENT;

-- Full scan for critical tables
UPDATE STATISTICS PayrollTransactions WITH FULLSCAN;

-- Check when stats were last updated
SELECT 
    OBJECT_NAME(object_id) AS TableName,
    name AS StatisticsName,
    last_updated
FROM sys.stats
CROSS APPLY sys.dm_db_stats_properties(object_id, stats_id)
ORDER BY last_updated ASC;

Common Mistakes to Avoid

1. Indexing Every Column

Wrong:

CREATE INDEX IX_Everything ON Employees (Name, Email, Phone, Department, Salary, ...);

Right: Index based on query patterns.

2. Ignoring Fill Factor

Problem: Page splits from 100% full pages.

Solution:

-- Leave 20% free space for growth
CREATE INDEX IX_PayrollTransactions 
ON PayrollTransactions (TransactionDate)
WITH (FILLFACTOR = 80);

3. Not Testing in Production-Like Environment

Problem: Index works great on small test data, fails on production volume.

Solution: Test with production-sized data and realistic query load.

4. Forgetting About Parameter Sniffing

Problem: Query plan optimized for first parameter value, terrible for others.

Solution:

-- Use OPTION (RECOMPILE) for varying parameters
SELECT * FROM Orders 
WHERE CustomerId = @CustomerId 
AND OrderDate >= @StartDate
OPTION (RECOMPILE);

Performance Measurement

Always measure before and after:

-- Enable actual execution plan
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Run query
SELECT * FROM Employees WHERE DepartmentId = 5;

-- Check output:
-- Table 'Employees'. Scan count 1, logical reads 150, ...
-- SQL Server Execution Times: CPU time = 45 ms, elapsed time = 52 ms.

Key metrics:

• Logical reads (lower is better)
• CPU time
• Elapsed time
• Execution plan (look for index seeks vs scans)

Summary

1. Understand your workload before adding indexes
2. Start with clustered index—it's the foundation
3. Use covering indexes to eliminate key lookups
4. Monitor usage and remove unused indexes
5. Maintain regularly—fragmentation kills performance
6. Test with production data—test environments lie
7. Measure everything—don't optimize based on guesses

---

Have a specific SQL Server performance challenge? Reach out on LinkedIn—happy to discuss.