Jason Strate - Expert Performance Indexing in SQL Server 2019: Toward Faster Results and Lower Maintenance

Any source code or other supplementary material referenced by the author in this book is available to readers on GitHub via the books product page, located at www.apress.com/9781484254639 . For more detailed information, please visit http://www.apress.com/source-code .

Todays world is all about the data. From the applications to manage our lives to the analytics we use to guide our decisions, data is everywhere. Behind data, databases provide the engine to get to our data, but without the right indexes we lack the fuel to access our data efficiently.

When it comes to indexes, no single structure aids in retrieving data from a database more than an index. New features in SQL Server provide new and unique ways to leverage and access your data, but without going back to the basics and ensuring that the data is properly indexed, youll find that even the new features fail to live up to expectations.

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 working on data platforms, one of the most common resolutions that I provide for performance tuning and application outages is to provide the right indexes for the underlying databases. Often, the effort of adding an index or two to the most accessed tables within a database provides significant performance improvementsmuch more so than tuning the database at a per SQL statement level. This is because an index affects many SQL statements that are being run against the database lifting performance across the workload.

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 the data they use change 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 databases 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 if the current indexing is accurate for the new load. This book also provides information in this regard.

From beginning to end, this book provides information that helps build your skills from a novice at indexing to an expert. The 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 can barely spell index, need to understand the fundamentals, or need to build an indexing methodology, the information is available here.

Chaptercovers index fundamentals. It lays the groundwork for all of 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.

Chapterpicks up where the previous chapter left off. Going beyond defining the indexes available, the chapter looks at the physical and logical 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 structures of indexes, youll 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, Chapterexplores 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 is fully discussed in the first chapter; the types not discussed are covered in Chapters.Beyond the rowstore and columnstore index structures, there are a few other index types which are Extensible Markup Language (XML), spatial, full-text, and semantic search. These indexes are applicable to specific situations. In these chapters, youll look into these other index types to understand what they have to offer. Youll also look at situations where they should be implemented.