0% нашли этот документ полезным (0 голосов)
7 просмотров10 страниц

SQL Server Query Performance Tuning (4th Edition) PDF

Книга 'SQL Server Query Performance Tuning (4th Edition)' предназначена для администраторов баз данных и разработчиков, которые хотят улучшить производительность SQL Server. Она охватывает широкий спектр тем, включая анализ производительности памяти, диска и ЦП, создание базовых показателей, анализ запросов и архитектуру индексов. Каждая глава предлагает практические советы и методы для оптимизации запросов и устранения узких мест в производительности.

Загружено:

austinhard1986
Авторское право
© All Rights Reserved
Мы серьезно относимся к защите прав на контент. Если вы подозреваете, что это ваш контент, заявите об этом здесь.
Доступные форматы
Скачать в формате PDF, TXT или читать онлайн в Scribd
0% нашли этот документ полезным (0 голосов)
7 просмотров10 страниц

SQL Server Query Performance Tuning (4th Edition) PDF

Книга 'SQL Server Query Performance Tuning (4th Edition)' предназначена для администраторов баз данных и разработчиков, которые хотят улучшить производительность SQL Server. Она охватывает широкий спектр тем, включая анализ производительности памяти, диска и ЦП, создание базовых показателей, анализ запросов и архитектуру индексов. Каждая глава предлагает практические советы и методы для оптимизации запросов и устранения узких мест в производительности.

Загружено:

austinhard1986
Авторское право
© All Rights Reserved
Мы серьезно относимся к защите прав на контент. Если вы подозреваете, что это ваш контент, заявите об этом здесь.
Доступные форматы
Скачать в формате PDF, TXT или читать онлайн в Scribd

SQL Server Query Performance Tuning (4th Edition)

://[Link]/download/sql-server-query-performance-tuning-4th-edition/

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.
SQL Server Query Performance Tuning (4th Edition)

Contents at a Glance
[Link]

About the Author ������������������������������������������������������������������������������������������������������������� xxiii


About the Technical Reviewer ������������������������������������������������������������������������������������������ xxv
Acknowledgments ���������������������������������������������������������������������������������������������������������� xxvii
Introduction ��������������������������������������������������������������������������������������������������������������������� xxix

■ Chapter 1: SQL Query Performance Tuning������������������������������������������������������������������������1


■ Chapter 2: Memory Performance Analysis ����������������������������������������������������������������������17
■ Chapter 3: Disk Performance Analysis ����������������������������������������������������������������������������35
■ Chapter 4: CPU Performance Analysis �����������������������������������������������������������������������������47
■ Chapter 5: Creating a Baseline ����������������������������������������������������������������������������������������57
■ Chapter 6: Query Performance Metrics ���������������������������������������������������������������������������69
■ Chapter 7: Analyzing Query Performance ����������������������������������������������������������������������85
■ Chapter 8: Index Architecture and Behavior �����������������������������������������������������������������111
■ Chapter 9: Index Analysis ����������������������������������������������������������������������������������������������143
■ Chapter 10: Database Engine Tuning Advisor ����������������������������������������������������������������165
■ Chapter 11: Key Lookups and Solutions ������������������������������������������������������������������������181
■ Chapter 12: Statistics, Data Distribution, and Cardinality ���������������������������������������������193
■ Chapter 13: Index Fragmentation ����������������������������������������������������������������������������������237
■ Chapter 14: Execution Plan Generation �������������������������������������������������������������������������269
■ Chapter 15 Execution Plan Cache Behavior�������������������������������������������������������������������283

This Book is Available on [Link]


iii
■ Contents at a GlanCe

SQL Server Query Performance Tuning (4th Edition)


■ Chapter 16: Parameter Sniffing �������������������������������������������������������������������������������������311
■ Chapter 17: Query Recompilation ����������������������������������������������������������������������������������321
■ Chapter 18: Query Design Analysis �������������������������������������������������������������������������������355
■ Chapter 19: Reduce Query Resource Use ����������������������������������������������������������������������379
[Link]
■ Chapter 20: Blocking and Blocked Processes ���������������������������������������������������������������397
■ Chapter 21: Causes and Solutions for Deadlocks ����������������������������������������������������������443
■ Chapter 22: Row-by-Row Processing ����������������������������������������������������������������������������459
■ Chapter 23: Memory-Optimized OLTP Tables and Procedures���������������������������������������483
■ Chapter 24: Database Performance Testing ������������������������������������������������������������������505
■ Chapter 25: Database Workload Optimization ���������������������������������������������������������������515
■ Chapter 26: SQL Server Optimization Checklist ������������������������������������������������������������547

Index ���������������������������������������������������������������������������������������������������������������������������������565

This Book is Available on [Link]


iv
■ IntroduCtIon

SQL Server Query Performance Tuning (4th Edition)


Who This Book Is For
This book is for just about anyone responsible for the performance of the system. Database administrators, certainly,
are targeted because they’re responsible for setting up the systems, creating the infrastructure, and monitoring it over
time. Developers are too, because who else is going to generate all the well-formed and highly performant T-SQL
code? Database devel-opers, more than anyone, are the target audience, if only because that’s what I do for work.
Anyone who has the capability to write T-SQL, design tables, implement indexes, or manipulate server settings on the
[Link]
SQL Server system is going to need this information to one degree or another.

