Choosing and creating SQL Server indexes is a balancing act: the right indexes speed up reads dramatically, but every index adds overhead to INSERT, UPDATE, and DELETE operations. This guide shows you how to design indexes that give you the query performance you need while keeping write slowdowns to a minimum.
What You’ll Need
- SQL Server Management Studio (SSMS) or another query tool with access to the database.
- Permissions to create indexes (typically the
ALTERpermission on the table). - Knowledge of the queries you want to speed up—you don’t need every query, just the important ones.
- An understanding of your table’s write frequency (how often rows are inserted, updated, or deleted).
Step-by-Step
- Identify the slow queries. Use Dynamic Management Views (DMVs) to find queries with high reads or long durations. For example, run:
SELECT TOP 10 qs.total_worker_time, qs.execution_count, SUBSTRING(st.text, 1, 200) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_worker_time DESC;
Focus on queries that run often or are part of critical user interactions. - Examine the execution plan. In SSMS, include the actual execution plan (Ctrl+M) and run the query. Look for table scans or key lookups—these are signs that an index might help. Note the columns used in WHERE, JOIN, ORDER BY, and SELECT clauses.
- Design indexes for the most important queries. For each query, ask: What columns filter rows? What columns are returned? A typical index includes:
- Key columns—those used in WHERE and JOIN conditions, in the order of selectivity (most selective first).
- Included columns—those only in the SELECT list, to avoid key lookups.
- Keep the index narrow. Use the fewest columns needed. Each key column adds to the index size and slows writes. If a column is rarely used in queries, don’t index it.
- Consider index uniqueness. Primary keys and unique constraints create unique indexes automatically. For other indexes, if the data is naturally unique (like an email address), make the index unique—it helps the optimizer and doesn’t add write overhead beyond what a non-unique index would have.
- Use the Database Engine Tuning Advisor (DTA) for suggestions. Run DTA on a representative workload to get index recommendations. Treat them as starting points, not gospel—review each suggestion against your write patterns.
- Create the index with a fill factor. For tables with frequent updates, set
FILLFACTORto 70–80% to leave room for page splits. This reduces fragmentation and the associated write slowdowns. Example:CREATE INDEX IX_Orders_CustomerID ON dbo.Orders (CustomerID) WITH (FILLFACTOR = 80); - Test the index on a copy or during low activity. Create the index in a test environment or during a maintenance window. Measure the performance of the target query and also measure write performance (inserts, updates, deletes) before and after.
- Monitor and adjust. After deploying, watch for fragmentation and usage. Use
sys.dm_db_index_usage_statsto see if the index is actually used. If it’s not used after a few weeks, drop it. - Rebuild or reorganize periodically. Fragmented indexes slow reads and writes. Schedule index maintenance (REORGANIZE if fragmentation is 5–30%, REBUILD if above 30%).
Common Mistakes
- Over-indexing: Adding an index for every query. Each index adds write overhead and uses disk space. Start with the top 5–10 queries.
- Including too many columns in the key: A wide index is less efficient and slows writes more. Use included columns for non-filtering columns.
- Ignoring the order of key columns: If you have a WHERE clause with two columns, the more selective column should be first. Otherwise the index may not be used.
- Creating indexes on low-selectivity columns: Indexing a column like
Statuswhere 90% of rows have the same value often doesn’t help reads and just hurts writes. - Not considering the impact on INSERT performance: On a table with heavy inserts, every index requires an update to the index structure. Fewer indexes mean faster inserts.
Tips
- Use included columns instead of key columns for SELECT-only columns. This keeps the key narrow and still avoids key lookups.
- Consider filtered indexes for partial data. If you often query a subset of rows (e.g., only active orders), a filtered index on
Status = 'Active'is smaller and less costly to maintain. - Drop unused indexes. Use
sys.dm_db_index_usage_statsto find indexes with zero seeks or scans—they’re pure overhead. - Test with realistic data volumes. An index that works on a small table may not help on a large one, and the write overhead becomes more noticeable with more rows.
- Use online index operations where possible. In Enterprise Edition,
CREATE INDEX WITH (ONLINE = ON)reduces blocking, but it still adds overhead.
Troubleshooting
Index not used: Check the query’s WHERE clause—if you use a function on the column (e.g., WHERE YEAR(OrderDate) = 2023), the index is ignored. Rewrite as a range: WHERE OrderDate >= '2023-01-01' AND OrderDate < '2024-01-01'.
Writes have become slower after adding an index: That’s expected. If the slowdown is unacceptable, consider dropping the index or making it narrower. Also check if the index is causing page splits—increase the fill factor or rebuild.
Queries still slow: Look for missing statistics. Update statistics on the indexed columns: UPDATE STATISTICS dbo.Orders; Sometimes the optimizer needs fresh stats.
Index fragmentation is high: Run ALTER INDEX ... REORGANIZE for light fragmentation or REBUILD for heavy. Schedule this during low write activity.
Cannot create index because of blocking: Use WITH (ONLINE = ON) if your edition supports it, or schedule during maintenance.
Frequently Asked Questions
Q: How many indexes is too many?
A: There’s no fixed number, but a general rule is to keep fewer than 5–6 per table for OLTP workloads. Each index adds write overhead, so only keep those that are clearly beneficial.
Q: Should I index every column in the WHERE clause?
A: Not necessarily. Composite indexes (multiple columns) are often better than separate indexes. Also, if a column is rarely used, skip it.
Q: What’s the difference between a clustered and nonclustered index?
A: A clustered index determines the physical order of rows—there’s only one per table. Nonclustered indexes are separate structures. Clustered indexes are great for range queries but can slow inserts if the key isn’t sequential.
Q: Does a primary key always create an index?
A: Yes, a primary key creates a unique clustered index by default (unless you specify otherwise). That’s usually fine, but for tables with heavy inserts, a nonclustered primary key might be better.
Q: How do I know if an index is unused?
A: Query sys.dm_db_index_usage_stats and look for indexes with 0 seeks, scans, or lookups. But note that stats reset on server restart, so monitor over a period of normal activity.
Q: Can I create an index on a view?
A: Yes, but it requires schemabinding and specific settings. Indexed views can speed up complex aggregations, but they add maintenance overhead to the base tables.
Related
General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.
More in this bed
- Disable Autocorrect on iPhone or iPad: Quick Guide
- Custom Keyboard Shortcuts on Windows 11 & macOS
- Restore Windows 11 Display Scaling to Recommended
- Semiconductor Cycle: How Investors Track It0.0
- Microsoft Store Error 0x80073d12: Fixes That Work
- View Notification Count on iPhone: Full Guide