SQL Server Performance Tuning: Indexing Strategies That Work

REPLACE(REPLACE(ISNULL(mid.equality_columns, ''), '[', ''), ']', '') +
CASE WHEN mid.inequality_columns IS NOT NULL
THEN '_' + REPLACE(REPLACE(mid.inequality_columns, '[', ''), ']', '')
ELSE '' END +
' ON ' + OBJECT_NAME(mid.object_id, mid.database_id) + ' (' +
ISNULL(mid.equality_columns, '') +
CASE WHEN mid.inequality_columns IS NOT NULL
THEN CASE WHEN mid.equality_columns IS NOT NULL THEN ',' ELSE '' END +
mid.inequality_columns
ELSE '' END + ')' +
CASE WHEN mid.included_columns IS NOT NULL
THEN ' INCLUDE (' + mid.included_columns + ')'
ELSE '' END AS CreateIndexStatement```
FROM sys.dm_db_missing_index_details mid
INNER JOIN sys.dm_db_missing_index_groups mig

ON mid.index_handle = mig.index_handle```
INNER JOIN sys.dm_db_missing_index_group_stats migs 
```text
ON mig.index_handle = migs.group_handle```
WHERE mid.database_id = DB_ID()
  AND migs.avg_user_impact > 50 -- High impact
ORDER BY migs.avg_user_impact DESC;

Find Unused Indexes

-- Indexes that are never used should be dropped
SELECT 
```text
OBJECT_NAME(i.object_id) AS TableName,
i.name AS IndexName,
i.type_desc,
ius.user_seeks,
ius.user_scans,
ius.user_lookups,
ius.user_updates,
CASE 
    WHEN ius.user_seeks + ius.user_scans + ius.user_lookups = 0 
    THEN 'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(OBJECT_NAME(i.object_id)) + ';'
    ELSE 'Index is used'
END AS Recommendation```
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius 
```text
ON i.object_id = ius.object_id AND i.index_id = ius.index_id```
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
  AND i.type_desc != 'HEAP'
  AND i.is_primary_key = 0
  AND i.is_unique_constraint = 0
  AND (ius.user_seeks + ius.user_scans + ius.user_lookups = 0 OR ius.index_id IS NULL)
ORDER BY TableName, IndexName;

Index Usage Statistics

-- Detailed index usage analysis
SELECT 
```text
OBJECT_NAME(ius.object_id) AS TableName,
i.name AS IndexName,
ius.user_seeks AS UserSeeks,
ius.user_scans AS UserScans,
ius.user_lookups AS UserLookups,
ius.user_updates AS UserUpdates,
ius.last_user_seek AS LastSeek,
ius.last_user_scan AS LastScan,
CASE 
    WHEN ius.user_seeks + ius.user_scans + ius.user_lookups = 0 THEN 'NEVER USED'
    WHEN ius.user_updates > (ius.user_seeks + ius.user_scans + ius.user_lookups) * 2 
    THEN 'WRITE HEAVY (Consider Dropping)'
    WHEN ius.user_seeks + ius.user_scans + ius.user_lookups > ius.user_updates * 10 
    THEN 'READ HEAVY (Excellent)'
    ELSE 'BALANCED'
END AS UsagePattern```
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.indexes i 
```text
ON ius.object_id = i.object_id AND ius.index_id = i.index_id```
WHERE ius.database_id = DB_ID()
  AND OBJECTPROPERTY(ius.object_id, 'IsUserTable') = 1
ORDER BY UserSeeks + UserScans + UserLookups DESC;

Step 9: Advanced Indexing Strategies

Composite Index Column Order

-- Rule: Most selective columns first, then less selective

-- BAD: Low selectivity first
CREATE INDEX IX_Orders_Bad ON Orders (OrderStatus, CustomerID);
-- OrderStatus has only 4 values, CustomerID has 100,000 values

-- GOOD: High selectivity first
CREATE INDEX IX_Orders_Good ON Orders (CustomerID, OrderStatus);
-- CustomerID narrows down quickly, then OrderStatus refines

-- Test the difference
SET STATISTICS IO ON;

-- Using bad index
SELECT * FROM Orders WITH (INDEX(IX_Orders_Bad))
WHERE CustomerID = 12345 AND OrderStatus = 'Pending';
-- Higher logical reads

-- Using good index
SELECT * FROM Orders WITH (INDEX(IX_Orders_Good))
WHERE CustomerID = 12345 AND OrderStatus = 'Pending';
-- Much fewer logical reads!

SET STATISTICS IO OFF;

