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 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

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