Index Tuning

1. Detailed MetricsSQL Server

UID: sql_server_index_tuningVersion: 1.3.3 Panels: 9 SQL Queries: 10

Purpose

This dashboard provides detailed information about the state of indexes in SQL Server, allowing you to identify:

Missing indexes that could improve performance
Unused indexes that consume space unnecessarily
Duplicate indexes that generate maintenance overhead
Heap tables without clustered index

Importance of Index Management

Indexes are fundamental structures for the performance of any SQL Server database. Proper index management can mean the difference between a query that takes milliseconds and one that takes minutes.

Benefits of a good index strategy:

Aspect	Without proper indexes	With optimized indexes
Reads	Full table scans	Direct seeks to data
CPU	High consumption in sorting	Pre-sorted data
I/O	Reading unnecessary pages	Minimal page reads
Locks	Extended table locks	Precise row locks
Response time	Seconds to minutes	Milliseconds

Real business impact:

Slow queries directly affect user experience
Excessive I/O impacts the entire instance, not just one query
Prolonged locks can cause application timeouts
A missing index on a critical table can degrade the entire system

Important fact

According to Microsoft, index problems are responsible for more than 60% of poor performance cases in SQL Server. The Missing Index DMV can identify indexes that would improve performance by up to 99%.

Variables

Variable	Description	Type
`$Server_Name`	SQL Server to analyze	Query (hc_server_configuration)
`$Database`	Database (or all)	Query with "-- All Databases" option
`$Table`	Specific table (or all)	Query with "-- All Tables" option

Panels

Index Summary

Index Summary Stats stat

Shows global counters of index status on the selected instance.

Metric	Description	Action if high
Duplicated	Duplicate indexes detected	Review and remove duplicates
Missing	Indexes suggested by SQL Server	Evaluate creation
Unused	Created but unused indexes	Consider removal
Total Index	Total indexes created	Reference
Tables Clustered	Tables with clustered index	Ideal: all tables
Tables Non-Clustered	Tables with only NC indexes	Normal

Recommendation

A high number of Missing indexes combined with slow queries indicates that the system could significantly benefit from creating additional indexes.

Missing Indexes

Missing Indexes table

Indexes that SQL Server recommends creating based on analysis of executed queries.

Important columns:

Column	Meaning
`Equality Columns`	Columns used in `WHERE col = value` conditions
`Inequality Columns`	Columns used in `WHERE col > value`, `BETWEEN`, etc.
`Included Columns`	Columns that should be included (INCLUDE)
`User Seeks`	Times the index would have been used for seeks
`Avg User Impact`	Estimated improvement percentage (0-100%)
`Create Statement`	Ready-to-use script to create the index

Important

Do not automatically create all suggested indexes. Evaluate:

Impact on write operations (INSERT/UPDATE/DELETE)
Required disk space
Tables with high write activity

User Impact interpretation:

Impact	Priority	Action
> 90%	High	Create soon
50-90%	Medium	Evaluate
< 50%	Low	Optional

Unused Indexes

Unused Indexes table

Indexes that exist but have not been used since the last SQL service restart.

Candidates for removal:

An index is a candidate for removal if:

User Seeks = 0
User Scans = 0
User Lookups = 0
User Updates > 0 (indicates maintenance overhead)

Caution

Before removing an index:

Verify that SQL Server has been running long enough (preferably > 1 week)
Consider if the index is only used in monthly/annual processes
Save the creation script before removing

Duplicate Indexes

Duplicate Indexes table

Indexes that are redundant because another index covers the same columns.

Types of duplicates:

Type	Description	Action
Exact	Same key and included columns	Remove one
Subset	One index contains the other	Remove the smaller one
Overlap	Partially identical columns	Evaluate case by case

Heap Tables

Heap Tables (Without Clustered Index) table

Tables without clustered index that store data unordered.

Why avoid Heaps?

Fragmentation: Data is stored without order, causing random I/O
Table Scans: Without clustered index, many queries do full table scans
Forwarding pointers: Updates that increase row size create extra pointers

Acceptable exceptions:

Staging tables for ETL loads
Very small tables (< 1000 rows)
Insert-only tables without searches

Index Best Practices

When to Create Indexes

Ideal scenarios for creating an index:

Scenario	Recommended index type
Column frequently in WHERE with equality	Non-clustered on that column
Column used in JOINs	Non-clustered (or clustered if PK)
Columns frequently in ORDER BY	Non-clustered covering the order
Queries that always fetch the same columns	Index with INCLUDE
Table without natural PK	Clustered with identity or sequential GUID
Searches by date range	Clustered or NC on date column