Index for Sorting (ORDER BY)

-- Create index matching sort order
CREATE INDEX IX_Orders_DateDesc ON Orders (OrderDate DESC, OrderID DESC);

-- Query benefits from sorted index
SELECT TOP 100 OrderID, OrderDate, TotalAmount
FROM Orders
ORDER BY OrderDate DESC, OrderID DESC;
-- No sort operation needed! Index already sorted

-- Check execution plan: Should show "Index Seek" with "Ordered: True"

Partial Match Searches

-- For LIKE queries with leading wildcard
CREATE INDEX IX_Customers_Email ON Customers (Email);

-- This can use index (leading characters known)
SELECT * FROM Customers WHERE Email LIKE 'john%';
-- Index seek possible

-- This CANNOT use index (leading wildcard)
SELECT * FROM Customers WHERE Email LIKE '%@gmail.com';
-- Full index scan required

-- Solution for trailing searches: Computed column + index
ALTER TABLE Customers ADD EmailReversed AS REVERSE(Email);
CREATE INDEX IX_Customers_EmailReversed ON Customers (EmailReversed);

-- Now search reversed
SELECT * FROM Customers WHERE EmailReversed LIKE REVERSE('%@gmail.com') + '%';
-- Can use index seek!

Step 10: Monitoring and Troubleshooting

Create Monitoring Dashboard

-- Index health report
CREATE OR ALTER VIEW vw_IndexHealthReport
AS
SELECT 
```text
OBJECT_NAME(i.object_id) AS TableName,
i.name AS IndexName,
i.type_desc AS IndexType,
ips.avg_fragmentation_in_percent AS Fragmentation,
ips.page_count AS Pages,
(ips.page_count * 8) / 1024.0 AS SizeMB,
ius.user_seeks AS Seeks,
ius.user_scans AS Scans,
ius.user_lookups AS Lookups,
ius.user_updates AS Updates,
ius.last_user_seek AS LastSeek,
CASE 
    WHEN ips.avg_fragmentation_in_percent > 30 THEN 'REBUILD NEEDED'
    WHEN ips.avg_fragmentation_in_percent > 10 THEN 'REORGANIZE RECOMMENDED'
    ELSE 'HEALTHY'
END AS MaintenanceAction,
CASE 
    WHEN ius.user_seeks + ius.user_scans + ius.user_lookups = 0 THEN 'UNUSED - CONSIDER DROPPING'
    WHEN ius.user_updates > (ius.user_seeks + ius.user_scans + ius.user_lookups) * 3 
    THEN 'WRITE HEAVY - REVIEW NECESSITY'
    ELSE 'OPTIMAL'
END AS UsageStatus```
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
```text
ON i.object_id = ips.object_id AND i.index_id = ips.index_id```
LEFT JOIN sys.dm_db_index_usage_stats ius
```text
ON i.object_id = ius.object_id AND i.index_id = ius.index_id AND ius.database_id = DB_ID()```
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
  AND i.type_desc != 'HEAP';
GO

-- Query the dashboard
SELECT * FROM vw_IndexHealthReport
WHERE MaintenanceAction != 'HEALTHY' OR UsageStatus != 'OPTIMAL'
ORDER BY SizeMB DESC;

Performance Baseline

-- Capture current performance metrics
CREATE TABLE IndexPerformanceBaseline (
```text
CaptureDate DATETIME NOT NULL DEFAULT GETDATE(),
TableName NVARCHAR(128),
IndexName NVARCHAR(128),
UserSeeks BIGINT,
UserScans BIGINT,
UserLookups BIGINT,
UserUpdates BIGINT,
AvgFragmentation FLOAT,
PageCount BIGINT```
);

-- Capture baseline
INSERT INTO IndexPerformanceBaseline
SELECT 
```text
GETDATE(),
OBJECT_NAME(i.object_id),
i.name,
ISNULL(ius.user_seeks, 0),
ISNULL(ius.user_scans, 0),
ISNULL(ius.user_lookups, 0),
ISNULL(ius.user_updates, 0),
ips.avg_fragmentation_in_percent,
ips.page_count```
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius
```text
ON i.object_id = ius.object_id AND i.index_id = ius.index_id AND ius.database_id = DB_ID()```
LEFT JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
```text
ON i.object_id = ips.object_id AND i.index_id = ips.index_id```
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1;

