Contents

About the Authors

About the Technical Reviewer

Introduction

 Chapter 1: Index Fundamentals

Why Build Indexes?

Major Index Types

Heap Tables

Clustered Indexes

Nonclustered Indexes

Columnstore Indexes

Other Index Types

XML Indexes

Spatial Indexes

Hash and Range Indexes

Full-Text Search

Index Variations

Primary Key

Unique Index

Included Columns

Partitioned Indexes

Filtered Indexes

Compression and Indexing

Index Data Definition Language

Creating an Index

Altering an Index

Dropping an Index

Index Metadata

sys.indexes

sys.index_columns

sys.xml:indexes

sys.selective_xml:index_paths

sys.spatial_indexes

sys.column_store_dictionaries

sys.column_store_segments

sys.hash_indexes

sys.fulltext_catalogs

sys.fulltext_indexes

sys.fulltext_index_columns

Summary

 Chapter 2: Index Storage Fundamentals

Storage Basics

Pages

Extents

Page Types

File Header Page

Boot Page

Page Free Space Page

Global Allocation Map Page

Shared Global Allocation Map Page

Differential Changed Map Page

Bulk Changed Map Page

Index Allocation Map Page

Data Page

Index Page

Large Object Page

Organizing Pages

Heap Structure

B-Tree Structure

Columnstore Structure

Examining Pages

DBCC EXTENTINFO

DBCC IND

sys.dm_db_database_page_allocations

DBCC PAGE

Page Fragmentation

Forwarded Records

Page Splits

Index Characteristics

Heap

Clustered Index

Nonclustered Index

Columnstore Index

Summary

 Chapter 3: Index Metadata and Statistics

Index-Level Statistics

DBCC SHOW_STATISTICS

Catalog Views

STATS_DATE

sys.dm_db_stats_properties

Statistics DDL

Index-Level Statistics Summary

Index Usage Statistics

Header Columns

User Columns

System Columns

Index Usage Stats Summary

Index Operational Statistics

Header Columns

DML Activity

SELECT Activity

Locking Contention

Latch Contention

Page Allocation Cycle

Compression

LOB Access

Index Operational Stats Summary

Index Physical Statistics

Header Columns

Row Statistics

Fragmentation Statistics

Index Physical Stats Summary

Summary

 Chapter 4: XML Indexes

XML Indexing

Benefits

Cautions

Categories

Creating an XML Index

Effects on Query Optimizer

Selective XML Indexes

Summary

 Chapter 5: Spatial Indexing

How Spatial Data Is Indexed

Creating Spatial Indexes

Supporting Methods with Indexes

Understanding Statistics, Properties, and Information

The Views

The Procedures

Tuning Spatial Indexes

Restrictions on Spatial Indexes

Summary

 Chapter 6: Full-Text Indexing

Full-Text Indexing

Creating a Full-Text Example

Creating a Full-Text Catalog

Creating a Full-Text Index

Full-Text Search Index Catalog Views and Properties

Summary

 Chapter 7: Indexing Memory-Optimized Tables

Memory-Optimized Tables Overview

Hash Indexes

Range Indexes

Summary

 Chapter 8: Indexing Myths and Best Practices

Index Myths

Myth 1: Databases Don’t Need Indexes

Myth 2: Primary Keys Are Always Clustered

Myth 3: Online Index Operations Don’t Block

Myth 4: Any Column Can Be Filtered in Multicolumn Indexes

Myth 5: Clustered Indexes Store Records in Physical Order

Myth 6: Indexes Always Output in the Same Order

Myth 7: Fill Factor Is Applied to Indexes During Inserts

Myth 8: Deleting Form Heaps Results in Unrecoverable Space

Myth 9: Every Table Should Have a Heap/Clustered Index

Index Best Practices

Use Clustered Indexes on Primary Keys by Default

Balance Index Count

Specify Fill Factors

Index Foreign Key Columns

Index to Your Environment

Summary

 Chapter 9: Index Maintenance

Index Fragmentation

Fragmentation Operations

Fragmentation Variants

Fragmentation Issues

Defragmentation Options

Defragmentation Strategies

Preventing Fragmentation

Index Statistics Maintenance

Automatically Maintaining Statistics

Manually Maintaining Statistics

Summary

 Chapter 10: Indexing Tools

Missing Index DMOs

Explaining the DMOs

Using the DMOs

Database Engine Tuning Advisor

Explaining the DTA

Using the DTA GUI

Using the DTA Utility

Summary

 Chapter 11: Indexing Strategies

Heaps

Temporary Objects

Other Heap Scenarios

Clustered Indexes

Identity Sequence

Surrogate Key

Foreign Key

Multiple Column

Globally Unique Identifier

Nonclustered Indexes

Search Columns

Index Intersection

Multiple Column

Covering Indexes

Included Columns

Filtered Indexes

Foreign Keys

Columnstore Index

Index Storage Strategies

Row Compression

Page Compression

Indexed Views

Summary

 Chapter 12: Query Strategies

LIKE Comparison

Concatenation

Computed Columns

Scalar Functions

Data Conversion

Summary

 Chapter 13: Monitoring Indexes

Performance Counters

Dynamic Management Objects

Index Usage Stats

Index Operational Stats

Index Physical Stats

Wait Statistics

Data Cleanup

Event Tracing

SQL Trace

Extended Events

Summary

 Chapter 14: Index Analysis

Review of Server State

Performance Counters

Wait Statistics

Buffer Allocation

Schema Discovery

Identify Heaps

Duplicate Indexes

Overlapping Indexes

Unindexed Foreign Keys

Database Engine Tuning Advisor

Unused Indexes

Index Plan Usage

Summary

 Chapter 15: Indexing Methodology

The Indexing Method

Implement

Communication

Deployment Scripts

Execution

Repeat

Summary

Index