Indicators for creating an index:

Missing Index with high User Seeks (> 1000) and Avg Impact > 80%
Frequent queries doing Table Scan on tables > 10,000 rows
Reporting queries taking longer than acceptable
Frequent locks on specific tables

When NOT to Create Indexes

Avoid creating indexes in these cases:

Situation	Reason
Very small tables (< 1000 rows)	Overhead exceeds benefit
Columns with low selectivity	Very repeated values (e.g., Yes/No, Active/Inactive)
Tables with very high write/read ratio	Index maintenance cost exceeds benefit
Missing Index with User Seeks < 100	Insufficient use to justify the index
Columns that change frequently	Generates fragmentation and overhead
Index already exists covering those columns	Would duplicate maintenance effort

General rule

Do not create more than 5-7 indexes per table in OLTP systems. For tables with high write activity, consider a maximum of 3-4 indexes.

Index Maintenance: Reorganize vs Rebuild

Regular index maintenance is crucial to maintain performance. SQL Server offers two main operations:

Operations comparison:

Feature	REORGANIZE	REBUILD
Target fragmentation	10% - 30%	> 30%
Locking	Minimal (online)	Table lock (offline) or online with Enterprise
Resource usage	Low	High
Execution time	Shorter	Longer
Updates statistics	No	Yes
Rebuilds pages	No, only reorders them	Yes, completely
Transactional	Can be stopped and continued	All or nothing (offline)

Recommended strategy by fragmentation level:

Fragmentation	Action	Suggested frequency
< 10%	None	N/A
10% - 30%	REORGANIZE	Weekly
> 30%	REBUILD	Weekly or as needed
> 50%	Urgent REBUILD	Immediate

Practical advice

Schedule index maintenance during low-usage windows. For 24/7 systems, use REBUILD ONLINE (requires Enterprise Edition) or REORGANIZE which has minimal impact.

Recommended Fill Factor

Fill Factor determines the percentage of space on each index page that is filled during creation or rebuild. The remaining space allows future insertions without causing page splits.

Fill Factor guide by usage pattern:

Table type	Fill Factor	Reason
Read-only (lookup tables)	100%	No insertions, maximum density
Reads >> Writes	90-95%	Few insertions, good balance
Read/write balance	80-90%	Space for moderate insertions
Writes >> Reads	70-80%	Reduces frequent page splits
Massive insertion with random values (GUIDs)	60-70%	GUIDs cause insertions in the middle of pages
Table with identity (insertion at end)	95-100%	Insertions are always at the end

Impact of incorrect Fill Factor:

Fill Factor too high (100%) on tables with insertions: Causes frequent page splits, immediate fragmentation
Fill Factor too low (< 70%) on read tables: Wastes disk space, more I/O to read the same data

Impact on DML Operations

Each index has a maintenance cost. When a data modification operation (INSERT, UPDATE, DELETE) is executed, SQL Server must update all affected indexes.

Cost per Operation

Operation	Impact on Indexes	Considerations
INSERT	All indexes must be updated	Each additional index = more I/O and more locks
UPDATE	Only indexes with modified columns	If the column is in the index key, the cost is higher
DELETE	All indexes must be updated	Similar to INSERT, must delete from each index

Overhead Calculation

To estimate the impact of indexes on write operations:

Number of NC indexes	Approximate overhead on INSERT
1-2	Minimal (< 10% additional)
3-5	Moderate (10-30% additional)
6-10	Significant (30-60% additional)
> 10	High (> 60% additional)

Warning for OLTP tables

On tables with high INSERT frequency (e.g., logs, transactions, auditing), each additional index can significantly degrade write performance. Carefully evaluate the real need for each index.

Symptoms of Index Excess

INSERT/UPDATE/DELETE times gradually increasing
Wait stats showing elevated PAGEIOLATCH_EX and PAGELATCH_EX
Unused Indexes with high number of User Updates but zero User Seeks/Scans
Transaction log growing rapidly during data loads

Evaluation Strategy

Use the following systematic workflow to evaluate dashboard recommendations and make informed decisions.

Missing Index Evaluation Workflow

┌─────────────────────────────────────────────────────────────────┐
│                    MISSING INDEX DETECTED                        │
└─────────────────────────────────────────────────────────────────┘
                              │
                              ▼