-- Compare against baseline (run after some time)
SELECT 
```text
current.TableName,
current.IndexName,
current.UserSeeks - baseline.UserSeeks AS SeeksDelta,
current.UserScans - baseline.UserScans AS ScansDelta,
current.UserUpdates - baseline.UserUpdates AS UpdatesDelta,
current.AvgFragmentation - baseline.AvgFragmentation AS FragmentationDelta,
DATEDIFF(HOUR, baseline.CaptureDate, current.CaptureDate) AS HoursSinceBaseline```
FROM IndexPerformanceBaseline baseline
CROSS APPLY (
```sql
SELECT TOP 1 *
FROM IndexPerformanceBaseline current_inner
WHERE current_inner.TableName = baseline.TableName
  AND current_inner.IndexName = baseline.IndexName
ORDER BY CaptureDate DESC```
) current
WHERE baseline.CaptureDate = (SELECT MIN(CaptureDate) FROM IndexPerformanceBaseline)
ORDER BY SeeksDelta DESC;

Best Practices Summary

DO:

1. ✅ Always create clustered index on primary key
2. ✅ Use covering indexes for frequently-run queries
3. ✅ Create filtered indexes for subset queries
4. ✅ Order composite index columns by selectivity (most selective first)
5. ✅ Monitor index usage and drop unused indexes
6. ✅ Rebuild fragmented indexes regularly (>30% fragmentation)
7. ✅ Include necessary columns to avoid key lookups
8. ✅ Use included_columns liberally in covering indexes

DON'T:

1. ❌ Create indexes on every column "just in case"
2. ❌ Index columns with very low selectivity (e.g., boolean, status with 2-3 values)
3. ❌ Create too many indexes on write-heavy tables
4. ❌ Ignore index maintenance (fragmentation)
5. ❌ Forget to analyze execution plans before creating indexes
6. ❌ Create duplicate or overlapping indexes
7. ❌ Use index hints unless absolutely necessary

Architecture Decision and Tradeoffs

When designing data management solutions with SQL Server, consider these key architectural trade-offs:

Recommendation: Start with the managed approach for most workloads and move to custom only when specific requirements demand it.

Validation and Versioning

• Last validated: April 2026
• Validate examples against your tenant, region, and SKU constraints before production rollout.
• Keep module, CLI, and SDK versions pinned in automation pipelines and review quarterly.

Security and Governance Considerations

• Apply least-privilege access using RBAC roles and just-in-time elevation for admin tasks.
• Store secrets in managed secret stores and avoid embedding credentials in scripts or source files.
• Enable audit logging, data protection policies, and periodic access reviews for regulated workloads.

Cost and Performance Notes

• Define budgets and alerts, then monitor usage and cost trends continuously after go-live.
• Baseline performance with synthetic and real-user checks before and after major changes.
• Scale resources with measured thresholds and revisit sizing after usage pattern changes.

Official Microsoft References

• https://learn.microsoft.com/sql/
• https://learn.microsoft.com/azure/azure-sql/
• https://learn.microsoft.com/fabric/database/

Public Examples from Official Sources

• These examples are sourced from official public Microsoft documentation and sample repositories.
• Documentation examples: https://learn.microsoft.com/sql/
• Sample repositories: https://github.com/microsoft/sql-server-samples
• Prefer adapting these examples to your tenant, subscriptions, and governance requirements before production use.

Key Takeaways

1. Clustered indexes physically sort data - only ONE per table, choose wisely (usually primary key)
2. Non-clustered indexes are separate structures - can have up to 999 per table
3. Covering indexes eliminate key lookups by including all needed columns - massive performance gains
4. Filtered indexes reduce index size and improve performance for subset queries
5. Index maintenance is critical - rebuild when fragmentation > 30%, reorganize when 10-30%
6. Monitor index usage - drop unused indexes that slow down writes
7. Composite index column order matters - most selective columns first
8. Analyze execution plans before creating indexes to verify they'll be used

Additional Resources

• SQL Server Index Architecture
• Index Design Guidelines
• Execution Plans Explained
• DMV Queries for Performance
• Brent Ozar's Index Tuning Resources

Next Steps

1. Audit your database: Run missing index queries and find unused indexes
2. Create covering indexes: Identify your top 10 queries and optimize them
3. Implement monitoring: Set up automated index health checks
4. Schedule maintenance: Create SQL Agent jobs for regular index rebuilding
5. Learn execution plans: Master reading and interpreting query plans
6. Explore columnstore: For analytical workloads, investigate columnstore indexes
7. Test everything: Use non-production environments to test index changes

Ready to supercharge your database performance? Start with the missing index queries and work through your most expensive operations first—you'll see dramatic improvements immediately!