SQL Server内存OLTP专家指南第二版：提升性能

需积分: 9 168 浏览量更新于2024-07-18 收藏 11.98MB PDF 举报

《专家级SQL Server内存OLTP（第二版）》是Dmitri Korotkevitch所著的一本专著，该书专注于在Microsoft SQL Server中实现革命性的在线事务处理（OLTP）性能提升。本书是针对SQL Server内存优化技术的深入指南，特别关注于In-Memory OLTP功能，这是SQL Server 2016及后续版本中引入的一项关键特性，旨在通过利用内存来加速数据处理速度和响应时间。 In-Memory OLTP的核心理念是将频繁访问的数据加载到内存中，减少硬盘I/O，从而大大提高数据库操作的效率。第二版更新了SQL Server的最新技术和最佳实践，涵盖了以下主要内容： 1. **理论基础**：介绍内存优化的基础概念，包括内存架构、内存池和缓存策略，帮助读者理解内存是如何被管理和利用的。 2. **设计原则**：提供关于如何设计适合内存优化的数据库模式、索引和分区策略的实用建议，以确保数据在内存中的高效存储和访问。 3. **SQL Server内存管理**：深入讲解SQL Server如何在内存中执行查询优化，包括内存分配、缓存策略、内存预算和内存泄漏检测等。 4. **案例研究与实战演练**：书中包含丰富的实际案例，通过具体的场景展示如何在不同业务场景下部署和优化In-Memory OLTP，以及如何解决可能遇到的问题。 5. **性能调优**：提供如何监控、分析和调整内存使用情况，以确保系统性能的持续优化，包括使用性能监视器、资源 governor 和内存配置参数等。 6. **最新版本更新**：鉴于SQL Server版本的迭代，作者会涵盖SQL Server 2016之后的新功能，如表级锁定、row-level locking和内存优化的扩展性策略。 7. **最佳实践与未来趋势**：书中还探讨了如何结合其他SQL Server特性（如Hekaton、Columnstore Indexes等）以实现最佳性能，并对内存计算的未来发展方向进行展望。《专家级SQL Server内存OLTP（第二版）》是一本兼具理论与实战的指南，无论对于数据库管理员、开发人员还是架构师，都是提升SQL Server内存优化技能和理解内存优化策略的重要参考书。通过阅读这本书，读者可以掌握如何在SQL Server中实现高效的内存使用，从而显著提升数据库系统的响应时间和整体性能。

xix

Introduction

The year 2016 was delightful for the SQL Server community—we put our hands on the

new SQL Server build. This was quite a unique release; for the first time in more than ten

years, the new version did not focus on specific technologies. In SQL Server 2016, you

can find enhancements in all product areas, such as programmability, high availability,

administration, and BI.

I, personally, was quite excited about all the enhancements in In-Memory OLTP.

I really enjoyed this technology in SQL Server 2014; however, it had way too many

limitations. This made it a niche technology and prevented its widespread adoption. In

many cases, the cost of the required system refactoring put the first release of In-Memory

OLTP in the “it’s not worth it” category.

I was incredibly happy that the majority of those limitations were removed in SQL

Server 2016. There are still some, but they are not anywhere near as severe as in the first

release. It is now possible to migrate systems into memory and start using the technology

without significant code and database schema changes.

I would consider this simplicity, however, a double-edged sword. While it can

significantly reduce the time and cost of adopting the technology, it can also open

the door to incorrect decisions and suboptimal implementations. As with any other

technology, In-Memory OLTP has been designed for a specific set of tasks, and it can hurt

the performance of the systems when implemented incorrectly. Neither is it a “set it and

forget it” type of solution; you have to carefully plan for it before implementing it and

maintain it after the deployment.

In-Memory OLTP is a great tool, and it can dramatically improve the performance

of systems. Nevertheless, you need to understand how it works under the hood to get the

most from it. The goal for this book is to provide you with such an understanding. I will

explain the internals of the In-Memory OLTP Engine and its components. I believe that

knowledge is the cornerstone of a successful In-Memory OLTP implementation, and this

book will help you make educated decisions on how and when to use the technology.

If you read my Pro SQL Server Internals book (Apress, 2016), you will notice some

familiar content from there. However, this book is a much deeper dive into In-Memory

OLTP, and you will find plenty of new topics covered. You will also learn how to address

some of In-Memory OLTP’s limitations and how to benefit from it in existing systems

when full in-memory migration is cost-ineffective.

Even though this book covers In-Memory OLTP in SQL Server 2016, the content

should also be valid for the SQL Server 2017 implementation. Obviously, check what

technology limitations were lifted there.

Finally, I would like to thank you for choosing this book and for your trust in me.

I hope that you will enjoy reading it as much as I enjoyed writing it.

D. Korotkevitch, Expert SQL Server In-Memory OLTP, DOI 10.1007/978-1-4842-2772-5_1

CHAPTER 1

Why In-Memory OLTP?

This introductory chapter explains the importance of in-memory databases and the

problems they address. It provides an overview of the Microsoft In-Memory OLTP

implementation (code name Hekaton) and its design goals. It discusses the high-level

architecture of the In-Memory OLTP Engine and how it is integrated into SQL Server.

