Microsoft SQL Server 2008 Internals by Kalen Delaney et al.
Microsoft SQL Server 2008 Internals took me a good six weeks to finish as it had so much information to consume that I would often find myself reading for ten or twenty pages and then having to take a break. However, I feel like reading it cover to cover once, in combination with some of the MCM Prep videos, has given me a very strong foundation of how the engine works. I’m sure that I will continue to look back at some of the chapters over the next several years as I come upon problems with T-SQL.
Chapter 01 – SQL Server 2008 Architecture and Configuration
This chapter is an introduction chapter to SQL Server and goes into detail of the various editions of SQL Server, configurations for the server as well as a brief look at the SQLOS, the scheduler, memory management and the resource governor.
Chapter 02 – Change Tracking, Tracing and Extended Events
A good chapter that introduces the reader to several subjects. We first look at triggers and event notifications, then we examine the methods of change tracking in 2008 as well as how they work under the cover. Next, we look at server side traces and the SQL Profiler tool (the coverage for this tool is limited). Finally, the section which I was most interested in, Extended Events.
Unfortunately, even though the coverage here was well written and good content, it wasn’t quite as in depth as I would have liked. When I was reading the book the first time (and I have since re-read this chapter) I had been looking for more of a way in which I could apply extended events to my environment. Since the stated mission of this book was to show the internals of the engine and the tools involved expectation of such use cases was unfounded. This section does, however, provide a thorough explanation of the mechanics behind extended events.
Note that if you were to search on extended events (Particularly if you searched with the term “Jonathan Kehayais” (@SQLSarg) ) there is plenty of
in-depth the use case style information on the subject of extended events available. (And in depth information for that matter as well)
Chapter 03 – Databases and Database Files
In this chapter, the reader learns how various information about databases such as how to create databases, database files, space allocation, how to set database level options, database snapshots and how to expand or shrink databases. In addition, we learn about the system databases, particularly the tempdb. Like most chapters, we gain in depth observations of how these items are handled internally by the database engine. I particularly liked the investigation of the mssqlsystemresource database since it is one which very few people are aware even exists.
Chapter 04 – Logging and Recovery
This chapter takes the reader from transaction log basics up through showing how the log file changes in size, reuses virtual log files (VLFs), the effects of multiple log files, how to back up and restore transaction logs, etc. A great introduction and in depth explanation of what the server is doing with the transaction logs.
Chapter 05 – Tables
In this chapter the reader is introduced to the basics of tables as well as their internal structures. Everything from user defined types, to the options you can set with a table, to the effects of allowing nulls in a column are explored. We are also exposed to table and column level constraints. If you’ve ever wondered how it is that SQL server can efficiently locate columns when the exact size of the column or record is unknown, this chapter is for you!
Chapter 06 – Indexes: Internals and Management
This chapter, written by the trio of SQL Server experts Kalen Delaney, Kimberly Tripp and Paul Randal, looks at how indexes are stored, how they are updated, how they are chosen during query optimization and how you can analyze the indexes you have, and those you don’t have, in order to determine their usefulness as it relates to your server workload. A truly fantastic chapter.
Chapter 07 – Special Storage
The nuts and bolts of how large objects are stored, what filestream data is and how it is handled, how the engine handles storing and retrieving sparse columns, partitioning and finally the topic of data compression. An incredibly valuable look at how some of the special cases of data storage work.
Chapter 08 – The Query Optimizer
In this chapter the reader is exposed to the process which the query optimizer takes to build the execution plan and to provide optimization. This chapter further explores index selection, statistics, cardinality estimation and costing, and plan hints. In my opinion this chapter provides a very good explanation of how it is that everything works so that the reader will be able to examine the plans that are generated to determine where optimizations can be attempted.
Chapter 09 – Plan Caching and Recompilation
This chapter examines the plan cache, forced and simple parameterization, recompilations, plan cache management, plan guides and the DMOs which help to examine the cache.
Chapter 10 – Transactions and Concurrency
In this chapter the reader learns in depth information about the internal locking architecture in place in SQL Server. In addition, we learn how transactions and isolation levels interact with the locking mechanisms and how much protection each will provide from invalid data introduced through “dependency” or “consistency” problems. Finally the chapter examines the relatively new snapshot isolation levels and row versioning.
Chapter 11 – DBCC Internals
In this chapter we learn all about the various types of consistency checks and how it is that the database handles these internally form the man who was in charge of the tool up through SQL 2008, Paul Randal.
This book is highly recommended, although not for the faint of heart, and definitely not an entry level text. I’d say that this tome, in combination with the T-SQL Querying and T-SQL Programming books by Itzik Ben Gan, is the basis for a very solid understanding of SQL Server from a database developer’s viewpoint.
Note: I read this between November 2010 and January 2011. In my opinion it is an essential read for any SQL Server professional.
Edited review of chapter 2 after rereading the subject matter.