PASS Book Review by: Grant Fritchey
Rating:   (5 out of 5)

Overview

Most SQL Server DBA’s I know who have been working on SQL Server 7 or 2000 for any length of time usually have a copy of Kalen Delaney’s “Inside SQL Server” sitting on their desk. For SQL Server 2005, that book has been split into four different books and it looks like all them are going to be must-have’s occupying large swaths of book-shelf space across the planet. Because, the fact of the matter is, if you want to know the internals of the new database engine, this is your source.

Comparisons between this new book and the previous books in the series seem inevitable. From that type of comparison, I think that this book comes off very well. Removing the TSQL processing and other aspects of development from the book allows Delaney to drill down into every aspect of how the server works. She takes a very structured approach, showing how to install SQL Server before moving on to the Server Architecture and Configuration chapters. She covers database and database files, logging and recovery, tables, indexes, and locking, each in separate chapters, each in a great degree of detail. She takes the time between explaining how things work to suggest best practices for configuration and usage along the way. Scattered throughout are also Tip’s & Notes that suggest alternatives to the topic under discussion or implications of the usage of a particular topic.

This is not a book from which you can easily learn basic SQL Server skills. So much time is spent on so much detail, invaluable detail for the experienced user, that the novice might get stuck in the mire. I personally don’t find that to be an actual detraction from the book. In fact, I’d recommend that if you’re just learning SQL Server 2005, you pick this book up in addition to some other, more general, tome, because from here you can learn the why’s and wherefore’s of what you’re trying to do.

The Forward to the book says one of the most important things about this book and it bears repeating, “This is not a revision of the previous book. So much has changed and there are so many new features that Kalen had to write a brand new book.” Reading through this book, that becomes clear on every single page. Nothing appears to have been regurgitated from the previous books, but each and every screen shot, description and code example is written for 2005.

Target Audience

As usual, I went to the Introduction and Forward to see what the author believed her target audience to be. By defining what the series, and this book in particular, is trying to do, focus on the internals of the storage and query engines, Delaney defines her target audience. Those that are looking for interface and programming advice at a high level need to look elsewhere. This book is geared toward those that need to understand how SQL Server works in order to better design their own systems and/or troubleshoot issues with those systems. If you’re primarily concerned with system configurations for mirroring, replication or clustering or if you’re a data warehouse developer who needs information on the Analysis Services engine or SSIS, you need to find a different book.

Strengths 

In a word, depth. This book doesn’t simply tell you that SQL Server manages its own memory. You get a detailed break down of the various memory caches managed by SQL Server and how each of these interacts to manage the system. Another example, I knew that SQL added a field to a clustered index to make it unique if the index itself wasn’t unique. I didn’t know that it only adds that uniquefier when a duplicate value is added to the index. See page 263 for a detailed description of exactly what happens and why. It’s all this detail and depth, the why of what is happening as well as the what, that separates this book from almost any other book you’re likely to pick up on SQL Server 2005

Weaknesses

I wouldn’t really say the book has weaknesses. Yes, there are topics not covered, so you Analytics engine folks will be sorely disappointed. The only thing that can really detract from the book is the one thing that makes it great. I suppose it would be possible, when looking up a simple answer, you get a complex explanation. That might not always make a given reader happy. However, if you go into this book with the full knowledge that it is thick and deep, you shouldn’t be surprised when it’s thick and deep.

Chapter Breakdown

1 Installing and Upgrading to SQL Server 2005

This chapter walks you through the process of installing or upgrading SQL Server 2005, as advertised. A lot of the information here is available in other sources. The parts that are not are some of the suggestions that Delaney makes. When she suggests that choosing a sort order of Case Insensitive, Uppercase Preference, is bad, and then details why it’s bad, you should probably listen. By and large, you won’t be referencing this chapter frequently, but I would strongly recommend you read it at least once. It will save you headaches in the future.

2 SQL Server 2005 Architecture

The book takes off in Chapter 2. The discussions about the low level operations, how SQL Server manages memory, context switching, all the operations of the engine itself made a huge difference in my understanding of how the system worked. She also starts an exploration of various & sundry Dynamic Management Views and Functions (DMVs and DMFs) that continues through the rest of the book. This chapter, like the rest of the book, will be referenced regularly when you have questions about some bit of detail or a broad concept such as, how does SQL Server interact with Non-Uniform Memory Architecture (NUMA).

