Thursday , March 28 2024
If you are ready to take the next step, into the internals of why SQL Server does what it does.

Book Review: Microsoft SQL Server 2008 Internals by Kalen Delaney, Paul S. Randal, et al

Microsoft SQL Server 2008 Internals is the latest incarnation of the book that was known as "Inside SQL Server 2005". The author of these prior editions, Kalen Delaney, felt that the series was becoming too unfocused and thought that it was time to refocus what the book’s intent. Add to that, there was the fact that the term "Inside" had been used by many other authors, that the designation was becoming diluted in its true meaning.

By renaming it SQL Server 2008 Internals, the intent is to tell you that this book is intended to explain how Microsoft's flagship relational database product internally works. While there are many books that show you best practices with regard to tuning SQL Server, the goal of SQL Server 2008 Internals is to help you understand why certain tuning practices work the way that they do so as to help you determine your own best practices.

SQL Server 2008 Internals is intended for anyone who wants a deeper understanding of what SQL Server does behind the scenes and its main focus is on the core SQL Server engine, particularly on the query processor and the storage engine. SQL Server 2008 Internals is 784 pages in length and divided into 11 chapters.

Chapter One, "SQL Server 2008 Architecture and Configuration," begins by looking at the general workings of the SQL Server engine. It examines the interactions between SQL Server and the operating system as well as providing insight into the roles and responsibilities of the major components. This chapter also looks at the use of the configuration options that can be used to change behavior of SQL Server.

Chapter Two, "Change Tracking, Tracing, and Extended Events," explores the various hidden and internal objects used by Change Tracking to help support synchronization applications, the inner workings of SQL Trace, and the architecture of what will be the future of events in SQL Server Extended Events (XE). Having enough internal knowledge of these systems will give you a better understand how to use the many eventing features in your normal activities.

Chapter Three, "Databases and Database Files," now looks at the complex collection of objects known as a database. You begin with the basic system databases and the sample databases. From there you begin looking at creating, altering, and setting database options. Along the way you will begin to understand space allocation, security, creating snapshots, and other forms of working with databases.

Chapter Four, "Logging and Recovery," continues your understanding of databases by now looking at how the SQL Log files are structured, how they are managed, and when the transaction information is logged. You will also learn how the log file grows and how it can be reduced in size, as well as how they are used during backup and restore operations.

Chapter Five, "Tables," digs into a component of a database and starts with a fundamental introduction to tables. You will then continue with a detailed examination of their internal structures and internal storage issues of the various data types; in particular between the fixed and variable data types. You will also see how data is physically stored in databases and how to query some of the metadata views to retrieve information from underlying system tables.

Chapter Six, "Indexes: Internals and Management," now looks at index usage, concepts and internals. Here you will see how indexes are stored and how they work for data retrieval. Then you will see what happens when data is modified and the potential effects of data modifications on indexes such as fragmentation. Finally you will learn about index management and maintenance.

Chapter Seven, "Special Storage," examines what happens with data that exceeds normal row size limitations and is stored as either row-overflow or Large Object data. Also discussed is file-stream data which is new to SQL Server 2008 that allows you to access data from the operating system files as if it were part of your relational table. Then, finally you will look at data compression and also see how to separate data onto partitions.

Chapter Eight, "The Query Optimizer," determines the query plan to be executed for any given SQL Statement. This chapter explains the basic mechanisms of the optimizer including high-level structures that are used and the alternatives are defined. Then you will see specific areas in the Query Optimizer and how they fit into this framework.

Chapter Nine, "Plan Caching and Recompilation," now examines the plan cache and how it is organized. You will see what kinds of plans are saved and what situations that SQL Server might reuse them. You will also see how to force SQL Server to reuse a plan when it may want to recompile as well as how to force it to recompile when it wants to reuse.

Chapter Ten, "Transactions and Concurrency," discusses the two concurrency models that are available in SQL Server and how they can affect transactions, especially when trying to modify the same data at the same time. Then you will examine the five isolation levels in SQL Server 2008 and how it controls concurrent access using each model.

Chapter Eleven, "DBCC Internals," is all about getting a consistent view of your database. SQL Server seldom causes corruption, but rather the corruption comes from all of the stuff between SQL Server and the disk drives — also known as the I/O subsystems. This chapter examines how you can do regular consistency checks to maintain a well running system.

Microsoft SQL Server 2008 Internals is the definitive guide to the getting to know what is going on inside SQL Server. While it focuses on 2008, it still relates very much to 2005. Keep in mind that this is not a step-by-step book, but rather it examines what happens when steps are taken, and even more importantly, it can provide guidance when things don't work as you might expect.

What is more, the relationship with Microsoft SQL Server 2008 Internals is not a once and done, it is one of those books that you will read and re-read to really gather all it has to give. Keep in mind this is not a book for learning SQL Server, but rather a book for when you know SQL Server and want to know more about the internal workings. It is for more advanced users of SQL Server and those who want to become advanced users. If you are ready to take the next step, into the internals of why SQL Server does what it does, then I highly recommend this book.

About T. Michael Testi

Photographer, writer, software engineer, educator, and maker of fine images.

Check Also

CES

CES 2021: Online Technology Show Done Right

The Consumer Electronics Show (CES), like so many other film, trade, and technology events this …