The Guru's Guide to SQL Server Architecture and Internals

Paperback (Print)
Used and New from Other Sellers
Used and New from Other Sellers
from $2.07
Usually ships in 1-2 business days
(Save 96%)
Other sellers (Paperback)
  • All (16) from $2.07   
  • New (7) from $41.15   
  • Used (9) from $2.07   

Overview

"I can pretty much guarantee that anyone who uses SQL Server on a regular basis (even those located in Redmond working on SQL Server) can learn something new from reading this book."
--David Campbell, Product Unit Manager,
Relational Server Team, Microsoft Corporation

The latest book from the highly regarded and best-selling author Ken Henderson, The Guru's Guide to SQL Server Architecture and Internals is the consummate reference to Microsoft SQL Server. Picking up where documentation and white papers leave off, this book takes an all-inclusive approach to provide the most depth and breadth of coverage of any book on SQL Server architecture, internals, and tuning.

Blending in-depth discussion with practical application, the guide begins with several chapters on the fundamental Windows technologies behind SQL Server, including processes and threads, memory management, Windows I/O, and networking. The focus then moves on to the architectural details of SQL Server and how to practically apply them.

The entire SQL Server product is covered--not just the functionality that resides within the core executable or product features that have been in place for years. SQL Server has matured and broadened substantially with each release, and the author explores the "fringe" technologies that have yet to be covered elsewhere, including Notification Services, Full Text Search, SQLXML, replication, DTS, and a host of others.

Throughout the book, the author uses WinDbg, Microsoft's free downloadable symbolic debugger, to look under the hood of SQL Server. Armed with new debugging and coding skills, readers will be ready to master SQL Server on their own.

The accompanying CD-ROM is packed with additional material, including full source code for the book's 900+ examples, as well as three invaluable tools: DTSDIAG, the VBODSOLE Library, and DTS Package Guru. DTSDIAG allows developers and administrators to simultaneously collect Profiler traces, perform logs, blocking script output, system event logs, and SQLDIAG reports from a specified SQL Server. The VBODSOLE Library features more than twenty new COM-based functions for Transact-SQL, including T-SQL enhancements such as array-manipulation routines, financial functions, string-manipulation functions, and system functions. DTS Package Guru is a .NET-based package editor for SQL Server's Data Transformation Services that allows editing of any modifiable package and supports the automation of mass package changes.

The Guru's Guide to SQL Server Architecture and Internals is the essential guide for database developers and admin- istrators alike, regardless of skill level.

0201700476B10012003

Read More Show Less

What People Are Saying

David Campbell
I can pretty much guarantee that anyone who uses SQL Server on a regular basis (even those located in Redmond working on SQL Server) can learn something new from reading this book.
David Campbell, Product Unit Manager, Relational Server Team, Microsoft Corporation
Read More Show Less

Product Details

  • ISBN-13: 9780201700473
  • Publisher: Addison-Wesley
  • Publication date: 10/24/2003
  • Pages: 1021
  • Product dimensions: 6.98 (w) x 9.34 (h) x 2.03 (d)

Meet the Author

Ken Henderson, a nationally recognized consultant and leading DBMS practitioner, consults on high-end client/server projects for such customers as the U.S. Air Force, the U.S. Navy, H&R Block, Travelers Insurance, J.P. Morgan, the CIA, Owens-Corning, and CNA Insurance. He is the author of five previous books on client/server and DBMS development, a frequent magazine contributor to such publications as Software Development Magazine and DBMS Magazine, and a speaker at technical conferences.

0201700476AB07032003

Read More Show Less

Table of Contents

List of Exercises.

Foreword.

Historical Perspective.

Preface.

Acknowledgments.

Introduction.

About the Author.

I. FOUNDATIONS.

1. Overview.

Chapter Overview.

Chapter Pairs.

About the Code.

2. Windows Fundamentals.

The Win32 API.

User Mode vs. Kernel Mode.

Processes and Threads.

Virtual Memory vs. Physical Memory.

Subsystems.

Dynamic-Link Libraries.

Tools.

Recap.

Knowledge Measure.

3. Processes and Threads.

Processes.

Threads.

Thread Scheduling.

Thread Synchronization.

4. Memory Fundamentals.

Memory Basics.

Virtual Memory.

Heaps.

Shared Memory.

5. I/O Fundamentals.

I/O Basics.

Asynchronous and Nonbuffered I/O.

Scatter-Gather I/O.

I/O Completion Ports.

Memory-Mapped File I/O.

6. Networking Fundamentals.

Overview.

Named Pipes.

Windows Sockets.

Remote Procedure Call.

Recap.

Knowledge Measure.

7. COM.

Overview.

Before COM.

The Dawn of COM.

Basic Architecture.

COM at Work.

Threading Models.

COM and SQL Server.

Recap.

Knowledge Measure.

8. XML.

Overview.

Simplicity Comes at a Price.

A Brief History of XML.

XML vs. HTML: An Example.

Document Type Definitions.

XML Schemas.

Converting XML to HTML Using a Style Sheet.

The Document Object Model.

Processing XML with MSXML.

Resources.

Recap.

Knowledge Measure.

II. SUBSYSTEMS, COMPONENTS, AND TECHNOLOGIES.

9. SQL Server as a Server.

SQL Server and Networking.

The SQL Server Executable.

SQL Server's DLLs.

SQL Server I/O.

SQL Server Components.

Recap.

Knowledge Measure.

10. User Mode Scheduler.

UMS Design Goals.