3 SQL Server 2005 Configuration

Here Delaney shows you how to configure your server. I don’t mean just how to edit through Configuration Manager or Management Studio, but what your choices of settings are and the repercussions of making changes to those settings. She takes the time to walk through the various setting groups and makes some good recommendations for when and why you should deviate from the defaults, if at all. She also discusses the new Default Trace. This sort of replaces and enhances the old black box trace, but is actually more functional and easier to use. I know I’m repeating myself, but you will be reading parts of this chapter over and over again.

4 Database and Database Files

Here, Delaney delivers one of the meatier chapters in the book. This covers how SQL server, and you, manage databases. It includes topics on system databases, files, creating a database, filegroups, altering databases, options, security and tempdb. As with the rest of the book, we get details on how various operations work. One example is the detailed examination of shrinking a database and the various ways and methods available. Throughout, in addition to the detailed explanations, are good code examples that walk you through operations from the simple, but important, ALTER DATABASE dbname SET EMERGENCY;, to the more complex operations required to move the master and resource database. Delaney also delivers on recommendations and best practices. Every dba should read her run-through on tempdb, a classic bottle-neck in SQL Server. Snapshots, a database specific operation, are covered here rather than in the chapter on backups.

5 Logging and Recovery

Here you’ll find detailed explanations around backups and restores of databases. Delaney covers all the bases with detailed discussions on recovery models, choosing the types of backups and restores, what happens during a restore and how the log works. The information on logs and logging makes up most of the chapter and is invaluable information. This is one of the shorter chapters in the book, but possibly the most important.

6 Tables

Delaney starts this chapter with detailed discussions of the various types of metadata stored within the system tables. These are accessed, primarily through DMVs and DMFs unlike 2000 and before when you could access the tables directly. She goes on to discuss creating tables, defining columns, data types, identity values and constraints. She takes the time address every DBA’s favorite subjects, naming conventions (she gets this one right in my opinion).  I really enjoyed the discussion on the use of NULL. With full support for why, she outlines why her recommendation, strongly worded, is to never use NULL values, to set each column to NOT NULL and set default values. Full disclosure, I’ve never done this myself because I’ve always found it easier to simply allow NULL values. However, after reading some of her explanations, I may reexamine this practice. Another extremely useful section in this chapter is the detailed, lengthy, discussion on how data is stored within the database. Delaney also takes the time to explain how to access the internal storage and how to interpret the information stored there.

7 Index Internals and Management

This chapter’s focus on Indexes is, once again, a must read. Delaney starts off describing how the B-tree index structure works within SQL Server. From there she goes on to provide details on the structure of index pages, space requirements, and index management. She covers indexes on computed columns and indexes on views (materialized indexes) very well. The book details the topic of data manipulation, which leads to page splitting. She describes at length what happens when pages are split, when rows are deleted or moved. You will read about index maintenance, introducing new 2005 functionality such as the ALTER INDEX statement from which you can rebuild or defragment indexes. She shows the new DMV methods for determining index fragmentation.

8 Locking and Concurrency

This chapter goes over how concurrency works in 2005, changed from 2000 with the ability to set TRANSACTION ISOLATION LEVEL. It goes on to cover transaction processing including isolation levels. Locking and blocking are explained and, best of all, a series of examples are developed that show how the locks are created and released. She makes suggestions on methods for reducing locking which result in less wait time on the server. She spends considerable time discussing the merits of, and issues with, row-level locking vs. page locking and then goes over how lock escalation is handled within SQL Server. Lock escalation of course leads into deadlocks, with a complete description of why deadlocks occur, what SQL Server does when they happen and methods for avoiding them. The section on Row Versioning which introduces the new database properties of READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION is probably going to get post-it notes attached across the country. We get a very good explanation of where and when to use these new functions and what the cost will be, primarily related to tempdb.

Perspective of Reviewer

I work primarily as a database developer, doing database design from logical models and business requirements and then developing stored procedures, etc., for accessing data from those databases. I use this type of information to help in those designs and troubleshoot functional and performance issues. Knowing exactly what happens under the covers makes a very serious difference in how you build a database or write TSQL code.