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

需积分: 10 99 浏览量更新于2024-07-17 1 收藏 5.35MB PDF 举报

"《SQL Server Internals: In-Memory OLTP (Second Edition)》由Kalen Delaney撰写，深入解析了SQL Server 2016的In-Memory OLTP引擎（也称为Hekaton），该引擎旨在利用大量内存和高性能处理器核心。这本书详细介绍了如何使用内存优化的表和索引、本机编译的存储过程，以及传统的磁盘基础表和索引、T-SQL存储过程。作者Kalen Delaney自1987年起专注于SQL Server，特别是查询性能调优和SQL Server内部机制，她分享了关于Hekaton内存内部工作的知识，帮助读者理解和优化应用程序性能。" SQL Server 2016的In-Memory OLTP引擎（Hekaton）是一个革命性的技术，它允许数据库在内存中处理数据，显著提高事务处理速度。与传统的基于磁盘的数据存储和访问方式不同，Hekaton使用内存优化的表和索引，以及本机编译的存储过程，这些特性提供了ACID事务保证，同时消除了锁定和挂起机制。这使得Hekaton能够透明地通过熟悉的接口如T-SQL和SSMS进行访问，但其内部行为和能力却与标准关系型引擎有着本质的区别。 Hekaton的内部工作原理是关键所在。它使用不同的数据结构来存储和管理内存中的数据，如使用原生编译的存储过程提高执行效率。这些存储过程在编译时被转换为机器代码，从而避免了SQL解释器的开销。此外，Hekaton通过无锁和无挂起的数据访问策略，确保并发处理能力，同时保持事务的一致性，这是通过使用先进的并发控制算法实现的。本书详细阐述了Hekaton如何存储和操作数据，以及如何在没有传统锁定机制的情况下保证事务的原子性、一致性、隔离性和持久性。作者Kalen Delaney的深入分析将帮助开发者和DBA理解如何将现有的表或数据库迁移到Hekaton，以实现比以往更快的应用程序性能提升。此外，书中还涵盖了性能监控和调优策略，包括如何评估是否适合使用In-Memory OLTP，以及如何进行迁移规划和性能测试。对于希望优化SQL Server性能的开发者和数据库管理员来说，这本书提供了一个宝贵的资源，能够帮助他们充分利用Hekaton的优势，解决高并发、大数据量场景下的性能瓶颈问题。《SQL Server Internals: In-Memory OLTP (Second Edition)》是一本深入解析SQL Server 2016内存优化技术的权威指南，对于那些寻求提升数据库性能、理解和掌握In-Memory OLTP引擎内部机制的专业人士来说，它是不可或缺的参考书籍。

Introduction

The original design of the SQL Server engine, as well as that of most other RDBMS products

of the time, assumed that main memory was very limited, and so data needed to reside on

disk except when it was actually needed for processing. However, over the past thirty years,

the sustained fulllment of Moore's Law, predicting that computing power will double year

on year, has rendered this assumption largely invalid.

Moore's law has had a dramatic impact on the availability and affordability of both large

amounts of memory and multiple-core processing power. Today one can buy a server with

32 cores and 1 TB of memory for under $30K. Looking further ahead, it's entirely possible

that in a few years we'll be able to build distributed DRAM-based systems with capacities of

1–10 Petabytes at a cost of less than $5/GB. It is also only a question of time before non-

volatile RAM becomes viable as main-memory storage.

At the same time, the near-ubiquity of 64-bit architectures removes the previous 4 GB limit

on "addressable" memory and means that SQL Server has, in theory, near-limitless amounts

of memory at its disposal. This has helped to signicantly drive down latency time for read

operations, simply because we can t so much more data in memory. For example, many, if

not most, of the OLTP databases in production can t entirely in 1 TB. Even for the largest

nancial, online retail and airline reservation systems, with databases between 500 GB and

5 TB in size, the performance-sensitive working dataset, i.e. the "hot" data pages, is signi-

cantly smaller and could reside entirely in memory.

However, the fact remains that the traditional SQL Server engine is optimized for disk-

based storage, for reading specic 8 KB data pages into memory for processing, and writing

specic 8 KB data pages back out to disk after data modication, having rst "hardened" the

changes to disk in the transaction log. Reading and writing 8 KB data pages from and to disk

can generate a lot of random I/O and incurs a higher latency cost.

In fact, given the amount of data we can t in memory, and the high number of cores avail-

able to process it, the end result has been that most current SQL Server systems are I/O

bound. In other words, the I/O subsystem struggles to "keep up," and many organizations

sink huge sums of money into the hardware that they hope will improve write latency. Even

when the data is in the buffer cache, SQL Server is architected to assume that it is not, which

leads to inefcient CPU usage, with latching and spinlocks.

Assuming all, or most, of the data will need to be read from disk also leads to unrealistic cost

estimations for the possible query plans and a potential for not being able to determine which

plans will really perform best.

As a result of these trends, and the limitations of traditional disk-based storage structures,

the SQL Server team at Microsoft began building a database engine optimized for large main

memories and many-core CPUs, driven by the recognition that systems designed for a partic-

ular class of workload can frequently outperform more general purpose systems by a factor of

ten or more. Most specialized systems, including those for Complex Event Processing (CEP),

Data Warehousing and Business Intelligence (DW/BI), and Online Transaction Processing

(OLTP), optimize data structures and algorithms by focusing on in-memory structures.

The team set about building a specialized database engine specically for in-memory work-

loads, which could be tuned just for those workloads. The original concept was proposed at

the end of 2008, envisioning a relational database engine that was 100 times faster than the

existing SQL Server engine. In fact, the codename for this feature, Hekaton, comes from the

Greek word hekaton (ἑκατόν) meaning 100.