User Mode vs. Kernel Mode Scheduling.

Preemptive vs. Cooperative Tasking.

How UMS Takes Over Scheduling.

The UMS Scheduler.

The UMS Scheduler Lists.

Going Preemptive.

Hidden Schedulers.

DBCC SQLPERF(umsstats).

Recap.

Knowledge Measure.

11. SQL Server Memory Management.

Memory Regions.

Sizing.

The BPool.

Primitive Allocations.

AWE.

The Lazywriter.

The Memory Managers.

Pulling It All Together.

Recap.

Knowledge Measure.

12. Query Processor.

Key Terms and Concepts.

Parsing.

Optimization Stages.

Optimization Limits.

Parameter Sniffing.

Auto-Parameterization.

Indexing.

Statistics.

Indexable Expressions.

Join Order and Type Selection.

Logical and Physical Operators.

Recap.

Knowledge Measure.

13. Transactions.

The ACID Test.

How SQL Server Transactions Work.

Types of Transactions.

Avoiding Transactions Altogether.

Automatic Transaction Management.

Transaction Isolation Levels.

Transaction Commands and Syntax.

Debugging Transactions.

Optimizing Transactional Code.

Recap.

Knowledge Measure.

14. Cursors.

Overview.

On Cursors and ISAM Databases.

Types of Cursors.

Appropriate Cursor Use.

Transact-SQL Cursor Syntax.

Configuring Cursors.

Updating Cursors.

Cursor Variables.

Cursor Stored Procedures.

Optimizing Cursor Performance.

Recap.

Knowledge Measure.

15. ODSOLE.

Overview.

The sp_OA Procedures.

Automating with ODSOLE.

Automating SQL-DMO by Using ODSOLE.

Using ODSOLE to Automate Custom Objects.

Recap.

Knowledge Measure.

16. Full-Text Search.

Overview.

Architectural Details.

Setting Up Full-Text Indexes.

Full-Text Predicates.

Rowset Functions.

Recap.

Knowledge Measure.

III. DATA SERVICES.

17. Server Federations.

Partitioned Views.

Recap.

Knowledge Measure.

18. SQLXML.

Overview.

MSXML.

FOR XML.

Using FOR XML.

OPENXML.

Using OPENXML.

Accessing SQL Server over HTTP.

URL Queries.

Using URL Queries.

Template Queries.

Mapping Schemas.

Updategrams.

XML Bulk Load.

Managed Classes.

SQLXML Web Service (SOAP) Support.

SQLXML Limitations.

Recap. Knowledge Measure.

19. Notification Services.

How It Works.

Building Your Own Notification Application.

Recap.

Knowledge Measure.

20. Data Transformation Services.

Overview.

Packages.

The Multiphase Data Pump.

The Bulk Insert Task.

The Data Driven Query Task.

ActiveX Transformations.

Other Types of Transformations.

Lookup Queries.

Workflow Properties.

DTS and Transactions.

Controlling Package Workflow through Scripting.

Parameterized DTS Packages.

The DSO Rowset Provider.

Using DTS to Transform Replication Subscriptions.

Custom Tasks.

Controlling DTS through Automation.

Recap.

Knowledge Measure.

21. Snapshot Replication.

Overview.

The Snapshot Agent.

Duties of the Snapshot and Distribution Agents.

Updatable Subscriptions.

Remote Agent Activation.

Replication Cleanup.

Recap.

Knowledge Measure.

22. Transactional Replication.

Overview.

The MSrepl_commands Table.

The sp_replcmds Procedure.

The sp_repldone Procedure.

Update Stored Procedures.

Concurrent Snapshot Processing.

Updatable Subscriptions.

Validating Replicated Data.

Skipping Errors.

Cleanup.

Recap.

Knowledge Measure.

23. Merge Replication.

Overview.

Conflict Resolution.

Generations.

Filtering.

Identity Range Management.

Recap.

Knowledge Measure.

IV. UNDOCUMENTED SQL SERVER.

24. Finding Undocumented Features.

The syscomments Gold Mine.

Goodies in sysobjects.

Scripting Undocumented and System Objects.

The Profiler Treasure Trove.

Snooping around in the Installation Scripts.

DLL Imports.

Recap.

Knowledge Measure.

25. DTSDIAG.

V. ESSAYS.

Why I Really, Really Don't Like Fish!

Pseudo-Techie Tactics 101.

Index. 0201700476T10012003

Read More Show Less

Preface

One day I started writing, not knowing that I had chained myself for life to a noble but merciless master. When God hands you a gift, he hands you a whip; and the whip is intended solely for self-flagellation. . . . I'm here alone in my dark madness, all by myself with my deck of cards--and, of course, the whip God gave me.
--Truman Capote (In Music for Chameleons. New York: Vintage Books, 1994)

I wrote this book to get inside SQL Server. I wanted to see what we could learn about the product and the technologies on which it's based through the use of a freely downloadable debugger, a few well-placed xprocs, and a lot of tenacity. The book you're reading is the result of that experiment.

In my two previous SQL Server books, I focused more on the pragmatic aspects of SQL Server--how to program it and how to make practical use of its many features. As the title suggests, this book focuses more on the architectural design of the product. Here, we dwell on the technical underpinnings of the product more than on how to use it. It's my belief that understanding how the product works will make you a better SQL Server practitioner. You will use the product better and leverage its many features more successfully in your work because you will have a deeper understanding of how those features work and how they were intended to be used.

About Books Online