How This Book Is Structured


The purpose of this book was to use as many “real-looking” queries as possible. To do this, I needed a “real” database.
I could have created one and forced everyone to track down the download. Instead, I chose to use the sample
database created by Microsoft, called AdventureWorks2012. This is available through CodePlex ([Link]/
MSFTDBProdSamples). I suggest keeping a copy of the restore handy and resetting your sample database after you have
read a couple of topics from the book. Microsoft updates these databases over time, so you might see different sets
of data or different behavior with some of the queries than what is listed in this book. I chose AdventureWorks2012
not because it represents a perfect database design but because it suffers from a number of design flaws and data
distribution issues that make it more accurately reflect the real world instead of some flawless test case.
To a degree, this book builds on the knowledge presented from previous chapters. However, most of the chapters
present information unique within that topic, so it is possible for you to jump in and out of particular chapters. You
will still receive the most benefit by a sequential reading of Chapter 1 through Chapter 26.
• Chapter 1, “SQL Query Performance Tuning,” introduces the iterative process of performance
tuning. You’ll get a first glimpse at establishing a performance baseline, identifying
bottlenecks, resolving the problems, and quantifying the improvements.
• Chapter 2, “Memory Performance Analysis,” starts the process using Performance Monitor
metrics and dynamic management objects as mechanisms for collecting information about
memory on your systems.
• Chapter 3, “Disk Performance Analysis,” continues exploring the system of bottlenecks with
a chapter dedicated to understanding how to collect metrics on disk performance. You’ll use
Performance Monitor and dynamic management objects again as well as add a number of
additional T-SQL queries.
• Chapter 4, “CPU Performance Analysis,” concludes the system bottleneck discussions with
CPU. I’ll also cover some network monitoring, although that is a fairly rare issue within SQL
Server, and there’s little a DBA or developer can do about it usually. The tools used are the
same as in the preceding chapters.
• Chapter 5, “Creating a Baseline,” takes the information from all three of the preceding chapters
and uses it to define a baseline. A baseline represents a known point in your system from
which you can compare to understand how performance is changing over time within your
system.
• Chapter 6, “Query Performance Metrics,” defines the best ways to look “under the hood” and
see what kinds of queries are being run on your system. It provides a detailed look at the new
Extended Events tools. Several of the most useful dynamic management views and functions
used to monitor queries are first identified in this chapter.

This Book is Available on [Link]


xxx
■ IntroduCtIon


SQL Server Query Performance Tuning (4th Edition)
Chapter 7, “Analyzing Query Performance,” walks you through consuming the metrics
gathered in the previous chapter and shows various methods available to analyze query
performance. You’re introduced for the first time to query execution plans as well as other
utilities available within SQL Server for determining which queries are longest running, most
frequently called, or in need of tuning.
• Chapter 8, “Index Architecture and Behavior,” explains indexes and index architecture. It
[Link]
defines the differences between clustered and nonclustered indexes. It shows which types
of indexes work best with different types of querying. Basic index maintenance is also
introduced.
• Chapter 9, “Index Analysis,” adds to the information from the preceding chapter and supplies
more information about the use and functionality of indexes within SQL Server.
• Chapter 10, “Database Engine Tuning Advisor,” covers the Microsoft tool Database Engine
Tuning Advisor. The chapter goes over in detail how to use the Database Engine Tuning
Advisor; you’re introduced to the various mechanisms for calling the tool and shown how it
works under real loads.
• Chapter 11, “Key Lookups and Solutions,” takes on the classic performance problem, the key
lookup, which is also known as the bookmark lookup. This chapter explores various solutions
to the lookup operation.
• Chapter 12, “Statistics, Data Distribution, and Cardinality,” introduces the concept of
statistics. The optimizer uses statistics to make decisions regarding the execution of the
query. Maintaining statistics, understanding how they’re stored, learning how they work, and
learning how they affect your queries are all topics covered within this chapter.
• Chapter 13, “Index Fragmentation,” shows how indexes fragment over time. You’ll learn how
to identify when an index is fragmented. You’ll also see what happens to your queries as
indexes fragment, and you’ll learn mechanisms to eliminate index fragmentation.
• Chapter 14, “Execution Plan Generation,” presents the mechanisms that SQL Server uses to
create execution plans. Plan reuse is an important concept within SQL Server. You’ll learn how
to identify whether plans are being reused. You’ll get various mechanisms for looking at the
cache. This chapter also introduces dynamic management views that allow excellent access to
the cache.
• Chapter 15, “Execution Plan Cache Behavior,” covers information about how plans move in
and out of cache as well as other details about execution plan behaviors including query and
plan hash and your ability to reuse execution plans in cache.
• Chapter 16, “Parameter Sniffing,” explains the extremely helpful process running
automatically within SQL Server called parameter sniffing. But, parameter sniffing can go bad
and cause serious performance issues. The problem, and the solutions, all go back to system
statistics.
• Chapter 17, “Query Recompilation,” displays how and when SQL Server will recompile plans
that were stored in cache. You’ll learn how plan recompiles can hurt or help the performance
of your system. You’ll pick up mechanisms for forcing a recompile and for preventing one.
• Chapter 18, “Query Design Analysis,” reveals how to write queries that perform well within
your system. Common mistakes are explored, and solutions are provided. You’ll learn several
best practices to avoid common bottlenecks.

