SQL Server索引优化实战(第二版)

需积分: 10 7 浏览量更新于2024-07-21 收藏 16.03MB PDF 举报

"Expert Performance Indexing in SQL Server" 是一本深入探讨数据库性能优化的专业书籍，由 Jason Strate 和 Grant Fritchey 合著，第二版于2015年由Apress出版。本书专注于SQL Server中的索引及其对系统性能的影响。在SQL Server中，索引是提升数据库性能的关键要素。本书首先从基础入手，详细介绍了不同类型的索引，包括聚集索引、非聚集索引、唯一索引、全文索引等，并解释了它们在数据库中的存储方式。作者深入浅出地阐述了索引如何帮助快速检索数据，以及它们在数据库结构中的作用。随着内容的深入，读者将学习到与索引相关的统计信息，这些统计信息对于理解索引的工作机制至关重要。书中详细讨论了如何分析和管理这些统计信息，以优化索引的性能。此外，还涉及到了索引维护和重建的策略，以及如何在不影响系统运行的前提下进行索引调整。书中的一个重要环节是关于索引对查询性能影响的探讨。作者讲解了如何通过索引来避免全表扫描，提高查询速度，同时揭示了过度索引可能导致的问题，如写操作性能下降、额外的磁盘空间消耗等。他们提供了有效的建议来平衡索引的利弊，确保数据库在读写操作间达到最佳性能。书的后半部分，作者通过一系列真实的案例场景，引导读者实际应用所学知识去诊断、缓解和提升数据库性能。这些案例涵盖了常见的性能瓶颈，如慢查询、死锁、资源争用等，提供了针对性的解决方案和优化技巧。 “Expert Performance Indexing in SQL Server” 是一本面向数据库管理员和开发者的实用指南，它不仅涵盖了索引理论，还提供了丰富的实践经验，帮助读者全面理解和掌握SQL Server中的索引优化，从而实现数据库性能的最大化。无论是初学者还是经验丰富的专业人士，都能从中受益匪浅，提升自己的数据库性能调优能力。

xix

About the Technical Reviewer

Rodney Landrum went to school to be a poet and a writer. And then he

graduated, so that dream was crushed. He followed another path, which

was to become a professional in the fun-filled world of information

technology. He has worked as a systems engineer, UNIX and network

admin, data analyst, client services director, and finally database

administrator. The old hankering to put words on paper, while paper

still existed, got the best of him, and in 2000, he began writing technical

articles—some creative and humorous, some quite the opposite. In 2010

he wrote The SQL Server Tacklebox, a title his editor disdained but a book

closest to the true creative potential he sought; he wanted to do a full book

without a single screenshot. He promises his next book will be fiction or a

collection of poetry, but that has yet to transpire.

www.it-ebooks.info

xxi

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

www.it-ebooks.info

xxii

■ IntroduCtIon

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

www.it-ebooks.info

Chapter 1

Index Fundamentals

The goal of this book is to help you improve the performance of your databases through the use of indexes.

Before you can move toward that end, you must first understand what indexes are and why you need them.

You need to understand the differences between how data in a clustered index, columnstore index, and

heap table is stored. You also will look at how nonclustered and other index types are built and how indexes

interact with other indexes. This chapter will provide the building blocks for understanding the logical

design of indexes.

Why Build Indexes?

The most important asset any business owns is its data. Databases exist to store that data. A key piece in

providing the data is delivering it efficiently. Being able to efficiently access data improves the value that the

business gains from the data. The way to do that is through indexes.

Indexes are the means to providing an efficient access path between the user and the data. By providing

this access path, the user can ask for data from the database, and the database will know where to go to

retrieve the data.

Why not just have all the data in a table and return it when it is needed? Why go through the exercise of

creating indexes? Returning data when needed is actually the point of indexes; they provide the path that is

necessary to get to the data in the quickest manner possible.

To illustrate, let’s consider an analogy that is often used to describe indexes—a library. When you go to

the library, there are shelves upon shelves of books. In this library, a common task repeated over and over

is finding a book. Most often you are particular on the book that you need, and you have a few options for

finding that book.

In the library, books are stored on the shelves using the Dewey Decimal Classification system. This

system assigns a number to a book based on its subject. Once the value is assigned, the book is stored in

numerical order within the library. For instance, books on science are in the range of 500 to 599. From there,

if you wanted a book on mathematics, you would look for books with a classification of 510 to 519. Then to

find a book on geometry, you’d look for books numbered 516. With this classification system, finding a book

on any subject is easy and efficient. Once you know the number of the book you are looking for, you can

go directly to the stack in the library where the books with 516 are located, instead of wandering through

the library until you happen upon the geometry books. This is exactly how indexes work; they provide an

ordered manner to store information that allows users to easily find the data.

What happens, though, if you want to find all the books in a library written by Jason Strate? You could

make an educated guess that they are all categorized under databases, but you would have to know that for

certain. The only way to do that would be to walk through the library and check every stack. The library has a

solution for this problem—the card catalog.

www.it-ebooks.info

剩余414页未读，继续阅读

vanridin

粉丝: 108
资源: 1187

SQL Server索引优化实战(第二版)

Expert Performance Indexing in SQL Server 2019, 3rd Edition.pdf

[Apress] Expert Performance Indexing for SQL Server 2012

Apress.Expert.Performance.Indexing.for.SQL.Server.2012.Jul.2012

Expert.Performance.Indexing.for.SQL.Server.2012.pdf

Apress.Expert.Indexing.in.Oracle.Database.11g.Jan.2012.pdf

Witten, Moffat, Bell. Managing gigabytes. compressing and indexing documents and images (2ed)

Secondary Indexing in Phoenix

Apache Solr(Apress,2015)

Tutorial on matrix indexing in MATLAB

英文原版-SQL Server 2008 Administration in Action 1st Edition

最新资源