As with my previous books, one of the design goals of this book was to avoid needlessly repeating the information in Books Online. This necessitated omitting certain subjects that you might expect to find in a book like this. For example, I had originally planned to include an overview chapter that covered the architectural layout of the product from a high-level point of view. I had also planned to have a chapter on the architecture of the storage engine. However, on rereading the coverage of these subjects in Books Online (see the topic SQL Server Architecture Overview and the subtopics it links) and in other sources, I didn't feel I could improve on it substantially.

My purpose isn't to fill these pages with information that is already readily available to you; it is to pick up where the product documentation (and other books and whitepapers) leave off and take the discussion to the next level. As such, in this book I assume that you've read through Books Online and that you understand the basic concepts it relates.

About WinDbg

This book features a good deal of work with WinDbg, Microsoft's freely downloadable symbolic debugger. You may be wondering why we need a debugger to explore SQL Server in the first place. After all, we obviously aren't going to "debug" SQL Server, and we certainly don't have source code for it, so we won't be stepping through code as is typically the case with a debugger.

The reason we use a debugger is that it gives us the ability to look under the hood of a running process in ways no other tool can. A debugger lets us see the threads currently running inside the process, their current call stacks, the state of virtual memory and heaps within the process, and various other important process-wide and thread-specific data. It lets us set breakpoints, view registers, and see when DLLs are loaded by the process or rebased by Windows. It lets us pause execution, dump memory regions, and save and restore the complete process state. In short, a debugger provides a kind of "X-ray" facility--a tool that lets us peer inside a process and see what's really going on within it. In this case, the object of our interest is SQL Server, but the basic debugging skills you'll learn in this book could be used to investigate any Win32 application. One of the chief goals of this book is to equip you with some basic coding and debugging skills so that you can continue the exploration of SQL Server on your own.

If we are to truly get inside the product and understand how it works, using a debugger is a must. Trying to understand the internal workings of a technology by merely reading about it in books or whitepapers is like trying to learn about a foreign country without actually visiting it--there's no substitute for just going there.

Given that WinDbg is freely downloadable from the Microsoft Web site, has the features we need, and is relatively easy to use, it seems the obvious choice. A symbolic debugger, it can use the symbols that ship with SQL Server and that are publicly available over the Internet, so it's a suitable choice for exploring the inner workings and architectural design of the product.

About the Fundamentals

You'll notice an emphasis in this book on understanding the technologies behind SQL Server in order to understand how it works. I spend several chapters going through the fundamentals of processes and threads, memory management, Windows I/O, networking, and several other topics. To the uninitiated, these topics may seem only tangentially related at best. After all, why do you need to know about asynchronous I/O to understand SQL Server? You need to know something about it and the other fundamental technologies on which SQL Server is based in order to have a proper frame of reference and to gain a deep understanding of how the product itself works. You need to understand the fundamental Windows concepts on which SQL Server, a complex Windows application, is based for the same reason that a medical student needs to understand basic biology in order to get into medical school: Without this fundamental knowledge, you lack the perspective and foundation necessary to properly root and ground the more advanced concepts you will be attempting to learn. Humans learn by association--by associating new data with knowledge already acquired. Without a solid grounding in the fundamentals of Windows application design, you lack the basic knowledge required to systematically associate the details of how a complex Windows application such as SQL Server works.

To be sure, you can gain a superficial idea of how SQL Server works (for example, by reading that it makes use of scatter-gather I/O) without really understanding what the details mean. If you really want to master the product--if you really want to know it literally inside-out--you have to have some understanding of the technologies from which it's composed. Knowing how scatter-gather I/O works will give you immediate insight into why SQL Server uses it and why it enhances performance. The same is true for virtual memory, thread synchronization, networking, and the many other foundational topics we explore in this book. Not only are they relevant; having a basic understanding of them is essential to truly understanding SQL Server. Without a basic understanding of the fundamental technologies on which SQL Server is based--Win32 processes and threads, virtual memory, asynchronous I/O, COM, Windows networking, and various others--you have neither the tools nor the frame of reference to truly grasp how the product works or to master how to use it.

I fully realize that not every reader will be interested in the Windows technologies and APIs behind SQL Server's functionality. That's okay. If the nitty-gritty details of the Win32 APIs, how to use them, and how applications such as SQL Server typically employ them don't interest you, feel free to skip the Foundations section (Part I) of this book. There's still plenty of useful information in the rest of the book, and you don't have to understand every detail of every API to benefit from it.

About the "How-To"

I've tried very hard to provide the architectural details behind how the various components of SQL Server work without neglecting the discussion of how to apply them in practical use. I am still a coder at heart, and there is still plenty of "how-to" information in this book. At last count, there were some 900 source code files slated for inclusion on the book's CD. That's more than either of my last two books, both of which were very focused on putting SQL Server to practical use, as I've said.

In terms of the central topic of all three of my SQL Server books--namely, getting the most out of the product--I've attempted to elevate the discussion to an exploration of the architectural design behind the product without leaving behind my core reader base. Regardless of whether you came to this book expecting the mother lode of code and practical use information that you typically find in my books or you agree with me that understanding how the product works is key to using it effectively, I hope you won't be disappointed with what you find here.

About the Breadth of Topics

You will notice that this book covers a wide range of product features and technologies. It is not limited merely to the functionality provided within sqlservr.exe--it tries to cover the entire product. It's my opinion that a book that purports to discuss the internal workings and architectural design of a complex product such as SQL Server should cover the whole product, not just the functionality that resides within the core executable or product features that have been in place for many years. The world of SQL Server is a lot bigger than just a single executable. Prior to the 7.0 release of the product, I suppose you could get away with just covering the functionality provided by the main executable, but that's no longer the case and hasn't been for years. The product has matured and has broadened substantially with each new release.