┌─────────────────────────────────────────────────────────────────┐
│  1. EVALUATE BASIC METRICS                                       │
│     - User Seeks > 1000?                                         │
│     - Avg User Impact > 80%?                                     │
│     - Last User Seek recent (last 7 days)?                       │
└─────────────────────────────────────────────────────────────────┘
                              │
              ┌───────────────┴───────────────┐
              │ Yes to all                    │ No
              ▼                               ▼
┌──────────────────────────┐    ┌──────────────────────────────┐
│ Continue evaluation      │    │ Low priority - Document      │
│                          │    │ and review in next cycle     │
└──────────────────────────┘    └──────────────────────────────┘
              │
              ▼
┌─────────────────────────────────────────────────────────────────┐
│  2. VERIFY EXISTING INDEXES                                      │
│     - Is there a similar index that could be expanded?           │
│     - Are the columns in another index as INCLUDE?               │
│     - Would it duplicate an existing index?                      │
└─────────────────────────────────────────────────────────────────┘
              │
              ▼
┌─────────────────────────────────────────────────────────────────┐
│  3. ANALYZE THE TABLE                                            │
│     - Table size (rows and MB)                                   │
│     - Read/write ratio                                           │
│     - Current number of NC indexes                               │
│     - Is it a business-critical table?                           │
└─────────────────────────────────────────────────────────────────┘
              │
              ▼
┌─────────────────────────────────────────────────────────────────┐
│  4. DECISION                                                     │
│     - Create index (if benefit > cost)                           │
│     - Modify existing index (add INCLUDE columns)                │
│     - Discard (document reason)                                  │
│     - Postpone (monitor in next cycle)                           │
└─────────────────────────────────────────────────────────────────┘
              │
              ▼
┌─────────────────────────────────────────────────────────────────┐
│  5. IF THE INDEX IS CREATED                                      │
│     - Test first in development/staging environment              │
│     - Create during low-usage hours                              │
│     - Monitor impact on writes after creation                    │
│     - Verify actual usage after 1 week                           │
└─────────────────────────────────────────────────────────────────┘

Evaluation Checklist

Before creating any recommended index, answer these questions:

Question	If Yes	If No
Is User Impact > 80%?	Continue	Consider low priority
Does the table have < 5 NC indexes?	Continue	Evaluate consolidation
Does the table have more reads than writes?	Continue	Extra caution
Would the index benefit critical queries?	High priority	Normal priority
Is there a similar modifiable index?	Modify existing	Create new
Was it tested in non-production environment?	Implement	Test first

Usage Guide

Recommended workflow

Review Missing Indexes with impact > 80%
Evaluate large Unused Indexes (> 100 MB)
Remove obvious Duplicate Indexes
Add clustered index to large Heaps with activity

Review frequency

Task	Frequency
Review Missing Indexes	Weekly
Evaluate Unused Indexes	Monthly
Clean up Duplicates	Quarterly
Convert Heaps	As needed
Maintenance (Rebuild/Reorganize)	Weekly
Review Fill Factor	Annually or when patterns change

Microsoft References

To delve deeper into index concepts and their management, consult the official Microsoft documentation:

Index Fundamentals

Index Architecture and Design Guide - Complete guide on index types and design
Indexes in SQL Server - Main index documentation page

Index DMVs

sys.dm_db_missing_index_details - Missing index details
sys.dm_db_index_usage_stats - Index usage statistics
sys.dm_db_index_physical_stats - Fragmentation and physical statistics

Maintenance

Reorganize and Rebuild Indexes - Official maintenance guide
Specify Fill Factor for an Index - Fill Factor configuration

Best Practices

Troubleshooting: Missing Indexes - How to use Missing Index recommendations
Clustered Index Design Guidelines - When to use clustered vs non-clustered

Index Tuning ​

Purpose ​

Importance of Index Management ​

Variables ​

Panels ​

Index Summary ​

Index Summary Stats stat

Missing Indexes ​

Missing Indexes table

Unused Indexes ​

Unused Indexes table

Duplicate Indexes ​

Duplicate Indexes table

Heap Tables ​

Heap Tables (Without Clustered Index) table

Index Best Practices ​

When to Create Indexes ​

When NOT to Create Indexes ​

Index Maintenance: Reorganize vs Rebuild ​

Recommended Fill Factor ​

Impact on DML Operations ​

Cost per Operation ​

Overhead Calculation ​

Symptoms of Index Excess ​

Evaluation Strategy ​

Missing Index Evaluation Workflow ​

Evaluation Checklist ​

Usage Guide ​

Recommended workflow ​

Review frequency ​

Microsoft References ​

Index Fundamentals ​

Index DMVs ​

Maintenance ​

Best Practices ​