Expert Performance Indexing for SQL Server 2012

Expert Performance Indexing for SQL Server 2012......Page 333
 Contents at a Glance......Page 3
 Contents......Page 336
 About the Authors......Page 343
 About the Technical Reviewers......Page 344
 Acknowledgments......Page 345
 Introduction......Page 4
  Why Build Indexes?......Page 6
   Clustered Indexes......Page 7
   Column Store Indexes......Page 8
   Spatial Indexes......Page 9
   Unique Index......Page 10
   Filtered Indexes......Page 11
  Index Data Definition Language......Page 12
   Creating an Index......Page 13
   Dropping an Index......Page 15
   sys.indexes......Page 16
  Summary......Page 17
   Pages......Page 19
   Extents......Page 20
  Page Types......Page 21
   File Header Page......Page 22
   Global Allocation Map Page......Page 23
   Bulk Changed Map Page......Page 24
   Large Object Page......Page 25
   Heap Structure......Page 26
   B-Tree Structure......Page 28
   Column Store Structure......Page 29
   Dbcc Extentinfo......Page 31
   Dbcc Ind......Page 35
   DBCC PAGE......Page 39
    Page Header Only Print Option......Page 41
    Hex Rows Print Option......Page 43
    Hex Data Print Option......Page 44
    Row Data Print Option......Page 46
  Page Fragmentation......Page 47
   Forwarded Records......Page 48
   Page Splits......Page 49
   Non-Clustered Index......Page 52
  Summary......Page 53
   DBCC SHOW_STATISTICS......Page 54
    Histogram......Page 55
    Density Vector......Page 58
    sys.stat_columns......Page 59
  Usage Statistics......Page 60
   User Columns......Page 61
   System Columns......Page 67
  Operational Statistics......Page 69
   DML Activity......Page 70
    Range Scan......Page 73
    Forwarded Fetch......Page 74
   Locking Contention......Page 75
    Row Lock......Page 76
    Page Lock......Page 77
    Lock Escalation......Page 78
   Latch Contention......Page 79
    Page I/O Latch......Page 80
    Page Latch......Page 82
   Page Allocation Cycle......Page 83
   Compression......Page 85
   LOB Access......Page 86
  Physical Statistics......Page 88
   Header Columns......Page 89
   Row Statistics......Page 90
   Fragmentation Statistics......Page 91
  Summary......Page 92
   Benefits......Page 93
   Creating an XML Index......Page 94
   Effects on Execution Plans......Page 97
    Effects from a Secondary Index......Page 99
   How Spatial Data Is Indexed......Page 103
   Creating Spatial Indexes......Page 104
   Supporting Methods with Indexes......Page 109
    The Views......Page 111
    The Procedures......Page 112
   Restrictions on Spatial Indexes......Page 113
   Creating a Full-Text Example......Page 114
    Syntax......Page 115
    StopLists......Page 117
   Full-Text Search Index Catalog Views and Properties......Page 120
  Summary......Page 122
  Index Myths......Page 123
   Myth 1: Databases Don’t Need Indexes......Page 124
   Myth 2: Primary Keys Are Always Clustered......Page 125
   Myth 3: Online Index Operations Don’t Block......Page 127
   Myth 4: Any Column Can Be Filtered In Multicolumn Indexes......Page 129
   Myth 5: Clustered Indexes Store Records in Physical Order......Page 131
   Myth 6: Fill Factor Is Applied to Indexes During Inserts......Page 132
   Myth 7: Every Table Should Have a Heap/Clustered Index......Page 133
  Index Best Practices......Page 134
    Database Level Fill Factor......Page 135
  Summary......Page 136
   Fragmentation Operations......Page 137
    Insert Operations......Page 138
    Update Operations......Page 140
    Delete Operations......Page 142
    Shrink Operations......Page 144
    Index I/O......Page 146
   Defragmentation Options......Page 148
    Index Rebuild......Page 149
    Index Reorganization......Page 150
     Reorganize Index Task......Page 151
     Rebuild Index Task......Page 152
    T-SQL Scripts......Page 154
    Fill Factor......Page 158
    Default Values......Page 159
    Automatic Updating......Page 160
    Maintenance Plans......Page 161
     DDL Command......Page 163
  Summary......Page 165
  Missing Index DMOs......Page 166
    sys.dm_db_missing_index_details......Page 167
    sys.dm_db_missing_index_columns......Page 168
    sys.dm_db_missing_index_group_stats......Page 169
   Using the DMOs......Page 170
  Database Engine Tuning Advisor......Page 173
   Explaining DTA......Page 174
   Using the DTA GUI......Page 175
   Using the DTA Utility......Page 179
  Summary......Page 187
   Temporary Objects......Page 188
   Other Heap Scenarios......Page 190
  Clustered Indexes......Page 191
   Identity Column......Page 192
   Surrogate Key......Page 193
   Foreign Key......Page 195
   Multiple Column......Page 199
   Globally Unique Identifier......Page 203
  Non-Clustered Indexes......Page 205
   Search Columns......Page 206
   Index Intersection......Page 208
   Multiple Columns......Page 211
   Covering Index......Page 212
   Included Columns......Page 213
   Filtered Indexes......Page 217
   Foreign Keys......Page 220
  ColumnStore Index......Page 224
  Index Storage Strategies......Page 226
   Row Compression......Page 227
   Page Compression......Page 229
  Indexed Views......Page 232
  Summary......Page 235
  LIKE Comparison......Page 236
  Concatenation......Page 239
  Computed Columns......Page 241
  Scalar Functions......Page 243
  Data Conversion......Page 245
  Summary......Page 248
  Indexing Method......Page 250
  Monitor......Page 251
   Performance Counters......Page 252
    Index Usage Stats......Page 257
    Index Operational Stats......Page 261
    Index Physical Stats......Page 269
    Wait Statistics......Page 271
    Data Cleanup......Page 274
   SQL Trace......Page 275
  Analyze......Page 279
    Performance Counters......Page 280
     Access Methods\Forwarded Records/sec......Page 281
     Access Methods\FreeSpace Scans/sec......Page 284
     Access Methods\Full Scans/sec......Page 286
     Access Methods\Index Searches/sec......Page 288
     Access Methods\Page Splits/sec......Page 290
     Buffer Manager\Page lookups/sec......Page 292
     Locks(*)\Lock Wait Time (ms)......Page 294
     Locks(*)\Lock Waits/sec......Page 296
     Locks(*)\Number of Deadlocks/sec......Page 298
    Wait Statistics......Page 300
     Cxpacket......Page 302
     LCK_M_*......Page 306
     PAGEIOLATCH_*......Page 307
    Buffer Allocation......Page 308
    Identify Heaps......Page 310
    Duplicate Indexes......Page 311
    Overlapping Indexes......Page 313
    Unindexed Foreign Keys......Page 314
   Database Engine Tuning Advisor......Page 316
   Unused Indexes......Page 319
   Index Plan Usage......Page 320
   Communication......Page 321
    Status Report......Page 322
    Peer Review with Impact Analysis......Page 323
  Repeat......Page 324
  Summary......Page 325
 Index......Page 326