This book isn't titled The Guru's Guide to sqlservr.exe--it's about all of SQL Server and how its many component pieces work and fit together. So, you'll see coverage in this book of what might seem like fringe SQL Server technologies such as Full Text Search, Notification Services, and SQLXML. We'll explore replication, DTS, and a host of other SQL Server technologies that are not implemented in the main SQL Server executable. Of necessity, I can't cover every feature in the product or even as many as I'd like. The book would take ten years to write and would be 5,000 pages long. However, I've tried to strike a balance between covering topics in the depth that people have come to expect from my books and exploring a sufficient breadth of features and technologies such that you can get a good feel for the overall design and architecture of SQL Server as a product.

About C++

I'm fully aware that many SQL Server people are more comfortable in Visual Basic than in any C or C++ dialect. I used C and C++ to cover Windows programming fundamentals and elsewhere in the book for a couple of reasons.

First, the Win32 API itself is written in C. Although whole books have been written on accessing the Win32 API from VB, it has been my experience that this ranges from clunky to outright impossible in some circumstances, depending on the API function in question. The Win32 API was originally written in C, and therefore C and C++ are the purest and most direct methods of accessing it. Any other approach--be it from VB, Delphi, C#, or some other language or tool--adds a layer of indirection that can cloud the discussion.

Second, I used C++ because I happen to believe that the language is not that hard to learn and that most VB people are more than capable of developing basic C++ programming skills and effectively reading C++ code, regardless of whether they believe that themselves. There seems to be a natural aversion or fear of all things C++ among those in the VB community. It's my belief that these concerns are largely unfounded and that they needlessly limit people's ability to really understand Windows and complex Windows apps such as SQL Server. My advice: Even if you don't know C++ and feel you're out of your depth when reading through C++ code, don't be afraid of it. Work through the examples in this book, follow the instructions I provide, and see where your exploration leads you. Pick up an introductory book on the language if it suits you. You may find that the language isn't nearly as hard to get around in as you thought, and you may benefit--perhaps immensely--from the experience.

All that said, C++ is far from the only language used in this book. I know that no one language is used by everyone so I've tried to keep the book balanced in terms of the language tools used. A good deal of the example code used throughout the book is some flavor of Visual Basic--VB6, VBScript, or VB.NET. In the ODSOLE chapter, for example, I show you how to build COM objects in VB6. In the SQLXML chapter, I show you how to access SQLXML using VBScript. And in the Notification Services chapter, I show you how to implement a subscription management application using VB.NET. There's also a healthy helping of C#, Delphi, CMD files, and even a discussion or two of assembly language. And, of course, there's a wealth of Transact-SQL code throughout the book. Regardless of your preferred language(s), you should find code of interest to you in this book.

About Visual C++ 6.0

Some of you may question the decision to use Visual C++ 6.0 for most of the C++ code examples in this book. I chose VC6 over Visual Studio .NET for two reasons: (1) having been around considerably longer, VC6 is much more pervasive, and (2) Visual Studio .NET (both the 2001 and the 2003 releases) will automatically upgrade VC6 projects when they are first opened. So, regardless of whether you have Visual Studio 6 or Visual Studio .NET, the C++ projects on the CD accompanying this book should open just fine for you. You should be able to compile and run them without incident. Also, when teaching basic Windows concepts such as thread synchronization and memory management, I do not use any version-specific features, so there's no advantage to using Visual Studio .NET over VC6.

About the Terms and Knowledge Measures

Readers of my previous books may notice a significant amount of "supplementary" material in several of the chapters. You'll likely notice the term definitions that precede some of the chapter discussions and the knowledge measures at the end of each discussion. Don't worry: I still hate filler material and have gone to great lengths to avoid unnecessary screen shots, summaries, and other devices commonly used to lengthen technical books.

Though I personally don't like putting together term definition tables, knowledge measures, and the like and have avoided them in previous books, a growing number of readers have asked for additions such as these in order to make my books more suitable for classroom use. Several of my previous books are regularly used in classroom settings even though, admittedly, those books do not lend themselves well to it. Therefore, I've finally decided to try to do something about that. If you do not find these sections particularly useful, feel free to skip over them. All of the data contained in the term definitions is also in the chapter text--you won't miss anything by skipping them. That said, you may find that having a basic understanding of some of the terms and concepts before we get into them in depth may be useful to you. It really comes down to your individual preferences.

I have intentionally not included the answers to the questions in the knowledge measure sections in order to get a feel for how much they are used. Again, this is an adaptation intended to make the book more usable in classroom scenarios. I may or may not continue it in future books, depending on how useful it proves to be. If you want the answers to the knowledge measure questions, e-mail me at khen@khen.com, and I'll provide them.

About SQL Server Versions

This book targets the latest release of SQL Server currently available, SQL Server 2000. Throughout the book, when you see a reference to SQL Server, you can assume that it definitely applies to SQL Server 2000 and probably to other releases as well. I rarely mention SQL Server's version number because I've found it to be a little cumbersome. That said, when in doubt, assume what you read in this book applies to SQL Server 2000.

About Master Programming

With the sheer volume of code and code-related discussions in this book, it might appear to some that I'm trying to turn you into a master programmer rather than a master SQL Server practitioner. Nothing could be further from the truth. In order to really address that concern, let's first define what a master programmer is.

