Expert Performance Indexing in SQL Server (Jason Strate, Grant Fritchey (auth.)) (z-library.sk, 1lib.sk, z-lib.sk)
SQLAuthor:Jason Strate, Grant Fritchey (auth.)
No description
AI Reading Assistant
Summary and highlights from this book's index; jump to passages in the text
Tags
Support Statistics
¥.00 ·
0times
Text Preview (First 20 pages)
Registered users can read the full content for free
Register as a Gaohf Library member to read the complete e-book online for free and enjoy a better reading experience.
Page
1
Strate Fritchey SECOND EDITION Shelve in: Databases/MS SQL Server User level: Intermediate–Advanced www.apress.com RELATED BOOKS FOR PROFESSIONALS BY PROFESSIONALS® Expert Performance Indexing in SQL Server 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 SOURCE CODE ONLINE 9 781484 211199 54999 ISBN 978-1-4842-1119-9
Page
2
Expert Performance Indexing in SQL Server Second Edition Jason Strate Grant Fritchey
Page
3
Expert Performance Indexing in SQL Server Copyright © 2015 by Jason Strate and Grant Fritchey This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting, reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed. Exempted from this legal reservation are brief excerpts in connection with reviews or scholarly analysis or material supplied specifically for the purpose of being entered and executed on a computer system, for exclusive use by the purchaser of the work. Duplication of this publication or parts thereof is permitted only under the provisions of the Copyright Law of the Publisher’s location, in its current version, and permission for use must always be obtained from Springer. Permissions for use may be obtained through RightsLink at the Copyright Clearance Center. Violations are liable to prosecution under the respective Copyright Law. ISBN-13 (pbk): 978-1-4842-1119-9 ISBN-13 (electronic): 978-1-4842-1118-2 Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights. While the advice and information in this book are believed to be true and accurate at the date of publication, neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may be made. The publisher makes no warranty, express or implied, with respect to the material contained herein. Managing Director: Welmoed Spahr Lead Editor: Jonathan Gennick Technical Reviewer: Rodney Landrum Editorial Board: Steve Anglin, Mark Beckner, Gary Cornell, Louise Corrigan, Jim DeWolf, Jonathan Gennick, Robert Hutchinson, Michelle Lowman, James Markham, Susan McDermott, Matthew Moodie, Jeffrey Pepper, Douglas Pundick, Ben Renow-Clarke, Gwenan Spearing, Matt Wade, Steve Weiss Coordinating Editor: Jill Balzano Copy Editor: Kim Wimpsett Compositor: SPi Global Indexer: SPi Global Artist: SPi Global Cover Designer: Anna Ishchenko Distributed to the book trade worldwide by Springer Science+Business Media New York, 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail orders-ny@springer-sbm.com, or visit www.springer.com. Apress Media, LLC is a California LLC and the sole member (owner) is Springer Science + Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a Delaware corporation. For information on translations, please e-mail rights@apress.com, or visit www.apress.com. Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Special Bulk Sales–eBook Licensing web page at www.apress.com/bulk-sales. Any source code or other supplementary material referenced by the author in this text is available to readers at www.apress.com. For detailed information about how to locate your book’s source code, go to www.apress.com/source-code/.
Page
4
For Nikolai, Aspen, Dysin, Grace, and Michael, for giving up a weekend or two while I worked on putting this together. —Jason Strate This one goes to Jason Strate, who had faith that I could help with his baby. I appreciate everything you’ve done here, Jason, and I sure hope I helped out a little. —Grant Fritchey
Page
5
v Contents at a Glance About the Authors ��������������������������������������������������������������������������������������������������xvii About the Technical Reviewer ��������������������������������������������������������������������������������xix Introduction ������������������������������������������������������������������������������������������������������������xxi ■Chapter 1: Index Fundamentals ���������������������������������������������������������������������������� 1 ■Chapter 2: Index Storage Fundamentals ������������������������������������������������������������� 15 ■Chapter 3: Index Metadata and Statistics ����������������������������������������������������������� 55 ■Chapter 4: XML Indexes ������������������������������������������������������������������������������������� 101 ■Chapter 5: Spatial Indexing ������������������������������������������������������������������������������� 115 ■Chapter 6: Full-Text Indexing ���������������������������������������������������������������������������� 131 ■Chapter 7: Indexing Memory-Optimized Tables������������������������������������������������� 141 ■Chapter 8: Indexing Myths and Best Practices ������������������������������������������������� 153 ■Chapter 9: Index Maintenance ��������������������������������������������������������������������������� 175 ■Chapter 10: Indexing Tools �������������������������������������������������������������������������������� 213 ■Chapter 11: Indexing Strategies ������������������������������������������������������������������������ 237 ■Chapter 12: Query Strategies ���������������������������������������������������������������������������� 293 ■Chapter 13: Monitoring Indexes ������������������������������������������������������������������������ 311 ■Chapter 14: Index Analysis �������������������������������������������������������������������������������� 345 ■Chapter 15: Indexing Methodology �������������������������������������������������������������������� 391 Index ��������������������������������������������������������������������������������������������������������������������� 397
Page
6
vii Contents About the Authors ��������������������������������������������������������������������������������������������������xvii About the Technical Reviewer ��������������������������������������������������������������������������������xix Introduction ������������������������������������������������������������������������������������������������������������xxi ■Chapter 1: Index Fundamentals ���������������������������������������������������������������������������� 1 Why Build Indexes? ���������������������������������������������������������������������������������������������������������� 1 Major Index Types ������������������������������������������������������������������������������������������������������������� 2 Heap Tables �������������������������������������������������������������������������������������������������������������������������������������������� 2 Clustered Indexes ����������������������������������������������������������������������������������������������������������������������������������� 2 Nonclustered Indexes ����������������������������������������������������������������������������������������������������������������������������� 3 Columnstore Indexes ������������������������������������������������������������������������������������������������������������������������������ 3 Other Index Types ������������������������������������������������������������������������������������������������������������� 4 XML Indexes ������������������������������������������������������������������������������������������������������������������������������������������� 4 Spatial Indexes ��������������������������������������������������������������������������������������������������������������������������������������� 5 Hash and Range Indexes ������������������������������������������������������������������������������������������������������������������������ 5 Full-Text Search �������������������������������������������������������������������������������������������������������������������������������������� 6 Index Variations ���������������������������������������������������������������������������������������������������������������� 6 Primary Key �������������������������������������������������������������������������������������������������������������������������������������������� 6 Unique Index ������������������������������������������������������������������������������������������������������������������������������������������� 6 Included Columns ����������������������������������������������������������������������������������������������������������������������������������� 7 Partitioned Indexes ��������������������������������������������������������������������������������������������������������������������������������� 7 Filtered Indexes �������������������������������������������������������������������������������������������������������������������������������������� 7
Page
7
viii ■ Contents Compression and Indexing ����������������������������������������������������������������������������������������������� 8 Index Data Definition Language ��������������������������������������������������������������������������������������� 8 Creating an Index ������������������������������������������������������������������������������������������������������������������������������������ 8 Altering an Index ����������������������������������������������������������������������������������������������������������������������������������� 11 Dropping an Index �������������������������������������������������������������������������������������������������������������������������������� 12 Index Metadata ��������������������������������������������������������������������������������������������������������������� 12 sys�indexes ������������������������������������������������������������������������������������������������������������������������������������������� 13 sys�index_columns ������������������������������������������������������������������������������������������������������������������������������� 13 sys�xml:indexes ������������������������������������������������������������������������������������������������������������������������������������ 13 sys�selective_xml:index_paths ������������������������������������������������������������������������������������������������������������� 13 sys�spatial_indexes ������������������������������������������������������������������������������������������������������������������������������ 13 sys�column_store_dictionaries������������������������������������������������������������������������������������������������������������� 13 sys�column_store_segments���������������������������������������������������������������������������������������������������������������� 13 sys�hash_indexes ��������������������������������������������������������������������������������������������������������������������������������� 14 sys�fulltext_catalogs ����������������������������������������������������������������������������������������������������������������������������� 14 sys�fulltext_indexes ������������������������������������������������������������������������������������������������������������������������������ 14 sys�fulltext_index_columns ������������������������������������������������������������������������������������������������������������������ 14 Summary ������������������������������������������������������������������������������������������������������������������������ 14 ■Chapter 2: Index Storage Fundamentals ������������������������������������������������������������� 15 Storage Basics ��������������������������������������������������������������������������������������������������������������� 15 Pages ���������������������������������������������������������������������������������������������������������������������������������������������������� 15 Extents ������������������������������������������������������������������������������������������������������������������������������������������������� 16 Page Types ��������������������������������������������������������������������������������������������������������������������� 17 File Header Page ���������������������������������������������������������������������������������������������������������������������������������� 18 Boot Page �������������������������������������������������������������������������������������������������������������������������������������������� 18 Page Free Space Page �������������������������������������������������������������������������������������������������������������������������� 19 Global Allocation Map Page ������������������������������������������������������������������������������������������������������������������ 19 Shared Global Allocation Map Page ������������������������������������������������������������������������������������������������������ 20 Differential Changed Map Page ������������������������������������������������������������������������������������������������������������ 20 Bulk Changed Map Page����������������������������������������������������������������������������������������������������������������������� 20 Index Allocation Map Page ������������������������������������������������������������������������������������������������������������������� 20
Page
8
ix ■ Contents Data Page ��������������������������������������������������������������������������������������������������������������������������������������������� 21 Index Page �������������������������������������������������������������������������������������������������������������������������������������������� 21 Large Object Page �������������������������������������������������������������������������������������������������������������������������������� 21 Organizing Pages ����������������������������������������������������������������������������������������������������������� 22 Heap Structure ������������������������������������������������������������������������������������������������������������������������������������� 22 B-Tree Structure ����������������������������������������������������������������������������������������������������������������������������������� 24 Columnstore Structure ������������������������������������������������������������������������������������������������������������������������� 25 Examining Pages ������������������������������������������������������������������������������������������������������������ 27 DBCC EXTENTINFO �������������������������������������������������������������������������������������������������������������������������������� 27 DBCC IND ���������������������������������������������������������������������������������������������������������������������������������������������� 32 sys�dm_db_database_page_allocations ���������������������������������������������������������������������������������������������� 36 DBCC PAGE ������������������������������������������������������������������������������������������������������������������������������������������� 39 Page Fragmentation ������������������������������������������������������������������������������������������������������� 48 Forwarded Records ������������������������������������������������������������������������������������������������������������������������������ 48 Page Splits �������������������������������������������������������������������������������������������������������������������������������������������� 50 Index Characteristics ������������������������������������������������������������������������������������������������������ 53 Heap ����������������������������������������������������������������������������������������������������������������������������������������������������� 53 Clustered Index ������������������������������������������������������������������������������������������������������������������������������������� 53 Nonclustered Index ������������������������������������������������������������������������������������������������������������������������������� 53 Columnstore Index �������������������������������������������������������������������������������������������������������������������������������� 54 Summary ������������������������������������������������������������������������������������������������������������������������ 54 ■Chapter 3: Index Metadata and Statistics ����������������������������������������������������������� 55 Index-Level Statistics ����������������������������������������������������������������������������������������������������� 55 DBCC SHOW_STATISTICS ���������������������������������������������������������������������������������������������������������������������� 56 Catalog Views ��������������������������������������������������������������������������������������������������������������������������������������� 60 STATS_DATE ����������������������������������������������������������������������������������������������������������������������������������������� 61 sys�dm_db_stats_properties ���������������������������������������������������������������������������������������������������������������� 62 Statistics DDL ��������������������������������������������������������������������������������������������������������������������������������������� 65 Index-Level Statistics Summary ����������������������������������������������������������������������������������������������������������� 66
Page
9
x ■ Contents Index Usage Statistics ���������������������������������������������������������������������������������������������������� 66 Header Columns ����������������������������������������������������������������������������������������������������������������������������������� 66 User Columns ��������������������������������������������������������������������������������������������������������������������������������������� 67 System Columns ����������������������������������������������������������������������������������������������������������������������������������� 73 Index Usage Stats Summary ���������������������������������������������������������������������������������������������������������������� 75 Index Operational Statistics ������������������������������������������������������������������������������������������� 75 Header Columns ����������������������������������������������������������������������������������������������������������������������������������� 76 DML Activity ������������������������������������������������������������������������������������������������������������������������������������������ 76 SELECT Activity ������������������������������������������������������������������������������������������������������������������������������������� 79 Locking Contention ������������������������������������������������������������������������������������������������������������������������������� 82 Latch Contention ����������������������������������������������������������������������������������������������������������������������������������� 86 Page Allocation Cycle ��������������������������������������������������������������������������������������������������������������������������� 90 Compression ����������������������������������������������������������������������������������������������������������������������������������������� 92 LOB Access ������������������������������������������������������������������������������������������������������������������������������������������� 93 Index Operational Stats Summary �������������������������������������������������������������������������������������������������������� 95 Index Physical Statistics ������������������������������������������������������������������������������������������������� 96 Header Columns ����������������������������������������������������������������������������������������������������������������������������������� 97 Row Statistics ��������������������������������������������������������������������������������������������������������������������������������������� 97 Fragmentation Statistics ���������������������������������������������������������������������������������������������������������������������� 98 Index Physical Stats Summary ������������������������������������������������������������������������������������������������������������� 99 Summary ������������������������������������������������������������������������������������������������������������������������ 99 ■Chapter 4: XML Indexes ������������������������������������������������������������������������������������� 101 XML Indexing ���������������������������������������������������������������������������������������������������������������� 101 Benefits ���������������������������������������������������������������������������������������������������������������������������������������������� 101 Cautions ���������������������������������������������������������������������������������������������������������������������������������������������� 102 Categories ������������������������������������������������������������������������������������������������������������������������������������������ 102 Creating an XML Index ����������������������������������������������������������������������������������������������������������������������� 102 Effects on Query Optimizer ����������������������������������������������������������������������������������������������������������������� 106 Selective XML Indexes ������������������������������������������������������������������������������������������������������������������������ 112 Summary ���������������������������������������������������������������������������������������������������������������������� 114
Page
10
xi ■ Contents ■Chapter 5: Spatial Indexing ������������������������������������������������������������������������������� 115 How Spatial Data Is Indexed ����������������������������������������������������������������������������������������� 115 Creating Spatial Indexes ��������������������������������������������������������������������������������������������� 118 Supporting Methods with Indexes �������������������������������������������������������������������������������� 123 Understanding Statistics, Properties, and Information ������������������������������������������������� 124 The Views ������������������������������������������������������������������������������������������������������������������������������������������� 124 The Procedures ����������������������������������������������������������������������������������������������������������������������������������� 125 Tuning Spatial Indexes ������������������������������������������������������������������������������������������������� 127 Restrictions on Spatial Indexes ������������������������������������������������������������������������������������ 128 Summary ���������������������������������������������������������������������������������������������������������������������� 129 ■Chapter 6: Full-Text Indexing ���������������������������������������������������������������������������� 131 Full-Text Indexing ��������������������������������������������������������������������������������������������������������� 131 Creating a Full-Text Example �������������������������������������������������������������������������������������������������������������� 131 Creating a Full-Text Catalog ��������������������������������������������������������������������������������������������������������������� 132 Creating a Full-Text Index ������������������������������������������������������������������������������������������������������������������� 133 Full-Text Search Index Catalog Views and Properties ������������������������������������������������������������������������ 137 Summary ���������������������������������������������������������������������������������������������������������������������� 140 ■Chapter 7: Indexing Memory-Optimized Tables������������������������������������������������� 141 Memory-Optimized Tables Overview ���������������������������������������������������������������������������� 141 Hash Indexes ���������������������������������������������������������������������������������������������������������������� 145 Range Indexes �������������������������������������������������������������������������������������������������������������� 149 Summary ���������������������������������������������������������������������������������������������������������������������� 151 ■Chapter 8: Indexing Myths and Best Practices ������������������������������������������������� 153 Index Myths ������������������������������������������������������������������������������������������������������������������ 154 Myth 1: Databases Don’t Need Indexes ���������������������������������������������������������������������������������������������� 154 Myth 2: Primary Keys Are Always Clustered ��������������������������������������������������������������������������������������� 156 Myth 3: Online Index Operations Don’t Block�������������������������������������������������������������������������������������� 157 Myth 4: Any Column Can Be Filtered in Multicolumn Indexes ����������������������������������������������������������� 160 Myth 5: Clustered Indexes Store Records in Physical Order ��������������������������������������������������������������� 162 Myth 6: Indexes Always Output in the Same Order ����������������������������������������������������������������������������� 164
Page
11
xii ■ Contents Myth 7: Fill Factor Is Applied to Indexes During Inserts ���������������������������������������������������������������������� 167 Myth 8: Deleting Form Heaps Results in Unrecoverable Space ���������������������������������������������������������� 169 Myth 9: Every Table Should Have a Heap/Clustered Index������������������������������������������������������������������ 171 Index Best Practices ����������������������������������������������������������������������������������������������������� 172 Use Clustered Indexes on Primary Keys by Default ���������������������������������������������������������������������������� 172 Balance Index Count ��������������������������������������������������������������������������������������������������������������������������� 172 Specify Fill Factors ����������������������������������������������������������������������������������������������������������������������������� 173 Index Foreign Key Columns ���������������������������������������������������������������������������������������������������������������� 173 Index to Your Environment ������������������������������������������������������������������������������������������������������������������ 174 Summary ���������������������������������������������������������������������������������������������������������������������� 174 ■Chapter 9: Index Maintenance ��������������������������������������������������������������������������� 175 Index Fragmentation ���������������������������������������������������������������������������������������������������� 175 Fragmentation Operations ������������������������������������������������������������������������������������������������������������������ 176 Fragmentation Variants ���������������������������������������������������������������������������������������������������������������������� 186 Fragmentation Issues ������������������������������������������������������������������������������������������������������������������������� 194 Defragmentation Options �������������������������������������������������������������������������������������������������������������������� 196 Defragmentation Strategies���������������������������������������������������������������������������������������������������������������� 199 Preventing Fragmentation ������������������������������������������������������������������������������������������������������������������ 206 Index Statistics Maintenance ��������������������������������������������������������������������������������������� 207 Automatically Maintaining Statistics �������������������������������������������������������������������������������������������������� 207 Manually Maintaining Statistics ��������������������������������������������������������������������������������������������������������� 209 Summary ���������������������������������������������������������������������������������������������������������������������� 212 ■Chapter 10: Indexing Tools �������������������������������������������������������������������������������� 213 Missing Index DMOs ����������������������������������������������������������������������������������������������������� 213 Explaining the DMOs��������������������������������������������������������������������������������������������������������������������������� 214 Using the DMOs ���������������������������������������������������������������������������������������������������������������������������������� 217 Database Engine Tuning Advisor ���������������������������������������������������������������������������������� 220 Explaining the DTA ������������������������������������������������������������������������������������������������������������������������������ 221 Using the DTA GUI ������������������������������������������������������������������������������������������������������������������������������� 222 Using the DTA Utility ��������������������������������������������������������������������������������������������������������������������������� 226 Summary ���������������������������������������������������������������������������������������������������������������������� 235
Page
12
xiii ■ Contents ■Chapter 11: Indexing Strategies ������������������������������������������������������������������������ 237 Heaps ��������������������������������������������������������������������������������������������������������������������������� 237 Temporary Objects ������������������������������������������������������������������������������������������������������������������������������ 237 Other Heap Scenarios ������������������������������������������������������������������������������������������������������������������������� 241 Clustered Indexes ��������������������������������������������������������������������������������������������������������� 241 Identity Sequence ������������������������������������������������������������������������������������������������������������������������������� 242 Surrogate Key ������������������������������������������������������������������������������������������������������������������������������������� 244 Foreign Key ����������������������������������������������������������������������������������������������������������������������������������������� 246 Multiple Column ���������������������������������������������������������������������������������������������������������������������������������� 251 Globally Unique Identifier�������������������������������������������������������������������������������������������������������������������� 256 Nonclustered Indexes ��������������������������������������������������������������������������������������������������� 258 Search Columns���������������������������������������������������������������������������������������������������������������������������������� 259 Index Intersection ������������������������������������������������������������������������������������������������������������������������������� 261 Multiple Column ���������������������������������������������������������������������������������������������������������������������������������� 264 Covering Indexes �������������������������������������������������������������������������������������������������������������������������������� 265 Included Columns ������������������������������������������������������������������������������������������������������������������������������� 267 Filtered Indexes ���������������������������������������������������������������������������������������������������������������������������������� 271 Foreign Keys ��������������������������������������������������������������������������������������������������������������������������������������� 275 Columnstore Index ������������������������������������������������������������������������������������������������������� 278 Index Storage Strategies ���������������������������������������������������������������������������������������������� 283 Row Compression ������������������������������������������������������������������������������������������������������������������������������� 283 Page Compression ������������������������������������������������������������������������������������������������������������������������������ 286 Indexed Views �������������������������������������������������������������������������������������������������������������� 289 Summary ���������������������������������������������������������������������������������������������������������������������� 292 ■Chapter 12: Query Strategies ���������������������������������������������������������������������������� 293 LIKE Comparison ���������������������������������������������������������������������������������������������������������� 293 Concatenation ������������������������������������������������������������������������������������������������������������� 297 Computed Columns ������������������������������������������������������������������������������������������������������ 299
Page
13
xiv ■ Contents Scalar Functions ���������������������������������������������������������������������������������������������������������� 302 Data Conversion ����������������������������������������������������������������������������������������������������������� 306 Summary ���������������������������������������������������������������������������������������������������������������������� 309 ■Chapter 13: Monitoring Indexes ������������������������������������������������������������������������ 311 Performance Counters ������������������������������������������������������������������������������������������������� 312 Dynamic Management Objects ������������������������������������������������������������������������������������ 317 Index Usage Stats ������������������������������������������������������������������������������������������������������������������������������� 318 Index Operational Stats ���������������������������������������������������������������������������������������������������������������������� 322 Index Physical Stats���������������������������������������������������������������������������������������������������������������������������� 329 Wait Statistics ������������������������������������������������������������������������������������������������������������������������������������� 332 Data Cleanup �������������������������������������������������������������������������������������������������������������������������������������� 335 Event Tracing ���������������������������������������������������������������������������������������������������������������� 336 SQL Trace ������������������������������������������������������������������������������������������������������������������������������������������� 336 Extended Events ��������������������������������������������������������������������������������������������������������������������������������� 340 Summary ���������������������������������������������������������������������������������������������������������������������� 343 ■Chapter 14: Index Analysis �������������������������������������������������������������������������������� 345 Review of Server State ������������������������������������������������������������������������������������������������� 346 Performance Counters ������������������������������������������������������������������������������������������������������������������������ 346 Wait Statistics ������������������������������������������������������������������������������������������������������������������������������������� 367 Buffer Allocation ��������������������������������������������������������������������������������������������������������������������������������� 375 Schema Discovery �������������������������������������������������������������������������������������������������������� 377 Identify Heaps ������������������������������������������������������������������������������������������������������������������������������������� 377 Duplicate Indexes ������������������������������������������������������������������������������������������������������������������������������� 378 Overlapping Indexes ��������������������������������������������������������������������������������������������������������������������������� 380 Unindexed Foreign Keys ��������������������������������������������������������������������������������������������������������������������� 382 Database Engine Tuning Advisor ���������������������������������������������������������������������������������� 384 Unused Indexes ������������������������������������������������������������������������������������������������������������ 386 Index Plan Usage ���������������������������������������������������������������������������������������������������������� 388 Summary ���������������������������������������������������������������������������������������������������������������������� 389
Page
14
xv ■ Contents ■Chapter 15: Indexing Methodology �������������������������������������������������������������������� 391 The Indexing Method ���������������������������������������������������������������������������������������������������� 391 Implement �������������������������������������������������������������������������������������������������������������������� 392 Communication ����������������������������������������������������������������������������������������������������������������������������������� 393 Deployment Scripts ���������������������������������������������������������������������������������������������������������������������������� 394 Execution �������������������������������������������������������������������������������������������������������������������������������������������� 395 Repeat �������������������������������������������������������������������������������������������������������������������������� 395 Summary ���������������������������������������������������������������������������������������������������������������������� 396 Index ��������������������������������������������������������������������������������������������������������������������� 397
Page
15
xvii About the Authors Jason Strate is a principal consultant with Pragmatic Works. He’s been working with data platforms for almost 20 years. He has been a recipient of Microsoft’s “Most Valuable Professional” award for Data Platform, formerly SQL Server, since July 2009. His experience includes design and implementation of both transaction and data warehouse solutions, as well as assessment and implementation of data platform environments for best practices, performance, and high availability solutions. Jason is an active member of the SQL Server community. He currently serves as co-chapter leader for the PASS Cloud Virtual Chapter. In the community, he presents on SQL Server and related topics at local, regional, and national events including SQL Saturdays and the PASS Summit. He also blogs at www.jasonstrate.com and can be contacted with his Twitter handle @stratesql. Grant Fritchey, Microsoft Data Platform MVP, works for Redgate Software, a market-leading software-tools vendor, as its product evangelist. He has developed large-scale applications using languages such as VB, C#, and Java, and he has worked in SQL Server since version 6.0. He has worked in insurance, finance, and consulting, as well as three failed dot-coms. Grant is the author of SQL Server Execution Plans (Simple Talk, 2012) and SQL Server Query Performance Tuning (Apress, 2014). He has contributed chapters to SQL Server MVP Deep Dives Volume 2 (Manning, 2012) and Pro SQL Server 2012 Practices (Apress, 2012). Grant presents on various SQL Server and data-related topics at large conferences and small user groups around the world.
Page
16
xix About the Technical Reviewer Rodney Landrum went to school to be a poet and a writer. And then he graduated, so that dream was crushed. He followed another path, which was to become a professional in the fun-filled world of information technology. He has worked as a systems engineer, UNIX and network admin, data analyst, client services director, and finally database administrator. The old hankering to put words on paper, while paper still existed, got the best of him, and in 2000, he began writing technical articles—some creative and humorous, some quite the opposite. In 2010 he wrote The SQL Server Tacklebox, a title his editor disdained but a book closest to the true creative potential he sought; he wanted to do a full book without a single screenshot. He promises his next book will be fiction or a collection of poetry, but that has yet to transpire.
Page
17
xxi Introduction Indexes are important—vastly important. No single structure aids in retrieving data from a database more than an index. Indexes represent both how data is stored and the access paths by which data can be retrieved from your database. Without indexes, a database is an unordered mess minus the roadmap to find the information you want. Throughout my experience with customers, one of the most common resolutions that I provide for performance tuning and application outages is the right indexes for their databases. Often, the effort of adding an index or two to the primary tables within a database provides significant performance improvements—much more so than tuning the database statement on. This is because an index can affect the many SQL statements that are being run against the database. Managing indexes may seem like an easy task. Unfortunately, their seeming simplicity is often the key to why they are overlooked. Often there is an assumption from developers that the database administrators will take care of indexing. Or there is an assumption by the database administrators that the developers are building the necessary indexes as they develop features in their applications. While these are primarily cases of miscommunication, people need to know how to determine what indexes are necessary and the value of those indexes. This book provides that information. Outside of the aforementioned scenarios is the fact that applications and how they are used changes over time. Features created and used to tune the database may not be as useful as expected, or a small feature change may lead to a big change in how the application and underlying database are used. All of this change affects the database and what needs to be accessed. As time goes on, databases and their indexes need to be reviewed to determine whether the current indexing is accurate for the new load. This book provides information in this regard. What’s in This Book? From beginning to end, this book provides information that can take you from an indexing novice to an indexing expert. The chapters are laid out such that you can start at any place to fill in the gaps in your knowledge and build from there. Whether you can barely spell index, need to understand the fundamentals, or want to build an indexing methodology, the information is available here. Chapter 1 covers index fundamentals. It lays the groundwork for all the following chapters. This chapter provides information regarding the types of indexes available in SQL Server. It covers some of the primary index types and defines what these are and how to build them. The chapter also explores the options available that can change the structure of indexes. From fill factor to included columns, the available attributes are defined and explained. Chapter 2 picks up where the previous chapter left off. Going beyond defining the indexes available, the chapter looks at the physical structure of indexes and the components that make up indexes. This internal understanding of indexes provides the basis for grasping why indexes behave in certain ways in certain situations. As you examine the physical structures of indexes, you’ll become familiar with the tools you can use to begin digging into these structures on your own. Armed with an understanding of the indexes available and how they are built, Chapter 3 explores the statistics that are stored on the indexes and how to use this information; these statistics provide insight into how SQL Server is utilizing indexes. The chapter also provides information necessary to decipher why an
Page
18
xxii ■ IntroduCtIon index may not be selected and why it is behaving in a certain way. You will gain a deeper understanding of how this information is collected by SQL Server through dynamic management views and what data is worthwhile to review. Not every index type was fully discussed in the first chapter; the types not discussed are covered in Chapters 4, 5, and 6. Beyond the rowstore and columnstore index structures, there are a few other index types, which are XML, spatial, full-text, and semantic search. These indexes are applicable to specific situations. In these chapters, you’ll look into these other index types to understand what they have to offer. You’ll also look at situations where they should be implemented. In a similar fashion to the previous three chapters, Chapter 7 takes a dive into memory-optimized tables. Memory-optimized tables are new to SQL Server 2014 and provide a unique capability to improve performance with tables that are primarily memory resident. Chapter 8 identifies and debunks some commonly held myths about indexes. Also, it outlines some best practices in regard to indexing a table. As you move into using tools and strategies to build indexes in the chapters that follow, this information will be important to remember. With a firm grasp of the options for indexing, the next thing that needs to be addressed is maintenance. In Chapter 9, you’ll look at what needs to be considered when maintaining indexes in your environment. First you’ll look at fragmentation. SQL Server is not without tools to automate your ability to build indexes. Chapter 10 explores these tools and looks at ways that you can begin build indexes in your environment today with minimal effort. The two tools discussed are the missing index DMVs and the Database Engine Tuning Advisor. You’ll look at the benefits and issues regarding both tools and get some guidance on how to use them effectively in your environment. The tools alone won’t give you everything you need to index your databases. In Chapter 11, you’ll begin to look at how to determine the indexes that are needed for a database and a table. There are a number of strategies for selecting what indexes to build within a database. They can be built according to recommendations by the query optimizer. They can also be built to support metadata structures such as foreign keys. For each strategy of indexing, there are a number of considerations to take into account when deciding whether to build the index. Part of effective indexing is writing queries that can utilize an index on a query. Chapter 12 discusses a number of strategies for indexing. Sometimes when querying data, the indexes you assume will be used are not used after all. These situations are usually tied into how a query is structured or the data that is being retrieved. Indexes can be skipped because of SARGability issues (where the query isn’t being properly selective on the index). They can also be skipped over because of tipping point issues, such as when the number of reads to retrieve data from an index potentially exceeds the reads to scan that or another index. These issues affect index selection as well as the effectiveness and justification for some indexes. Today’s DBA isn’t in a position to have only a single table to index. A database can have tens, hundreds, or thousands of tables, and all of them need to have the proper indexes. In Chapter 10, you’ll learn some methods to approach indexing for a single database but also for all the databases on the servers within your environment. What’s New in This Edition? The release of SQL Server 2014 included a significant number of changes to how indexes can be applied to your databases and data. Here are some of the key changes to SQL Server that involve indexing: • Introduction of memory-optimized tables with hash indexes • Expansion of columnstore indexes to include clustered structure • Coverage for selective XML indexes • Improvements to partitioning and statistics
Page
19
xxiii ■ IntroduCtIon To accommodate these changes, the format for this book has changed some. You’ll notice that Chapter 4 from the previous edition has been expanded to three chapters. This was based on reader feedback from the previous edition. This gives XML, spatial, and full-text their own chapters that focus just on those topics. A new chapter was added to dig deeper into memory-optimized tables and how to consider indexing for that table structure. The last format change was to the previous Chapter 10. In this edition, it has been expanded to three chapters, with some of the content expanded to include information that didn’t make the previous edition because of the size of the chapter. Summary As mentioned, indexes are important. Through the chapters in this book, you will become armed with what you need to know about the indexes in your environment. You will also learn how to find the information you need to improve the performance of your environment. —Jason Strate
Page
20
1 Chapter 1 Index Fundamentals The goal of this book is to help you improve the performance of your databases through the use of indexes. Before you can move toward that end, you must first understand what indexes are and why you need them. You need to understand the differences between how data in a clustered index, columnstore index, and heap table is stored. You also will look at how nonclustered and other index types are built and how indexes interact with other indexes. This chapter will provide the building blocks for understanding the logical design of indexes. Why Build Indexes? The most important asset any business owns is its data. Databases exist to store that data. A key piece in providing the data is delivering it efficiently. Being able to efficiently access data improves the value that the business gains from the data. The way to do that is through indexes. Indexes are the means to providing an efficient access path between the user and the data. By providing this access path, the user can ask for data from the database, and the database will know where to go to retrieve the data. Why not just have all the data in a table and return it when it is needed? Why go through the exercise of creating indexes? Returning data when needed is actually the point of indexes; they provide the path that is necessary to get to the data in the quickest manner possible. To illustrate, let’s consider an analogy that is often used to describe indexes—a library. When you go to the library, there are shelves upon shelves of books. In this library, a common task repeated over and over is finding a book. Most often you are particular on the book that you need, and you have a few options for finding that book. In the library, books are stored on the shelves using the Dewey Decimal Classification system. This system assigns a number to a book based on its subject. Once the value is assigned, the book is stored in numerical order within the library. For instance, books on science are in the range of 500 to 599. From there, if you wanted a book on mathematics, you would look for books with a classification of 510 to 519. Then to find a book on geometry, you’d look for books numbered 516. With this classification system, finding a book on any subject is easy and efficient. Once you know the number of the book you are looking for, you can go directly to the stack in the library where the books with 516 are located, instead of wandering through the library until you happen upon the geometry books. This is exactly how indexes work; they provide an ordered manner to store information that allows users to easily find the data. What happens, though, if you want to find all the books in a library written by Jason Strate? You could make an educated guess that they are all categorized under databases, but you would have to know that for certain. The only way to do that would be to walk through the library and check every stack. The library has a solution for this problem—the card catalog.
Comments 0
Loading comments...
Reply to Comment
Edit Comment