Microsoft SQL Server 2008 Internals

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.

Summary

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.

Advertisements
This entry was posted in Books and tagged , , , , , . Bookmark the permalink.

2 Responses to Microsoft SQL Server 2008 Internals

  1. Hi Eric,

    As the person who wrote Chapter 2, I’m curious about what you had in mind when you said that “it wasn’t quite as in depth” as you would have liked. This being an internals book, the chapter covered internals–how the feature actually works. I think I covered everything there was to cover, short of showing the actual source code, which of course wasn’t an option. To me that means that the chapter went as deep as it possibly could have. So I’m rather confused, and am thinking that you were perhaps looking for use cases? I’d call that breadth, not depth–but as with all of these terms it’s rather tricky to know what people mean when they’re coming from different points of view.

    Best,
    Adam

    • Eric Wisdahl says:

      Adam,

      You’re absolutely right in the poor terminology, and I’m sorry about that. I’ve just gone back to re-read it and it was an absolutely unfair assessment. I suppose that at the time I was reading this – and the review was based on what I remembered of how I felt after reading it the first time – I was looking more for use cases or how it was that I could sell the idea of extended events and what they could do for the organization to my team. If I were to have taken that approach with the rest of the book it would have been just as lacking, but I considered the other content to be really good. I suppose that it was more due to the fact that I didn’t really know too much about extended events before reading the chapter, and that the little I had heard had really piqued my interest. I’ll edit in a blurb in the post shortly.

      By the way, love your blog and really appreciate everything you do for the community!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s