nach oben

2017 | Buch

Expert SQL Server In-Memory OLTP

verfasst von: Dmitri Korotkevitch

Verlag: Apress

Enthalten in: Springer Professional "Wirtschaft+Technik" , Springer Professional "Technik" , Springer Professional "Wirtschaft"

Einloggen, um Zugang zu erhalten

Über dieses Buch

Take a deep dive into one of the most significant SQL Server features–support for In-Memory Online Transaction Processing. The latest developments are covered, including support of off-row storage, columnstore indexes and operational analytics, changes in programmability and native compilation, and more. This book describes the architecture and internals of the In-Memory OLTP Engine and explains how to develop, deploy, and maintain systems using it. With it you can dramatically increase transaction throughput to handle thousands of transactions per second supporting millions of customers.
Learn the architecture and the internals of In-Memory OLTP in order to recognize when technology can make a difference. Recognize opportunities for In-Memory OLTP in new development and understand how to benefit from it in existing systems. Don’t be without Dmitri Korotkevitch and the deep expertise he imparts in Expert SQL Server In-Memory OLTP, 2nd Edition as you move forward in using SQL Server’s In-Memory OLTP technology.
Dmitri Korotkevitch is the five-star author of Pro SQL Server Internals, and brings the same combination of clear thinking and deep expertise to help you in this second edition. The book:Explains In-Memory OLTP internals, architecture and programmability, including data storage, indexing, multi-version concurrency control, transaction logging and recovery, and native compilation
Covers SQL Server 2016 technology enhancements, including columnstore indexes and off-row storage
Guides in using In-Memory OLTP in new development and existing systems
What You’ll Learn
Grasp how SQL Server stores and works with data in memory-optimized tables
Properly design and index memory-optimized tables
Plan successful deployments, including the required memory size and I/O configuration
Accelerate T-SQL processing through the creation of natively compiled modules
Monitor and report on the benefits and performance of your In-Memory OLTP solutions
Benefit from the technology in existing systems and in the systems with the mixed workload
Who This Book Is For

Application developers and database administrators who create and manage online transaction processing (OLTP) systems; in particular, those wanting to take advantage of SQL Server’s new offering of in-memory OLTP to dramatically improve performance and throughput of their systems

Inhaltsverzeichnis

Frontmatter

Chapter 1. Why In-Memory OLTP?

Abstract

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.

Dmitri Korotkevitch

Chapter 2. In-Memory OLTP Objects

Abstract

This chapter provides a high-level overview of In-Memory OLTP objects. It shows how to create databases with an In-Memory OLTP filegroup and how to define memory-optimized tables and access them through the Interop Engine and natively compiled modules.

Dmitri Korotkevitch

Chapter 3. Memory-Optimized Tables

Abstract

This chapter discusses memory-optimized tables in detail. It shows how memory-optimized tables store their data and how SQL Server accesses them. It covers the format of the data rows in memory-optimized tables and talks about the process of native compilation.

Dmitri Korotkevitch

Chapter 4. Hash Indexes

Abstract

This chapter discusses hash indexes, the new type of index introduced in the In-Memory OLTP Engine. It will show their internal structure and explain how SQL Server works with them. You will learn about the most critical property of hash indexes, bucket_count, which defines the number of hash buckets in the index hash array. You will see how incorrect bucket count estimations affect system performance.

Dmitri Korotkevitch

Chapter 5. Nonclustered Indexes

Abstract

This chapter discusses nonclustered indexes, which is the second type of indexes supported by the In-Memory OLTP Engine. It shows how to define nonclustered indexes, talks about their SARGability rules, and explains their internal structure.

Dmitri Korotkevitch

Chapter 6. Memory Consumers and Off-Row Storage

Abstract

This chapter provides an overview of how In-Memory OLTP allocates the memory for different objects and explains how off-row column data is stored. It also illustrates the performance impact of having off-row columns in a table and explains how SQL Server chooses columns that need to be stored off-row.

Dmitri Korotkevitch

Chapter 7. Columnstore Indexes

Abstract

This chapter provides an overview of column-based storage and clustered columnstore indexes that can be defined on memory-optimized tables. It explains their internal structure and discusses several best practices that can improve the performance of data warehouse/reporting and operational analytics queries in the system.

Dmitri Korotkevitch

Chapter 8. Transaction Processing in In-Memory OLTP

Abstract

This chapter discusses transaction processing in In-Memory OLTP. It elucidates what isolation levels are supported with native compilation and cross-container transactions, provides an overview of concurrency phenomena encountered in the database systems, and explains how In-Memory OLTP addresses them. Finally, this chapter talks about the lifetime of In-Memory OLTP transactions in detail.

Dmitri Korotkevitch

Chapter 9. In-Memory OLTP Programmability

Abstract

This chapter focuses on the programmability aspects of the In-Memory OLTP engine in SQL Server. It describes the process of native compilation, and it provides an overview of the natively compiled modules and T-SQL features that are supported in In-Memory OLTP. Finally, this chapter compares the performance of several use cases that work with the data in memory-optimized tables using natively compiled modules and interpreted T-SQL with the interop engine.

Dmitri Korotkevitch

Chapter 10. Data Storage, Logging, and Recovery

Abstract

This chapter discusses how In-Memory OLTP stores the data from durable memory-optimized tables on disk. It illustrates the concept of checkpoint file pairs used by SQL Server to persist the data, provides an overview of the checkpoint process in In-Memory OLTP, and discusses the recovery of memory-optimized data. It also explains why In-Memory OLTP logging is more efficient compared to disk-based tables.

Dmitri Korotkevitch

Chapter 11. Garbage Collection

Abstract

This chapter covers the garbage collection process used in the In-Memory OLTP engine. It provides an overview of the various components involved in garbage collection and demonstrates how they interact with each other.

Dmitri Korotkevitch

Chapter 12. Deployment and Management

Abstract

This chapter discusses the deployment and management aspects of systems that utilize In-Memory OLTP. It provides a set of guidelines about hardware and server configurations, and it covers In-Memory OLTP–related database administration and management tasks. Finally, this chapter gives an overview of the changes and enhancements in the catalog and data management objects related to In-Memory OLTP.

Dmitri Korotkevitch

Chapter 13. Utilizing In-Memory OLTP

Abstract

This chapter discusses several design considerations for systems utilizing In-Memory OLTP and demonstrates how to benefit from the technology when migrating the existing systems is cost-ineffective. It also talks about implementing data partitioning, which can be helpful in a system with a large amount of data and mixed workload.

Dmitri Korotkevitch

Backmatter

Titel: Expert SQL Server In-Memory OLTP
verfasst von: Dmitri Korotkevitch
Verlag: Apress
Electronic ISBN: 978-1-4842-2772-5
Print ISBN: 978-1-4842-2771-8
DOI: https://doi.org/10.1007/978-1-4842-2772-5