Serious planning and design began in 2010, and product development began in 2011. At that

time, the team did not know whether the current SQL Server product could support this new

concept, and the original vision was that it might be a separate product. Fortunately, it soon

became evident that it would be possible to incorporate the "in-memory" processing engine

into SQL Server itself. The team then established four main goals as the foundation for

further design and planning:

1. Optimized for data that was stored completely in-memory but was also durable on

SQL Server restarts.

2. Fully integrated into the existing SQL Server engine.

3. Very high performance for OLTP operations.

4. Architected for modern CPUs (e.g. use of complex atomic instructions).

SQL Server In-Memory OLTP, formerly known and loved as Hekaton, meets all of these

goals, and in this book, you will learn how it meets them. The focus will be on the features

that allow high performance for OLTP operations. As well as eliminating read latency, since

the data will always be in memory, fundamental changes to the memory-optimized versions

of tables and indexes, as well as changes to the logging mechanism, mean that in-memory

OLTP also offers greatly reduced latency when writing to disk.

The rst four chapters of the book offer a basic overview of how the technology works

(Chapter 1), how to create in-memory databases and tables (Chapter 2), the basics of row

versioning and the new multi-version concurrency control model (Chapter 3), how memory-

optimized tables and their indexes store data, and how columnstore indexes, available for

memory-optimized tables as of SQL Server 2016, allow you to perform efcient OLTP

operations, as well as run analytic queries, on your in-memory data (Chapter 4).

Chapters in the latter half of the book focus on how the new in-memory engine delivers the

required performance boost, while still ensuring transactional consistency (ACID compli-

ance). In order to deliver on performance, the SQL Server team realized they had to address

some signicant performance bottlenecks. Two major bottlenecks were the traditional

locking and latching mechanisms: if the new in-memory OTLP engine retained these mecha-

nisms, with the waiting and possible blocking that they could cause, it could negate much

of the benet inherent in the vastly increased speed of in-memory processing. Instead, SQL

Server In-Memory OLTP delivers a completely lock- and latch-free system, and true opti-

mistic multi-version concurrency control (Chapter 5).

Other potential bottlenecks were the existing CHECKPOINT and transaction logging

processes. The need to write to durable storage still exists for in-memory tables, but in SQL

Server In-Memory OLTP these processes are adapted to be much more efcient, in order to

prevent them becoming performance limiting, especially given the potential to support vastly

increased workloads (Chapter 6).

The nal bottleneck derives from the fact that the SQL Server query processor is essentially

an interpreter; it re-processes statements continually, at runtime. It is not a true compiler. Of

course, this is not a major performance concern, when the cost of physically reading data

pages into memory from disk dwarfs the cost of query interpretation. However, once there

is no cost of reading pages, the difference in efciency between interpreting queries and

running compiled queries can be enormous. Consequently, the new SQL Server In-Memory

OLTP engine component provides the ability to create natively compiled procedures, i.e.

machine code, for our most commonly executed data processing operations (Chapter 7).

Finally, we turn our attention to tools for managing SQL Server In-Memory OLTP structures,

for monitoring and tuning performance, and considerations for migrating existing OLTP

workloads over to in-memory (Chapter 8).

Intended Audience and Prerequisites

This book is for anyone using SQL Server as a programmer or as an administrator who

wants to understand how the new Hekaton engine works behind the scenes. It is specically

a book about Hekaton internals, focusing on details of memory-optimized tables and

indexes, how the in-memory engine delivers transactional consistency (ACID compliance)

without locking or latching, and the mechanics of its checkpointing, logging and garbage

collection mechanisms.

SQL Server In-Memory OLTP is a new technology and this is not a book specically on

performance tuning and best practices. However, as you learn about how the Hekaton engine

works internally to process your queries, certain best practices, and opportunities for perfor-

mance tuning will become obvious.

This book does not assume that you're a SQL Server expert, but I do expect that you have

basic technical competency and familiarity with the standard SQL Server engine, and relative

uency with basic SQL statements.

You should have access to a SQL Server 2016 installation, even if it is the Evaluation edition

available free from Microsoft. In addition, SQL Server Developer Edition, which doesn't

have an expiration date, is also available free of charge. Downloads are available from this

link: http://preview.tinyurl.com/lea3ep8. As of SQL Server 2016, Service Pack 1, in-memory

OLTP is available in all editions of SQL Server.

The Hands-On Exercises

This book will provide the reader with scripts for hands-on exercises, shown as listings,

to create memory-optimized databases, tables and indexes and to explore some aspects of

Hekaton behavior. My examples were all created using SQL Server Management Studio

(SSMS).

You can download these scripts from: http://preview.tinyurl.com/y72g47te.

All examples have been veried on SQL Server 2016 SP1 (13.0.4001.0). All of the examples

use custom-built sample databases, as dened in the text and scripts. The only exception is

Listing 2-2 which shows you how to enable an existing database to use memory-optimized

tables; it uses AdventureWorks2014, but you could easily edit the script to substitute any

existing database.

剩余200页未读，继续阅读

evandrix_

粉丝: 0
资源: 2

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

深入理解SQL Server 2014 Hekaton内存引擎：内部揭秘

trace-filter-internals：实现JavaScript代码追踪中的内部函数过滤

深入理解SQL Server 2016内部机制：提升开发与性能调优

SQL Server Internals_ In-Memor - Kalen Delaney

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

Android Security Internals: An In-Depth Guide to Android's Security Architecture

Hyper-V-Internals:有关Hyper-V的内部信息

Windows Internals：6th Part 2 - Erasing Flash Program Memory Sequence Explained

Windows Internals: Including Windows Server 2008 and Windows Vista, Fifth Edition

Pro SQL Server Internals

最新资源