Pro SQL Server Internals summary

Understanding the Core Components

In Pro SQL Server Internals, Dmitri Korotkevitch takes us on an in-depth exploration of the core components of the SQL Server database engine. He starts by discussing the SQL Server architecture, the relational engine, and the storage engine. The relational engine is responsible for parsing and optimizing SQL queries, while the storage engine is in charge of managing the physical storage of the data.

Korotkevitch delves into the internal structures of the database, such as the storage of tables and indexes, and how SQL Server processes and optimizes queries. He also discusses the role of the transaction log in maintaining the consistency and durability of the database. This section provides a comprehensive understanding of how SQL Server stores and retrieves data.

Performance Tuning and Optimization

After laying the foundation, Pro SQL Server Internals moves on to performance tuning and optimization. Korotkevitch explores the query execution plans and the methods for understanding and improving them. He goes on to discuss the statistics SQL Server uses to estimate the cost of different query plans and how to maintain and update these statistics to ensure optimal query performance.

The book also covers index structures and their impact on query performance. It delves into different types of indexes, such as clustered and non-clustered indexes, and their internal structures. Korotkevitch explains how to choose the right indexes for your workload and how to design and maintain them for optimal performance.

Advanced Topics and New Features

In the latter part of the book, Korotkevitch explores more advanced topics and new features. He discusses the internals of the In-Memory OLTP engine, a new feature introduced in SQL Server 2014, and how it differs from the traditional disk-based storage engine. He also covers the implementation and internals of columnstore indexes, another feature introduced for data warehousing workloads.

Furthermore, Korotkevitch delves into the internals of the query processing improvements introduced in SQL Server 2014, including the new cardinality estimator. He explains the changes made to the query optimizer and execution engine and how these changes impact query performance and execution plans.

Practical Insights and Real-World Examples

Throughout Pro SQL Server Internals, Korotkevitch provides practical insights and real-world examples to illustrate the concepts discussed. He uses scripts and tools to demonstrate how to analyze and troubleshoot performance issues, and provides best practices for maintaining and optimizing SQL Server databases.

By the end of the book, the reader gains a deep understanding of the internal workings of SQL Server and how to leverage this knowledge to design, maintain, and optimize high-performance databases. Whether you're a database administrator, developer, or architect, Pro SQL Server Internals equips you with the knowledge and tools to tackle the most challenging SQL Server performance issues.