To begin with, a master programmer is someone who likely codes for a living. You cannot develop expert-level coding skills and keep them sharp by merely studying other people's code or reading programming books. You have to get in there and get your hands dirty, and you have to keep doing it. Technology changes and software engineering evolves quickly enough that there's simply no substitute for coding every day.

Second, a master programmer is someone who doesn't just know how to churn out source code. A person I worked with once suggested that the defining characteristic of an expert coder is great typing skill! I laughed out loud at that assertion because being an expert coder has nothing to do with typing--I know expert coders who don't type well at all. That notion reminds me of what Truman Capote said when asked about Jack Kerouac's work: "That isn't writing at all, it's typing." (As quoted in New Republic, Feb. 9, 1959.) Just as good writing amounts to a lot more than typing, so does expert-level coding. Cranking out reams of source code does not a master programmer make. In fact, there's a paucity and efficacy about the code of the programming masters that often accomplishes an astonishing amount of work with a surprisingly small amount of code. The idea isn't to write lots of code; it is to write good code. It's a question of quality versus quantity.

Third, a master programmer is well rounded. A master programmer knows a number of languages and works on multiple operating systems and platforms. He does not use one language at the expense of all others regardless of the problem. He uses the right tool for the job and constantly seeks to broaden his horizons and immerse himself in the art and science of computer language mastery. A master programmer is not a "jack of all trades and master of none" but maintains expert-level skills in several areas at once.

Fourth, an expert programmer masters the operating system environment and fundamental technologies with which he works just as much as he masters programming languages. He knows that simply mastering the language with which he happens to be working on a particular project is not enough; he must also know a good deal about the operating system and the foundational components with which he will construct applications. Whether this is COM or EJB, Windows or Linux, the master programmer knows that he must also have expert-level knowledge of the environment in which his code will run and the components from which it will be constructed in order to produce software that is robust, efficient, and extensible.

Fifth, a master programmer keeps up with the technology and developments in software engineering. A master programmer can tell you the difference between the decorator design pattern and the facade design pattern. He can tell you why COM is preferable to plain DLL use and about the advantages the .NET Framework offers over COM. He can tell you where Java fits in the grand scheme of things and how it compares to other languages. You can mention the term "refactoring" to him without getting a blank stare, and he can describe the relationship between eXtreme Programming and Aspect-Oriented Programming. He may not work every day with these concepts and technologies, but he stays current enough with the industry in which he works to understand them conceptually, to be able to explain the relationships between them, and to be able to discuss them articulately.

Sixth, a master programmer is well read. He knows who Martin Fowler is. He reads Kent Beck, and he's well versed in Erich Gamma's work. He reads both technology-specific books as well as those related to software engineering as a discipline. He reads Steve McConnell, and he also reads Donald Knuth. He knows who Jon Bentley is, and he also knows Brian Kernighan's work. He is well versed in Grady Booch's work and also reads Charles Petzold. In a day and age in which technology and the engineering required to master and put it to practical use seem to evolve at the speed of light, one can't read too much or stay too current with the latest developments in the industry. A master programmer knows this and dedicates himself to a lifelong course of continuing education.

So, with this in mind, I hope it's obvious that I'm not trying to turn anyone into a master programmer. This book isn't about software development; it's about SQL Server. To the extent that I delve into subjects seemingly more related to coding than to SQL Server, there is a method to the madness: I am trying to help develop basic coding and debugging skills in those who may lack them so that they can better understand how and why SQL Server is designed the way it is and so that they can continue the exploration of SQL Server on their own. The whole thrust of this book is about gaining as deep an understanding of SQL Server as possible so that we can put it to better use in the real world.

0201700476P10012003

Read More Show Less

Introduction

One day I started writing, not knowing that I had chained myself for life to a noble but merciless master. When God hands you a gift, he hands you a whip; and the whip is intended solely for self-flagellation. . . . I'm here alone in my dark madness, all by myself with my deck of cards--and, of course, the whip God gave me.
--Truman Capote (In Music for Chameleons. New York: Vintage Books, 1994)

I wrote this book to get inside SQL Server. I wanted to see what we could learn about the product and the technologies on which it's based through the use of a freely downloadable debugger, a few well-placed xprocs, and a lot of tenacity. The book you're reading is the result of that experiment.

In my two previous SQL Server books, I focused more on the pragmatic aspects of SQL Server--how to program it and how to make practical use of its many features. As the title suggests, this book focuses more on the architectural design of the product. Here, we dwell on the technical underpinnings of the product more than on how to use it. It's my belief that understanding how the product works will make you a better SQL Server practitioner. You will use the product better and leverage its many features more successfully in your work because you will have a deeper understanding of how those features work and how they were intended to be used.

About Books Online

As with my previous books, one of the design goals of this book was to avoid needlessly repeating the information in Books Online. This necessitated omitting certain subjects that you might expect to find in a book like this. For example, I had originally planned to include an overview chapter that covered thearchitectural layout of the product from a high-level point of view. I had also planned to have a chapter on the architecture of the storage engine. However, on rereading the coverage of these subjects in Books Online (see the topic SQL Server Architecture Overview and the subtopics it links) and in other sources, I didn't feel I could improve on it substantially.

My purpose isn't to fill these pages with information that is already readily available to you; it is to pick up where the product documentation (and other books and whitepapers) leave off and take the discussion to the next level. As such, in this book I assume that you've read through Books Online and that you understand the basic concepts it relates.