Finally, this chapter compares the SQL Server in-memory database product with

several other solutions available.

Background

Way back when SQL Server and other major databases were originally designed,

hardware was expensive. Servers at that time had just one or very few CPUs and a small

amount of installed memory. Database servers had to work with data that resided on disk,

loading it into memory on demand.

The situation has changed dramatically since then. During the last 30 years,

memory prices have dropped by a factor of 10 every 5 years. Hardware has become

more affordable. It is now entirely possible to buy a server with 32 cores and 1TB of RAM

for less than $50,000. While it is also true that databases have become larger, it is often

possible for active operational data to fit into the memory.

Obviously, it is beneficial to have data cached in the buffer pool. It reduces the load on

the I/O subsystem and improves system performance. However, when systems work under

a heavy concurrent load, this is often not enough to obtain the required throughput. SQL

Server manages and protects page structures in memory, which introduces large overhead

and does not scale well. Even with row-level locking, multiple sessions cannot modify data

on the same data page simultaneously and must wait for each other.

Perhaps the last sentence needs to be clarified. Obviously, multiple sessions can

modify data rows on the same data page, holding exclusive (X) locks on different rows

simultaneously. However, they cannot update physical data page and row objects

simultaneously because this could corrupt the in-memory page structure. SQL Server

addresses this problem by protecting pages with latches. Latches work in a similar

manner to locks, protecting internal SQL Server data structures on the physical level

by serializing access to them, so only one thread can update data on the data page in

memory at any given point of time.

Chapter 1 ■ Why In-MeMory oLtp?

In the end, this limits the improvements that can be achieved with the current

database engine’s architecture. Although you can scale hardware by adding more CPUs

and cores, that serialization quickly becomes a bottleneck and a limiting factor in

improving system scalability. Likewise, you cannot improve performance by increasing

the CPU clock speed because the silicon chips would melt down. Therefore, the only

feasible way to improve database system performance is by reducing the number of CPU

instructions that need to be executed to perform an action.

Unfortunately, code optimization is not enough by itself. Consider the situation

where you need to update a row in a table. Even when you know the clustered index key

value, that operation needs to traverse the index tree, obtaining latches and locks on the

data pages and a row. In some cases, it needs to update nonclustered indexes, obtaining

the latches and locks there. All of that generates log records and requires writing them

and the dirty data pages to disk.

All of those actions can lead to a hundred thousand or even millions of CPU

instructions to execute. Code optimization can help reduce this number to some degree,

but it is impossible to reduce it dramatically without changing the system architecture

and the way the system stores and works with data.

These trends and architectural limitations led the Microsoft team to the conclusion

that a true in-memory solution should be built using different design principles and

architecture than the classic SQL Server Database Engine. The original concept was

proposed at the end of 2008, serious planning and design started in 2010, actual

development began in 2011, and the technology was finally released to the public in SQL

Server 2014.

The main goal of the project was to build a solution that would be 100 times faster

than the existing SQL Server Database Engine, which explains the code name Hekaton

(Greek for “100”). This goal has yet to be achieved; however, it is not uncommon for

In-Memory OLTP to provide 20 to 40 times faster performance in certain scenarios.

It is also worth mentioning that the Hekaton design has been targeted toward

OLTP workloads. As we all know, specialized solutions designed for particular tasks and

workloads usually outperform general-purpose systems in the targeted areas. The same

is true for In-Memory OLTP. It shines with large and busy OLTP systems that support

hundreds or even thousands of concurrent transactions. At the same time, the original

release of In-Memory OLTP in SQL Server 2014 did not work well for a data warehouse

workload, where other SQL Server technologies outperformed it.

The situation changes with the SQL Server 2016 release. The second release of

In-Memory OLTP supports columnstore indexes, which allow you to run real-time

operation analytics queries against hot OLTP data. Nevertheless, the technology is not as

mature as disk-based column-based storage, and you should not consider it an

in-memory data warehouse solution.

In-Memory OLTP has been designed with the following goals:

• Optimize data storage for main memory: Data in In-Memory OLTP

is not stored on disk-based data pages, and it does not mimic

a disk-based storage structure when loaded into memory. This

permits the elimination of the complex buffer pool structure and

the code that manages it. Moreover, regular (non-columnstore)

indexes are not persisted on disk, and they are re-created upon

startup when the data from memory-resident tables is loaded

into memory.

剩余313页未读，继续阅读

weixin_38669689

粉丝: 27
资源: 300

SQL Server内存OLTP专家指南第二版：提升性能

Expert SQL Server In-Memory OLTP, 2nd Edition

Expert SQL Server in-Memory OLTP(Apress,2015)

Expert SQL Server In-Memory OLTP, 2nd Edition.pdf

Expert SQL Server In-Memory OLTP(2nd) 无水印pdf

SQL Server Internals: In-Memory OLTP (Second Edition)

SQL Server 2016 In-Memory OLTP 性能增强

深入理解SQL Server 2016 In-Memory OLTP：内存优化技术解析

SQLServer 2014内存优化技术解析：In-Memory OLTP

SQLServer 2014内存优化：In-Memory OLTP(Hekaton)深度解析

Microsoft SQL Server 2008：OLTP与OLAP解析

最新资源