Dr. Dobb's Journal March 2004
One of the more interesting things about Microsoft's SQL Server is the volume of information available to help developers and administrators make it work better. That there continue to be new books focusing on SQL Server 2000—a three-year old version whose successor, known as "Yukon," has already been introduced at Microsoft's Professional Developers Conference—is a testament to the community that exists around it.
The first book I examine is actually a somewhat older title, copyrighted 2001, but relatively new to me. Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook, by Ken England, is a replacement for the author's previous The SQL Server 6.5 Performance Optimization and Tuning Handbook—one of the first books I read when moving to SQL Server 6.5 years ago.
The book begins by covering the basics of how SQL Server organizes databases. England covers both the underlying details and the tools that let you explore those details. While it is possible to do almost everything you need to do with Query Analyzer and some SQL Script, the reality is that most users use Enterprise Manager to do much of their maintenance.
Next come three chapters on indexing. These chapters go beyond the basics of indexing and go into what makes a good index, and when to use clustered and nonclustered indexes. Just as important (and something missing from some similar books) is the examination of the impact of indexing on performance of inserts and updates.
The meat of the book is the chapter on query optimization. This single chapter makes up more than 100 pages of the 370-page book. Once the basics of query optimization are covered, the chapter covers use of the client tools, especially the graphical Showplan offered in Query Analyzer.
The balance of the chapters cover SQL Server's use of memory, disk, and locks, followed by a chapter devoted to the Profiler—the most underused but useful of SQL Server tools. This chapter alone is worth the cost of the book if you work with code that you do not fully understand. Seeing what the program is actually doing can help tremendously when trying to uncover a difficult performance problem. For instance, I have used the Profiler to see why Crystal Reports was performing terribly when using OleDb, yet performing wonderfully using the same SQL code hitting an ODBC data source. Interesting indeed.
The Guru's Guide to SQL Server Architecture and Internals by Ken Henderson is a new book, copyright 2004. You might wonder if a new book on SQL Server 2000 makes sense, given the new version of SQL Server waiting in the wings. In the case of this book, yes, it certainly does.
This is a huge book (about 1000 pages) and the level of detail and the breadth of Henderson's knowledge of SQL Server and the Win32 world in general is breathtaking. The first half of the book covers things that no other SQL Server book I have seen covers. In fact, while the book focuses on SQL Server and how it interacts with the Win32 environment, I would argue that the first 400 pages or so would serve as a complete introduction to server-side development.
About 300 pages into the book, I was questioning how important all of this stuff was. Processes and threads, memory handling, I/O (including a good discussion of I/O completion ports and their impact on server scalability), networking, and COM are all covered in some detail. It only took me about 30 pages into Part II to realize that the initial portion of the book was required for the level of detail offered in the balance of the book. Henderson was indeed smart to include what he did in the first section. If you have ever had any question about what SQL Server is doing, you will be able to figure it out after reading this book.
For instance, if you have ever used SQL Profiler on a program and didn't see a line of SQL that you're certain the program emits, it could be because the profiler specifically has code that filters out any call to sp_password, presumably to ensure that the new password won't be sniffable by unscrupulous administrators. As luck would have it, the code that does this is primitive, so even adding --sp_password (that is, the literal sp_password in a comment) causes the line to be missing from the SQL Profiler's output. This is certainly not the most useful detail in the book, but it does point up the level of detail.
If you can buy only one of these books, which is it? If you are just interested in SQL Server from a somewhat naive programmer's standpoint, you might be happier with Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook. This is a more traditional book covering SQL Server performance issues, and it does a good job at its intended task.
On the other hand, The Guru's Guide to SQL Server Architecture and Internals gives you a more detailed view of what SQL Server does and how it does it. There is also lots of code in the book, and a great deal (especially Part I) is in C++. Henderson's response to possible complaints concerning his use of C++ is, essentially, "get over it." That's reasonable. You don't need to understand every line of code, and most programmers familiar with any language can follow along, given his excellent explanations. And best of all, the code is included on a CD, making it easy to follow along in your favorite editor.
DDJ