About WinDbg

This book features a good deal of work with WinDbg, Microsoft's freely downloadable symbolic debugger. You may be wondering why we need a debugger to explore SQL Server in the first place. After all, we obviously aren't going to "debug" SQL Server, and we certainly don't have source code for it, so we won't be stepping through code as is typically the case with a debugger.

The reason we use a debugger is that it gives us the ability to look under the hood of a running process in ways no other tool can. A debugger lets us see the threads currently running inside the process, their current call stacks, the state of virtual memory and heaps within the process, and various other important process-wide and thread-specific data. It lets us set breakpoints, view registers, and see when DLLs are loaded by the process or rebased by Windows. It lets us pause execution, dump memory regions, and save and restore the complete process state. In short, a debugger provides a kind of "X-ray" facility--a tool that lets us peer inside a process and see what's really going on within it. In this case, the object of our interest is SQL Server, but the basic debugging skills you'll learn in this book could be used to investigate any Win32 application. One of the chief goals of this book is to equip you with some basic coding and debugging skills so that you can continue the exploration of SQL Server on your own.

If we are to truly get inside the product and understand how it works, using a debugger is a must. Trying to understand the internal workings of a technology by merely reading about it in books or whitepapers is like trying to learn about a foreign country without actually visiting it--there's no substitute for just going there.

Given that WinDbg is freely downloadable from the Microsoft Web site, has the features we need, and is relatively easy to use, it seems the obvious choice. A symbolic debugger, it can use the symbols that ship with SQL Server and that are publicly available over the Internet, so it's a suitable choice for exploring the inner workings and architectural design of the product.

About the Fundamentals

You'll notice an emphasis in this book on understanding the technologies behind SQL Server in order to understand how it works. I spend several chapters going through the fundamentals of processes and threads, memory management, Windows I/O, networking, and several other topics. To the uninitiated, these topics may seem only tangentially related at best. After all, why do you need to know about asynchronous I/O to understand SQL Server? You need to know something about it and the other fundamental technologies on which SQL Server is based in order to have a proper frame of reference and to gain a deep understanding of how the product itself works. You need to understand the fundamental Windows concepts on which SQL Server, a complex Windows application, is based for the same reason that a medical student needs to understand basic biology in order to get into medical school: Without this fundamental knowledge, you lack the perspective and foundation necessary to properly root and ground the more advanced concepts you will be attempting to learn. Humans learn by association--by associating new data with knowledge already acquired. Without a solid grounding in the fundamentals of Windows application design, you lack the basic knowledge required to systematically associate the details of how a complex Windows application such as SQL Server works.

To be sure, you can gain a superficial idea of how SQL Server works (for example, by reading that it makes use of scatter-gather I/O) without really understanding what the details mean. If you really want to master the product--if you really want to know it literally inside-out--you have to have some understanding of the technologies from which it's composed. Knowing how scatter-gather I/O works will give you immediate insight into why SQL Server uses it and why it enhances performance. The same is true for virtual memory, thread synchronization, networking, and the many other foundational topics we explore in this book. Not only are they relevant; having a basic understanding of them is essential to truly understanding SQL Server. Without a basic understanding of the fundamental technologies on which SQL Server is based--Win32 processes and threads, virtual memory, asynchronous I/O, COM, Windows networking, and various others--you have neither the tools nor the frame of reference to truly grasp how the product works or to master how to use it.

I fully realize that not every reader will be interested in the Windows technologies and APIs behind SQL Server's functionality. That's okay. If the nitty-gritty details of the Win32 APIs, how to use them, and how applications such as SQL Server typically employ them don't interest you, feel free to skip the Foundations section (Part I) of this book. There's still plenty of useful information in the rest of the book, and you don't have to understand every detail of every API to benefit from it.

About the "How-To"

I've tried very hard to provide the architectural details behind how the various components of SQL Server work without neglecting the discussion of how to apply them in practical use. I am still a coder at heart, and there is still plenty of "how-to" information in this book. At last count, there were some 900 source code files slated for inclusion on the book's CD. That's more than either of my last two books, both of which were very focused on putting SQL Server to practical use, as I've said.

In terms of the central topic of all three of my SQL Server books--namely, getting the most out of the product--I've attempted to elevate the discussion to an exploration of the architectural design behind the product without leaving behind my core reader base. Regardless of whether you came to this book expecting the mother lode of code and practical use information that you typically find in my books or you agree with me that understanding how the product works is key to using it effectively, I hope you won't be disappointed with what you find here.

About the Breadth of Topics

You will notice that this book covers a wide range of product features and technologies. It is not limited merely to the functionality provided within sqlservr.exe--it tries to cover the entire product. It's my opinion that a book that purports to discuss the internal workings and architectural design of a complex product such as SQL Server should cover the whole product, not just the functionality that resides within the core executable or product features that have been in place for many years. The world of SQL Server is a lot bigger than just a single executable. Prior to the 7.0 release of the product, I suppose you could get away with just covering the functionality provided by the main executable, but that's no longer the case and hasn't been for years. The product has matured and has broadened substantially with each new release.

This book isn't titled The Guru's Guide to sqlservr.exe--it's about all of SQL Server and how its many component pieces work and fit together. So, you'll see coverage in this book of what might seem like fringe SQL Server technologies such as Full Text Search, Notification Services, and SQLXML. We'll explore replication, DTS, and a host of other SQL Server technologies that are not implemented in the main SQL Server executable. Of necessity, I can't cover every feature in the product or even as many as I'd like. The book would take ten years to write and would be 5,000 pages long. However, I've tried to strike a balance between covering topics in the depth that people have come to expect from my books and exploring a sufficient breadth of features and technologies such that you can get a good feel for the overall design and architecture of SQL Server as a product.

