Expert performance indexing for SQL Server 2012

344 Pages·2012·27.642 MB·English

by  Strate, Jason;Krueger, Ted

Expert Performance
Indexing for SQL Server
2012
Jason Strate
Ted Krueger
Expert Performance Indexing for SQL Server 2012
Copyright © 2012 by Jason Strate and Ted Krueger
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 978-1-4302-3741-9 ISBN 978-1-4302-3742-6 (eBook)
DOI 10.1007/978-1-4302-3742-6
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.
President and Publisher: Paul Manning
Lead Editor: Jonathan Gennick
Technical Reviewers: Jorge Segarra and Ken Simmons
Editorial Board: Steve Anglin, Ewan Buckingham, Gary Cornell, Louise Corrigan, Morgan Ertel, Jonathan Gennick,
Jonathan Hassell, Robert Hutchinson, Michelle Lowman, James Markham, Matthew Moodie, Jeff Olson, Jeffrey
Pepper, Douglas Pundick, Ben Renow-Clarke, Dominic Shakeshaft, Gwenan Spearing, Matt Wade, Tom Welsh
Coordinating Editor: Debra Kelly
Copy Editors: Mary Bearden and Mary Behr
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 [phone], e-mail [e-mail],
or visit www.springeronline.com.
For information on translations, please e-mail [e-mail], 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 materials 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.
To Sarah, who missed too many walks and movies and learned more about SQL Server
and indexing that she ever thought she would.
– Jason Strate
In memory of Benelli, who was lost during the writing of this book. Always a part of our family
and in our hearts
– Ted Krueger
Contents at a Glance
About the Author ..........................................................................................................xv
About the Technical Reviewer ....................................................................................xvii
Acknowledgments .......................................................................................................xix
Introduction .................................................................................................................xxi
(cid:78) Chapter 1: Index Fundamentals ...................................................................................1
(cid:78) Chapter 2: Index Storage Fundamentals ...................................................................15
(cid:78) Chapter 3: Index Statistics ........................................................................................51
(cid:78) Chapter 4: XML, Spatial, and Full-Text Indexing ........................................................91
(cid:78) Chapter 5: Index Myths and Best Practices ............................................................121
(cid:78) Chapter 6: Index Maintenance .................................................................................135
(cid:78) Chapter 7: Indexing Tools ........................................................................................165
(cid:78) Chapter 8: Index Strategies .....................................................................................187
(cid:78) Chapter 9: Query Strategies ....................................................................................235
(cid:78) Chapter 10: Index Analysis ......................................................................................249
Index ...........................................................................................................................325
v
Contents
About the Author ..........................................................................................................xv
About the Technical Reviewer ....................................................................................xvii
Acknowledgments .......................................................................................................xix
Introduction .................................................................................................................xxi
(cid:78) Chapter 1: Index Fundamentals ...................................................................................1
Why Build Indexes? ................................................................................................................1
Major Index Types ...................................................................................................................2
Heap Tables ...................................................................................................................................................2
Clustered Indexes ..........................................................................................................................................2
Nonclustered Indexes ....................................................................................................................................3
Column Store Indexes ....................................................................................................................................3
Other Index Types ...................................................................................................................4
XML Indexes ..................................................................................................................................................4
Spatial Indexes ..............................................................................................................................................4
Full-Text Search .............................................................................................................................................5
Index Variations ......................................................................................................................5
Primary Key ...................................................................................................................................................5
Unique Index ..................................................................................................................................................5
Included Columns ..........................................................................................................................................6
Partitioned Indexes ........................................................................................................................................6
Filtered Indexes .............................................................................................................................................6
vii
(cid:78) CONTENTS
Compression and Indexing .....................................................................................................7
Index Data Definition Language .............................................................................................7
Creating an Index ...........................................................................................................................................8
Altering an Index ..........................................................................................................................................10
Dropping an Index .......................................................................................................................................10
Index Meta Data ...................................................................................................................11
sys.indexes ..................................................................................................................................................11
sys.index_columns ......................................................................................................................................12
sys.xml_indexes ..........................................................................................................................................12
sys.spatial_indexes .....................................................................................................................................12
sys.column_store_dictionaries....................................................................................................................12
sys.column_store_segments.......................................................................................................................12
Summary ..............................................................................................................................12
(cid:78) Chapter 2: Index Storage Fundamentals ...................................................................15
Storage Basics .....................................................................................................................15
Pages ...........................................................................................................................................................15
Extents .........................................................................................................................................................16
Page Types ...........................................................................................................................17
File Header Page .........................................................................................................................................18
Boot Page ...................................................................................................................................................19
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 ..........................................................................................................................21
Data Page ....................................................................................................................................................21
Index Page ...................................................................................................................................................21
Large Object Page .......................................................................................................................................21
viii
(cid:78)(cid:0)CONTENTS
Organizing Pages .................................................................................................................22
Heap Structure ............................................................................................................................................22
B-Tree Structure ..........................................................................................................................................24
Column Store Structure ...............................................................................................................................25
Examining Pages ..................................................................................................................27
DBCC EXTENTINFO .......................................................................................................................................27
DBCC IND .....................................................................................................................................................31
DBCC PAGE ..................................................................................................................................................35
Page Fragmentation .............................................................................................................43
Forwarded Records .....................................................................................................................................44
Page Splits ...................................................................................................................................................45
Index Characteristics ............................................................................................................48
Heap ............................................................................................................................................................48
Clustered Index ............................................................................................................................................48
Non-Clustered Index ....................................................................................................................................48
Column Store Index .....................................................................................................................................49
Summary ..............................................................................................................................49
(cid:78) Chapter 3: Index Statistics ........................................................................................51
Index-Level Statistics ...........................................................................................................51
DBCC SHOW_STATISTICS .............................................................................................................................51
Catalog Views ..............................................................................................................................................56
STATS_DATE ................................................................................................................................................57
Statistics DDL ..............................................................................................................................................57
Index-Level Statistics Summary ..................................................................................................................57
Usage Statistics ....................................................................................................................57
Header Columns ..........................................................................................................................................58
User Columns ..............................................................................................................................................58
System Columns ..........................................................................................................................................64
Index Usage Stats Summary .......................................................................................................................66
ix
(cid:78) CONTENTS
Operational Statistics ...........................................................................................................66
Header Columns ..........................................................................................................................................67
DML Activity .................................................................................................................................................67
SELECT Activity ............................................................................................................................................70
Locking Contention ......................................................................................................................................72
Latch Contention ..........................................................................................................................................76
Page Allocation Cycle ..................................................................................................................................80
Compression ................................................................................................................................................82
LOB Access ..................................................................................................................................................83
Index Operational Stats Summary ...............................................................................................................85
Physical Statistics ................................................................................................................85
Header Columns ..........................................................................................................................................86
Row Statistics ..............................................................................................................................................87
Fragmentation Statistics .............................................................................................................................88
Index Physical Stats Summary ....................................................................................................................89
Summary ..............................................................................................................................89
(cid:78) Chapter 4: XML, Spatial, and Full-Text Indexing ........................................................91
XML Indexing ........................................................................................................................91
Benefits .......................................................................................................................................................91
Categories ...................................................................................................................................................92
Creating an XML Index ................................................................................................................................92
Effects on Execution Plans ..........................................................................................................................95
Spatial Data Indexing .........................................................................................................101
How Spatial Data Is Indexed ......................................................................................................................101
Creating Spatial Indexes ...........................................................................................................................102
Supporting Methods with Indexes .............................................................................................................107
Understanding Statistics, Properties, and Information ..............................................................................109
Restrictions on Spatial Indexes .................................................................................................................111
Full-Text Indexing ...............................................................................................................112
Creating a Full-Text Example .....................................................................................................................112
Creating a Full-Text Catalog ......................................................................................................................113
x
(cid:78)(cid:0)CONTENTS
Creating a Full-Text Index ..........................................................................................................................113
Full-Text Search Index Catalog Views and Properties ...............................................................................118
Summary ............................................................................................................................120
(cid:78) Chapter 5: Index Myths and Best Practices ............................................................121
Index Myths ........................................................................................................................121
Myth 1: Databases Don’t Need Indexes .....................................................................................................122
Myth 2: Primary Keys Are Always Clustered ..............................................................................................123
Myth 3: Online Index Operations Don’t Block.............................................................................................125
Myth 4: Any Column Can Be Filtered In Multicolumn Indexes ...................................................................127
Myth 5: Clustered Indexes Store Records in Physical Order ......................................................................129
Myth 6: Fill Factor Is Applied to Indexes During Inserts .............................................................................130
Myth 7: Every Table Should Have a Heap/Clustered Index.........................................................................131
Index Best Practices ...........................................................................................................132
Use Clustered Indexes on Primary Keys by Default ...................................................................................133
Balance Index Count ..................................................................................................................................133
Fill Factor ...................................................................................................................................................133
Indexing Foreign Key Columns ..................................................................................................................134
Index to Your Environment .........................................................................................................................134
Summary ............................................................................................................................134
(cid:78) Chapter 6: Index Maintenance .................................................................................135
Index Fragmentation ..........................................................................................................135
Fragmentation Operations .........................................................................................................................135
Fragmentation Issues ................................................................................................................................144
Defragmentation Options ...........................................................................................................................146
Defragmentation Strategies.......................................................................................................................149
Preventing Fragmentation .........................................................................................................................156
Index Statistics Maintenance .............................................................................................158
Automatically Maintaining Statistics .........................................................................................................158
Manually Maintaining Statistics ................................................................................................................159
Summary ............................................................................................................................163
xi