ebook img

Expert Performance Indexing in SQL Server PDF

23 Pages·2015·0.16 MB·English
by  
Save to my drive
Quick download
Download
Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.

Preview Expert Performance Indexing in SQL Server

Expert Performance Indexing in SQL Server Second Edition Jason Strate Grant Fritchey 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 [email protected], 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 [email protected], 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/. 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 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 v 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 vii ■ 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 viii ■ 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 ix ■ 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 x

Description:
Expert Performance. Indexing in SQL Server. Second Edition The publisher makes no warranty, express or implied, with respect to the material 2012) and SQL Server Query Performance Tuning (Apress, 2014). He has
See more

The list of books you might like

Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.