About C++

I'm fully aware that many SQL Server people are more comfortable in Visual Basic than in any C or C++ dialect. I used C and C++ to cover Windows programming fundamentals and elsewhere in the book for a couple of reasons.

First, the Win32 API itself is written in C. Although whole books have been written on accessing the Win32 API from VB, it has been my experience that this ranges from clunky to outright impossible in some circumstances, depending on the API function in question. The Win32 API was originally written in C, and therefore C and C++ are the purest and most direct methods of accessing it. Any other approach--be it from VB, Delphi, C#, or some other language or tool--adds a layer of indirection that can cloud the discussion.

Second, I used C++ because I happen to believe that the language is not that hard to learn and that most VB people are more than capable of developing basic C++ programming skills and effectively reading C++ code, regardless of whether they believe that themselves. There seems to be a natural aversion or fear of all things C++ among those in the VB community. It's my belief that these concerns are largely unfounded and that they needlessly limit people's ability to really understand Windows and complex Windows apps such as SQL Server. My advice: Even if you don't know C++ and feel you're out of your depth when reading through C++ code, don't be afraid of it. Work through the examples in this book, follow the instructions I provide, and see where your exploration leads you. Pick up an introductory book on the language if it suits you. You may find that the language isn't nearly as hard to get around in as you thought, and you may benefit--perhaps immensely--from the experience.

All that said, C++ is far from the only language used in this book. I know that no one language is used by everyone so I've tried to keep the book balanced in terms of the language tools used. A good deal of the example code used throughout the book is some flavor of Visual Basic--VB6, VBScript, or VB.NET. In the ODSOLE chapter, for example, I show you how to build COM objects in VB6. In the SQLXML chapter, I show you how to access SQLXML using VBScript. And in the Notification Services chapter, I show you how to implement a subscription management application using VB.NET. There's also a healthy helping of C#, Delphi, CMD files, and even a discussion or two of assembly language. And, of course, there's a wealth of Transact-SQL code throughout the book. Regardless of your preferred language(s), you should find code of interest to you in this book.

About Visual C++ 6.0

Some of you may question the decision to use Visual C++ 6.0 for most of the C++ code examples in this book. I chose VC6 over Visual Studio .NET for two reasons: (1) having been around considerably longer, VC6 is much more pervasive, and (2) Visual Studio .NET (both the 2001 and the 2003 releases) will automatically upgrade VC6 projects when they are first opened. So, regardless of whether you have Visual Studio 6 or Visual Studio .NET, the C++ projects on the CD accompanying this book should open just fine for you. You should be able to compile and run them without incident. Also, when teaching basic Windows concepts such as thread synchronization and memory management, I do not use any version-specific features, so there's no advantage to using Visual Studio .NET over VC6.

About the Terms and Knowledge Measures

Readers of my previous books may notice a significant amount of "supplementary" material in several of the chapters. You'll likely notice the term definitions that precede some of the chapter discussions and the knowledge measures at the end of each discussion. Don't worry: I still hate filler material and have gone to great lengths to avoid unnecessary screen shots, summaries, and other devices commonly used to lengthen technical books.

Though I personally don't like putting together term definition tables, knowledge measures, and the like and have avoided them in previous books, a growing number of readers have asked for additions such as these in order to make my books more suitable for classroom use. Several of my previous books are regularly used in classroom settings even though, admittedly, those books do not lend themselves well to it. Therefore, I've finally decided to try to do something about that. If you do not find these sections particularly useful, feel free to skip over them. All of the data contained in the term definitions is also in the chapter text--you won't miss anything by skipping them. That said, you may find that having a basic understanding of some of the terms and concepts before we get into them in depth may be useful to you. It really comes down to your individual preferences.

I have intentionally not included the answers to the questions in the knowledge measure sections in order to get a feel for how much they are used. Again, this is an adaptation intended to make the book more usable in classroom scenarios. I may or may not continue it in future books, depending on how useful it proves to be. If you want the answers to the knowledge measure questions, e-mail me and I'll provide them.

About SQL Server Versions

This book targets the latest release of SQL Server currently available, SQL Server 2000. Throughout the book, when you see a reference to SQL Server, you can assume that it definitely applies to SQL Server 2000 and probably to other releases as well. I rarely mention SQL Server's version number because I've found it to be a little cumbersome. That said, when in doubt, assume what you read in this book applies to SQL Server 2000.

About Master Programming

With the sheer volume of code and code-related discussions in this book, it might appear to some that I'm trying to turn you into a master programmer rather than a master SQL Server practitioner. Nothing could be further from the truth. In order to really address that concern, let's first define what a master programmer is.

To begin with, a master programmer is someone who likely codes for a living. You cannot develop expert-level coding skills and keep them sharp by merely studying other people's code or reading programming books. You have to get in there and get your hands dirty, and you have to keep doing it. Technology changes and software engineering evolves quickly enough that there's simply no substitute for coding every day.

