Expert Performance Indexing in SQL Server, Second Edition

Expert Performance Indexing in SQL Server, Second Edition

Released October 2015
Publisher(s): Apress
ISBN: 9781484211182

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

This book is a deep dive into perhaps the single-most important facet of good performance: indexes, and how to best use them. The book begins in the shallow waters with explanations of the types of indexes and how they are stored in databases. Moving deeper into the topic, and further into the book, you will look at the statistics that are accumulated both by indexes and on indexes. You’ll better understand what indexes are doing in the database and what can be done to mitigate and improve their effect on performance. The final destination is a guided tour through a number of real life scenarios showing approaches you can take to investigate, mitigate, and improve the performance of your database.

  • • Defines the types of indexes and their implementation options
  • • Provides use cases and common patterns in applying indexing
  • • Describes and explain the index metadata and statistics
  • • Provides a framework of strategies and approaches for indexing databases
    •

    Table of contents

    1. Cover
    2. Title
    3. Copyright
    4. Dedication
    5. Contents at a Glance
    6. Contents
    7. About the Authors
    8. About the Technical Reviewer
    9. Introduction
    10. Chapter 1: Index Fundamentals
      1. Why Build Indexes?
      2. Major Index Types
        1. Heap Tables
        2. Clustered Indexes
        3. Nonclustered Indexes
        4. Columnstore Indexes
      3. Other Index Types
        1. XML Indexes
        2. Spatial Indexes
        3. Hash and Range Indexes
        4. Full-Text Search
      4. Index Variations
        1. Primary Key
        2. Unique Index
        3. Included Columns
        4. Partitioned Indexes
        5. Filtered Indexes
      5. Compression and Indexing
      6. Index Data Definition Language
        1. Creating an Index
        2. Altering an Index
        3. Dropping an Index
      7. Index Metadata
        1. sys.indexes
        2. sys.index_columns
        3. sys.xml:indexes
        4. sys.selective_xml:index_paths
        5. sys.spatial_indexes
        6. sys.column_store_dictionaries
        7. sys.column_store_segments
        8. sys.hash_indexes
        9. sys.fulltext_catalogs
        10. sys.fulltext_indexes
        11. sys.fulltext_index_columns
      8. Summary
    11. Chapter 2: Index Storage Fundamentals
      1. Storage Basics
        1. Pages
        2. Extents
      2. Page Types
        1. File Header Page
        2. Boot Page
        3. Page Free Space Page
        4. Global Allocation Map Page
        5. Shared Global Allocation Map Page
        6. Differential Changed Map Page
        7. Bulk Changed Map Page
        8. Index Allocation Map Page
        9. Data Page
        10. Index Page
        11. Large Object Page
      3. Organizing Pages
        1. Heap Structure
        2. B-Tree Structure
        3. Columnstore Structure
      4. Examining Pages
        1. DBCC EXTENTINFO
        2. DBCC IND
        3. sys.dm_db_database_page_allocations
        4. DBCC PAGE
      5. Page Fragmentation
        1. Forwarded Records
        2. Page Splits
      6. Index Characteristics
        1. Heap
        2. Clustered Index
        3. Nonclustered Index
        4. Columnstore Index
      7. Summary
    12. Chapter 3: Index Metadata and Statistics
      1. Index-Level Statistics
        1. DBCC SHOW_STATISTICS
        2. Catalog Views
        3. STATS_DATE
        4. sys.dm_db_stats_properties
        5. Statistics DDL
        6. Index-Level Statistics Summary
      2. Index Usage Statistics
        1. Header Columns
        2. User Columns
        3. System Columns
        4. Index Usage Stats Summary
      3. Index Operational Statistics
        1. Header Columns
        2. DML Activity
        3. SELECT Activity
        4. Locking Contention
        5. Latch Contention
        6. Page Allocation Cycle
        7. Compression
        8. LOB Access
        9. Index Operational Stats Summary
      4. Index Physical Statistics
        1. Header Columns
        2. Row Statistics
        3. Fragmentation Statistics
        4. Index Physical Stats Summary
      5. Summary
    13. Chapter 4: XML Indexes
      1. XML Indexing
        1. Benefits
        2. Cautions
        3. Categories
        4. Creating an XML Index
        5. Effects on Query Optimizer
        6. Selective XML Indexes
      2. Summary
    14. Chapter 5: Spatial Indexing
      1. How Spatial Data Is Indexed
      2. Creating Spatial Indexes
      3. Supporting Methods with Indexes
      4. Understanding Statistics, Properties, and Information
        1. The Views
        2. The Procedures
      5. Tuning Spatial Indexes
      6. Restrictions on Spatial Indexes
      7. Summary
    15. Chapter 6: Full-Text Indexing
      1. Full-Text Indexing
        1. Creating a Full-Text Example
        2. Creating a Full-Text Catalog
        3. Creating a Full-Text Index
        4. Full-Text Search Index Catalog Views and Properties
      2. Summary
    16. Chapter 7: Indexing Memory-Optimized Tables
      1. Memory-Optimized Tables Overview
      2. Hash Indexes
      3. Range Indexes
      4. Summary
    17. Chapter 8: Indexing Myths and Best Practices
      1. Index Myths
        1. Myth 1: Databases Don’t Need Indexes
        2. Myth 2: Primary Keys Are Always Clustered
        3. Myth 3: Online Index Operations Don’t Block
        4. Myth 4: Any Column Can Be Filtered in Multicolumn Indexes
        5. Myth 5: Clustered Indexes Store Records in Physical Order
        6. Myth 6: Indexes Always Output in the Same Order
        7. Myth 7: Fill Factor Is Applied to Indexes During Inserts
        8. Myth 8: Deleting Form Heaps Results in Unrecoverable Space
        9. Myth 9: Every Table Should Have a Heap/Clustered Index
      2. Index Best Practices
        1. Use Clustered Indexes on Primary Keys by Default
        2. Balance Index Count
        3. Specify Fill Factors
        4. Index Foreign Key Columns
        5. Index to Your Environment
      3. Summary
    18. Chapter 9: Index Maintenance
      1. Index Fragmentation
        1. Fragmentation Operations
        2. Fragmentation Variants
        3. Fragmentation Issues
        4. Defragmentation Options
        5. Defragmentation Strategies
        6. Preventing Fragmentation
      2. Index Statistics Maintenance
        1. Automatically Maintaining Statistics
        2. Manually Maintaining Statistics
      3. Summary
    19. Chapter 10: Indexing Tools
      1. Missing Index DMOs
        1. Explaining the DMOs
        2. Using the DMOs
      2. Database Engine Tuning Advisor
        1. Explaining the DTA
        2. Using the DTA GUI
        3. Using the DTA Utility
      3. Summary
    20. Chapter 11: Indexing Strategies
      1. Heaps
        1. Temporary Objects
        2. Other Heap Scenarios
      2. Clustered Indexes
        1. Identity Sequence
        2. Surrogate Key
        3. Foreign Key
        4. Multiple Column
        5. Globally Unique Identifier
      3. Nonclustered Indexes
        1. Search Columns
        2. Index Intersection
        3. Multiple Column
        4. Covering Indexes
        5. Included Columns
        6. Filtered Indexes
        7. Foreign Keys
      4. Columnstore Index
      5. Index Storage Strategies
        1. Row Compression
        2. Page Compression
      6. Indexed Views
      7. Summary
    21. Chapter 12: Query Strategies
      1. LIKE Comparison
      2. Concatenation
      3. Computed Columns
      4. Scalar Functions
      5. Data Conversion
      6. Summary
    22. Chapter 13: Monitoring Indexes
      1. Performance Counters
      2. Dynamic Management Objects
        1. Index Usage Stats
        2. Index Operational Stats
        3. Index Physical Stats
        4. Wait Statistics
        5. Data Cleanup
      3. Event Tracing
        1. SQL Trace
        2. Extended Events
      4. Summary
    23. Chapter 14: Index Analysis
      1. Review of Server State
        1. Performance Counters
        2. Wait Statistics
        3. Buffer Allocation
      2. Schema Discovery
        1. Identify Heaps
        2. Duplicate Indexes
        3. Overlapping Indexes
        4. Unindexed Foreign Keys
      3. Database Engine Tuning Advisor
      4. Unused Indexes
      5. Index Plan Usage
      6. Summary
    24. Chapter 15: Indexing Methodology
      1. The Indexing Method
      2. Implement
        1. Communication
        2. Deployment Scripts
        3. Execution
      3. Repeat
      4. Summary
    25. Index

    Product information

    • Title: Expert Performance Indexing in SQL Server, Second Edition
    • Author(s):
    • Release date: October 2015
    • Publisher(s): Apress
    • ISBN: 9781484211182