For your convenience Apress has placed some of the front
matter material after the index. Please use the Bookmarks
and Contents at a Glance links to access them.
Download from Wow! eBook <www.wowebook.com>
v
Contents at a Glance
About the Author .......................................................................................................... xv
About the Technical Reviewer
.................................................................................... xvii
Acknowledgments
....................................................................................................... xix
Introduction
................................................................................................................. xxi
Chapter 1: Index Fundamentals
■ ...................................................................................1
Chapter 2: Index Storage Fundamentals
■ ...................................................................15
Chapter 3: Index Statistics
■ ........................................................................................51
Chapter 4: XML, Spatial, and Full-Text Indexing
■ ........................................................91
Chapter 5: Index Myths and Best Practices
■ ............................................................121
Chapter 6: Index Maintenance
■ .................................................................................135
Chapter 7: Indexing Tools
■ ........................................................................................165
Chapter 8: Index Strategies
■ .....................................................................................187
Chapter 9: Query Strategies
■ ....................................................................................235
Chapter 10: Index Analysis
■ ......................................................................................249
Index
...........................................................................................................................325
xxi
Introduction
Indexes are important. Not only that, they are 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 seek.
roughout my experience with customers, one of the most common resolutions that I provide for
performance tuning and application outages is to add indexes to their databases. Often, the eort of adding an
index or two to the primary tables within a database provides significant performance improvements—much
more so than tuning the database on statement. is is because an index can aect 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. is 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 change may
lead to a big change in how the application and underlying database are used. All of this change aects the
database and what needs to be accessed. As time goes on, databases and their indexes need to be reviewed to
determine if the current indexing is accurate for the new load. is book also provides information in this regard.
From beginning to end, this book provides information that can take you from an indexing novice to an
indexing expert. e chapters are laid out such that you can start at any place to fill in the gaps in your knowledge
and build out from there. Whether you need to understand the fundamentals or you need to start building out
indexes, the information is available here.
Chapter 1 covers index fundamentals. It lays the ground work for all of the following chapters. is 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. e 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 o. Going beyond defining the indexes available, the
chapter looks at the physical structure of indexes and the components that make up indexes. is 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. e chapter also provides information necessary to decipher why an 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.
■ IntroduCtIon
xxii
Not every index type was fully discussed in the first chapter; those types not discussed are covered in
Chapter 4. Beyond the classic index structure, there are a few other index types that should also be considered
when performance tuning. ese indexes are applicable to specific situations. In this chapter, you’ll look into
these other index types to understand what they have to oer. You’ll also look at situations where they should be
implemented.
Chapter 5 identifies and debunks some commonly held myths about indexes. Also, it outlines some best
practices in regards 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 6, 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 7 explores these tools and
looks at ways that you can begin build indexes in your environment today with minimal eort. e 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 eectively in your environment.
e tools alone won’t give you everything you need to index your databases. In Chapter 8, you’ll begin to
look at how to determine the indexes that are needed for a database and a table. ere are a number of strategies
for selecting what indexes to build within a database. ey can be built according to recommendations by the
Query Optimizer. ey 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 or not to build the
index.
Part of eective indexing is writing queries that can utilize an index on a query. Chapter 9 discusses a
number of strategies for indexing. Sometimes when querying data the indexes that you assume will be used
are not used after all. ese situations are usually tied into how a query is structured or the data that is being
retrieved. Indexes can be skipped due to SARGability issues (where the query isn’t being properly selective on the
index). ey can also be skipped over due to 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. ese issues eect index selection
as well as the eectiveness and justification for some indexes.
Today’s DBA isn’t in a position where they have only a single table to index. A database can have tens,
hundred, 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 of the databases on a server and servers
within your environment.
As mentioned, indexes are important. rough 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.
