MSSQL Indexing Deep Dive

Clustered vs Non-Clustered Indexes

Clustered Index

• •Determines physical order of data
• •Only ONE per table
• •Usually on primary key

SET NOCOUNT ON;

CREATE CLUSTERED INDEX [IX_Orders_OrderDate]
ON [dbo].[Orders] ([OrderDate] DESC)

Non-Clustered Index

• •Separate structure pointing to data
• •Multiple allowed per table

SET NOCOUNT ON;

CREATE NONCLUSTERED INDEX [IX_Orders_CustomerId]
ON [dbo].[Orders] ([CustomerId])
INCLUDE ([OrderDate], [TotalAmount])

Covering Indexes

Includes all columns needed by query:

SET NOCOUNT ON;

-- Query
SELECT
    [CustomerId],
    [OrderDate],
    [TotalAmount]
FROM [dbo].[Orders] WITH(NOLOCK)
WHERE [CustomerId] = 123
OPTION (RECOMPILE);

-- Covering Index
CREATE NONCLUSTERED INDEX [IX_Orders_Covering]
ON [dbo].[Orders] ([CustomerId])
INCLUDE ([OrderDate], [TotalAmount])

Finding Missing Indexes

SET NOCOUNT ON;

SELECT
    OBJECT_NAME(d.[object_id]) AS [TableName],
    d.[equality_columns] AS [EqualityColumns],
    d.[inequality_columns] AS [InequalityColumns],
    d.[included_columns] AS [IncludedColumns],
    s.[avg_user_impact] AS [AvgUserImpact],
    s.[user_seeks] AS [UserSeeks]
FROM [sys].[dm_db_missing_index_details] d WITH(NOLOCK)
INNER JOIN [sys].[dm_db_missing_index_groups] g WITH(NOLOCK)
    ON d.[index_handle] = g.[index_handle]
INNER JOIN [sys].[dm_db_missing_index_group_stats] s WITH(NOLOCK)
    ON g.[index_group_handle] = s.[group_handle]
ORDER BY s.[avg_user_impact] * s.[user_seeks] DESC
OPTION (RECOMPILE);

Identifying Unused Indexes

SET NOCOUNT ON;

SELECT
    OBJECT_NAME(s.[object_id]) AS [TableName],
    i.[name] AS [IndexName],
    s.[user_seeks] AS [UserSeeks],
    s.[user_scans] AS [UserScans],
    s.[user_updates] AS [UserUpdates]
FROM [sys].[dm_db_index_usage_stats] s WITH(NOLOCK)
INNER JOIN [sys].[indexes] i WITH(NOLOCK)
    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_updates] > 0
ORDER BY s.[user_updates] DESC
OPTION (RECOMPILE);

Index Fragmentation

SET NOCOUNT ON;

SELECT
    OBJECT_NAME(ips.[object_id]) AS [TableName],
    i.[name] AS [IndexName],
    ips.[avg_fragmentation_in_percent] AS [AvgFragmentationPercent]
FROM [sys].[dm_db_index_physical_stats](
    DB_ID(), NULL, NULL, NULL, N'LIMITED'
) ips
INNER JOIN [sys].[indexes] i WITH(NOLOCK)
    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
OPTION (RECOMPILE);

Best Practices

1. •Index columns in WHERE, JOIN, ORDER BY
2. •Use INCLUDE for SELECT columns
3. •Monitor index usage regularly
4. •Rebuild fragmented indexes
5. •Avoid over-indexing (impacts INSERTs/UPDATEs)