This Book is Available on [Link]


xxxi
■ IntroduCtIon


SQL Server Query Performance Tuning (4th Edition)
Chapter 19, “Reduce Query Resource Use,” demonstrates various methods to ensure you’re
using fewer resources such as CPU and I/O when running your queries. You’ll learn about a
number of antipatterns that you should avoid while writing your T-SQL.
• Chapter 20, “Blocking and Blocked Processes,” teaches the best ways to recognize when
various sessions on your server are in contention for resources. You’ll learn how to monitor for
blocking along with methods and techniques to avoid blocked sessions.
[Link]
• Chapter 21, “Causes and Solutions for Deadlocks,” shows how deadlocks occur on your
system. You’ll get methods for identifying sessions involved with deadlocks. The chapter also
presents best practices for avoiding deadlocks or fixing your code if deadlocks are already
occurring.
• Chapter 22, “Row-by-Row Processing,” diagrams the inherent costs that cursors present to
set-oriented T-SQL code. However, when cursors are unavoidable, you need to understand
how they work, what they do, and how best to tune them within your environment if
eliminating them outright is not an option.
• Chapter 23, “Memory-Optimized OLTP Tables and Procedures,” introduces the new
capabilities of in-memory data storage and retrieval. You’ll also see how the in-memory
stored procedure can radically change performance in a positive fashion. But, this technology
isn’t universally applicable, so I’ll also go over some of the limitations and best practices for
applicability.
• Chapter 24, “Database Performance Testing,” provides you with mechanisms to replicate the
performance of your production system onto test systems in order to help you validate that the
changes you’ve introduced to your queries really are helpful. You’ll be using the Distributed
Replay utility, introduced in SQL Server 2012, along with all the other tools you’ve been using
throughout the book.
• Chapter 25, “Database Workload Optimization,” demonstrates how to take the information
presented in all the previous chapters and put it to work on a real database workload. You’ll
identify the worst-performing procedures and put them through various tuning methods to
arrive at better performance.
• Chapter 26, “SQL Server Optimization Checklist,” summarizes all the preceding chapters into
a set of checklists and best practices. The goal of the chapter is to enable you to have a place
for quickly reviewing all you have learned from the rest of the book.

Downloading the Code


You can download the code examples used in this book from the Source Code section of the Apress web site
([Link]). Most of the code is straight T-SQL stored in .sql files, which can be opened and used in any SQL
Server T-SQL editing tool. There are a couple of PowerShell scripts that will have to be run through a PowerShell
command line.

Contacting the Author


You can contact the author, Grant Fritchey, at grant@[Link]. You can visit his blog at [Link]

This Book is Available on [Link]


xxxii
Chapter 1 ■ SQL Query performanCe tuning

SQL Server Query Performance Tuning (4th Edition)


Before jumping straight into these topics, let’s first examine why we go about performance tuning the way we do.
In this chapter, I discuss the basic concepts of performance tuning for a SQL Server database system. It’s important
to have a process you follow in order to be able to find and identify performance problems, fix those problems, and
document the improvements you’ve made. Without a well-structured process, you’re going to be stabbing in the
dark, hoping to hit a target. I detail the main performance bottlenecks and show just how important it is to design a
database-friendly application, which is the consumer of the data, as well as how to optimize the database. Specifically,
I cover the following topics:
[Link]
• The performance tuning process
• Performance versus price
• The performance baseline
• Where to focus efforts in tuning
• The top 13 SQL Server performance killers
What I don’t cover within these pages could fill a number of other books. The focus of this book is on T-SQL query
performance tuning, as the title says. But, just so you’re clear, there will be no coverage of the following:
• Hardware choices
• Application coding methodologies
• Server configuration (except where it impacts query tuning)
• SQL Server Integration Services
• SQL Server Analysis Services
• SQL Server Reporting Services
• PowerShell

The Performance Tuning Process


The performance tuning process consists of identifying performance bottlenecks, prioritizing the identified issues,
troubleshooting their causes, applying different resolutions, and quantifying performance improvements—and then
repeating the whole process again and again. It is necessary to be a little creative, since most of the time there is no
one silver bullet to improve performance. The challenge is to narrow down the list of possible causes and evaluate the
effects of different resolutions. You can even undo previous modifications as you iterate through the tuning process.

The Core Process


During the tuning process, you must examine various hardware and software factors that can affect the performance
of a SQL Server–based application. You should be asking yourself the following general questions during the
performance analysis:
• Is any other resource-intensive application running on the same server?
• Is the capacity of the hardware subsystem capable of withstanding the maximum workload?
• Is SQL Server configured properly?

This Book is Available on [Link]


2

Вам также может понравиться