دانلود کتاب Expert Performance Indexing in Azure SQL and SQL Server 2022: Toward Faster Results and Lower Maintenance Both on Premises and in the Cloud

فهرست مطالب :

Table of Contents About the Authors About the Technical Reviewer Acknowledgments Introduction Chapter 1: Index Fundamentals Why Build Indexes? Major Index Types Heap Tables Clustered Indexes Non-clustered Indexes Other Index Types Columnstore Indexes JSON and 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.index_resumable_operations sys.xml_indexes sys.selective_xml_index_paths sys.selective_xml_index_namespaces sys.spatial_indexes sys.spatial_index_tessellations sys.column_store_dictionaries sys.column_store_segments sys.column_store_row_groups 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 Minimally Logged Page Index Allocation Map Page Data Page Index Page Large Object Page Organizing Pages Heap Structure B-Tree Structure Columnstore Structure Minimally Logged Bulk-Loading Data The Deltastore The Delete Bitmap Index Characteristics Heap Clustered Index Non-clustered Index Columnstore Index Summary Chapter 3: Examining Index Contents Examining Pages Dynamic Management Functions sys.dm_db_database_page_allocations sys.dm_db_page_info DBCC Commands DBCC EXTENTINFO DBCC IND DBCC PAGE Page Header–Only Print Option Hex Rows Print Option Hex Data Print Option Row Data Print Option Summary Chapter 4: Fragmentation Page Fragmentation Forwarded Records Page Splits Summary Chapter 5: Index Metadata and Statistics Column-Level Statistics DBCC SHOW_STATISTICS Stats Header Density Vector Histogram Catalog Views sys.stats sys.stats_columns STATS_DATE sys.dm_db_stats_properties sys.dm_db_stats_histogram sys.dm_db_incremental_stats_properties Statistics DDL Column-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 Range Scan Singleton Lookup Forwarded Fetch Locking Contention Row Lock Page Lock Lock Escalation Latch Contention Page I/O Latch Page Latch Page Allocation Cycle Compression LOB Access Row Version Index Operational Stats Summary Index Physical Statistics Header Columns Row Statistics Fragmentation Statistics Index Physical Stats Summary Columnstore Statistics Columnstore Physical Stats Header Columns Statistics Columns Columnstore Operational Stats Header Columns Statistics Columns Summary Untitled Untitled Chapter 6: XML Indexes XML Data Benefits Cautions XML Indexes Primary/Secondary XML Indexes Primary XML Index Secondary XML Index Selective XML Indexes Summary Chapter 7: Spatial Indexing How Spatial Data Is Indexed Creating Spatial Indexes Supporting Methods with Indexes Understanding Statistics, Properties, and Information The Views Stored Procedures Tuning Spatial Indexes Restrictions on Spatial Indexes Summary Chapter 8: Indexing Memory-Optimized Tables Memory-Optimized Tables Overview Hash Indexes Range Indexes Summary Chapter 9: Full-Text Indexing Full-Text Indexing Creating a Full-Text Example Creating a Full-Text Catalog Creating a Full-Text Index Syntax Key Indexes Population StopLists Full-Text Search Index Catalog Views and Properties Summary Chapter 10: Indexing Myths and Best Practices Index Myths Myth 1: Databases Do Not 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 Rows in the Same Order Myth 7: Fill Factor Is Applied to Indexes During Inserts Myth 8: Deleting from Heaps Results in Unrecoverable Space Myth 9: Every Table Should Have a Heap/Clustered Index Index Best Practices Index to the Current Workload Use Clustered Indexes on Primary Keys by Default Specify Fill Factors Database-Level Fill Factor Index-Level Fill Factor Index Foreign Key Columns Balance Index Count Summary Chapter 11: Index Maintenance Index Fragmentation Fragmentation Operations Insert Operations Update Operations Delete Operations Shrink Operations Fragmentation Variants Heap Bloat and Forwarding Columnstore Fragmentation Fragmentation Issues Index I/O Contiguous Reads Defragmentation Options Index Rebuild Index Reorganization Drop and Create Defragmentation Strategies Maintenance Plans Reorganize Index Task Rebuild Index Task Maintenance Plan Summary T-SQL Scripts Preventing Fragmentation Fill Factor Data Typing Default Values Index Statistics Maintenance Automatically Maintaining Statistics Automatic Creation Automatic Updating Preventing Auto-update Memory-Optimized Table Statistics Manually Maintaining Statistics Maintenance Plans T-SQL Scripts Stored Procedure DDL Command Summary Untitled Chapter 12: Indexing Tools Missing Indexes Explaining the DMOs sys.dm_db_missing_index_details sys.dm_db_missing_index_columns sys.dm_db_missing_index_groups sys.dm_db_missing_index_group_stats Using the DMOs Database Engine Tuning Advisor Explaining the DTA Using the DTA GUI Using the DTA Utility Summary Chapter 13: Indexing Strategies Heaps Temporary Objects Other Heap Scenarios Clustered Indexes Identity Sequence Natural Key Foreign Key Multiple Column Globally Unique Identifier Non-clustered Indexes Search Columns Index Intersection Multiple Column Covering Index Included Columns Filtered Indexes Foreign Keys Columnstore Index JSON Indexing Index Storage Strategies Row Compression Page Compression Indexed Views Summary Untitled Chapter 14: Query Strategies LIKE Comparison Concatenation Computed Columns Scalar Functions Data Conversion Summary Chapter 15: 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 Query Store Summary Chapter 16: Index Analysis Review of Server State Performance Counters Forwarded Records per Second FreeSpace Scans and Page Fetches per Second Full Scans per Second Index Searches per Second Page Splits per Second Page Lookups per Second Page Compression Lock Wait Time Lock Waits per Second Number of Deadlocks per Second Wait Statistics CXPACKET IO_COMPLETION LCK_M_∗ PAGEIOLATCH_∗ Buffer Allocation Schema Discovery Identify Heaps Duplicate Indexes Overlapping Indexes Unindexed Foreign Keys Uncompressed Indexes Database Engine Tuning Advisor Unused Indexes Index Plan Usage Summary Chapter 17: Indexing Methodology The Indexing Method Implement Communication Impact Analysis Status Report Deployment Scripts Prepare Deployment and Rollback of Schema Save Index Changes to Source Code Repository Peer Review with Impact Analysis Execution Repeat Summary Index