Second, a master programmer is someone who doesn't just know how to churn out source code. A person I worked with once suggested that the defining characteristic of an expert coder is great typing skill! I laughed out loud at that assertion because being an expert coder has nothing to do with typing--I know expert coders who don't type well at all. That notion reminds me of what Truman Capote said when asked about Jack Kerouac's work: "That isn't writing at all, it's typing." (As quoted in New Republic, Feb. 9, 1959.) Just as good writing amounts to a lot more than typing, so does expert-level coding. Cranking out reams of source code does not a master programmer make. In fact, there's a paucity and efficacy about the code of the programming masters that often accomplishes an astonishing amount of work with a surprisingly small amount of code. The idea isn't to write lots of code; it is to write good code. It's a question of quality versus quantity.

Third, a master programmer is well rounded. A master programmer knows a number of languages and works on multiple operating systems and platforms. He does not use one language at the expense of all others regardless of the problem. He uses the right tool for the job and constantly seeks to broaden his horizons and immerse himself in the art and science of computer language mastery. A master programmer is not a "jack of all trades and master of none" but maintains expert-level skills in several areas at once.

Fourth, an expert programmer masters the operating system environment and fundamental technologies with which he works just as much as he masters programming languages. He knows that simply mastering the language with which he happens to be working on a particular project is not enough; he must also know a good deal about the operating system and the foundational components with which he will construct applications. Whether this is COM or EJB, Windows or Linux, the master programmer knows that he must also have expert-level knowledge of the environment in which his code will run and the components from which it will be constructed in order to produce software that is robust, efficient, and extensible.

Fifth, a master programmer keeps up with the technology and developments in software engineering. A master programmer can tell you the difference between the decorator design pattern and the facade design pattern. He can tell you why COM is preferable to plain DLL use and about the advantages the .NET Framework offers over COM. He can tell you where Java fits in the grand scheme of things and how it compares to other languages. You can mention the term "refactoring" to him without getting a blank stare, and he can describe the relationship between eXtreme Programming and Aspect-Oriented Programming. He may not work every day with these concepts and technologies, but he stays current enough with the industry in which he works to understand them conceptually, to be able to explain the relationships between them, and to be able to discuss them articulately.

Sixth, a master programmer is well read. He knows who Martin Fowler is. He reads Kent Beck, and he's well versed in Erich Gamma's work. He reads both technology-specific books as well as those related to software engineering as a discipline. He reads Steve McConnell, and he also reads Donald Knuth. He knows who Jon Bentley is, and he also knows Brian Kernighan's work. He is well versed in Grady Booch's work and also reads Charles Petzold. In a day and age in which technology and the engineering required to master and put it to practical use seem to evolve at the speed of light, one can't read too much or stay too current with the latest developments in the industry. A master programmer knows this and dedicates himself to a lifelong course of continuing education.

So, with this in mind, I hope it's obvious that I'm not trying to turn anyone into a master programmer. This book isn't about software development; it's about SQL Server. To the extent that I delve into subjects seemingly more related to coding than to SQL Server, there is a method to the madness: I am trying to help develop basic coding and debugging skills in those who may lack them so that they can better understand how and why SQL Server is designed the way it is and so that they can continue the exploration of SQL Server on their own. The whole thrust of this book is about gaining as deep an understanding of SQL Server as possible so that we can put it to better use in the real world.

Read More Show Less

Customer Reviews

Average Rating 5
( 3 )
Rating Distribution

5 Star

(3)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously
Sort by: Showing all of 3 Customer Reviews
  • Anonymous

    Posted May 2, 2004

    Wish I'd had these books 5 years ago

    own all three of Ken Henderson's SQL Server books. They stand apart from the rest of the crowd as the best books available on their respective subjects. Henderson takes a fresh approach to teaching that other authors would do well to emulate. What do I mean exactly? I mean this: Every point of any significance that is raised is illustrated with code when possible. There are hundreds of code examples in each of Henderson's books - many times what you usually see in DBA or programming books. No details are glosssed over. If you really want to know how something works or what the best approach is to doing something, you need these books. Another thing that is great about these books is how easy to read they are. Complex subjects are regularly broached with explanations and teaching that practically anyone could understand. Topics that trip up other authors or that they skip altogether are discussed in terms that anyone can grasp. It is difficult to convey just how important this is, but suffice it to say that the books are simply easy to read. If you want to know SQL Server at an expert level, you need look no further. Get all three of Henderson's books and read them cover-to-cover.

    Was this review helpful? Yes  No   Report this review
  • Anonymous

    Posted November 28, 2003

    Complex optimisations

    Microsoft's SQL Server does not have all the functionality of Oracle or IBM's databases. But Microsoft has put a huge amount of work into beefing it up, as shown in this book. Henderson has done far more than just write a straightforward guide to the query syntax. In fact, the latter is not the emphasis here. Rather, he explains how SQL Server integrates into the Microsoft operating system, for one thing. Like memory mapped files, WinSock and network connections, and how they relate to SQL Server. He provides a long discussion of its memory management; crucial if you are heavily loading up the database. With queries and table joins, you get help on how to optimise, based on knowledge of SQL Server's innards. At over 900 pages, the breadth might seem daunting. But it reflects the complexity and potential of SQL Server. Henderson also provides some nice extra touches. Each chapter ends in a quick list of review questions, to see if you have been paying attention. Plus, at the book's end, he has 2 essays. One is on the drivel that constitutes most [all?] management fads. The second is on pseudo experts in newsgroups. The entire book is worth it, just for these essays! Very biting.

    Was this review helpful? Yes  No   Report this review
  • Anonymous

    Posted October 15, 2009

    No text was provided for this review.

Sort by: Showing all of 3 Customer Reviews

If you find inappropriate content, please report it to Barnes & Noble
Why is this product inappropriate?
Comments (optional)