Home Page Icon
Home Page
Table of Contents for
Cover
Close
Cover
by Grant Fritchey, Jason Strate
Expert Performance Indexing in SQL Server, Second Edition
Cover
Title
Copyright
Dedication
Contents at a Glance
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
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Next
Next Chapter
Title
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset