Inside Microsoft SQL Server 2005: The Storage Engine (Solid Quality Learning)
| For me, the most wonderful thing about writing a book and having it published is getting feedback from you, the readers. Of course, it's also one of the worst things when I get feedback from readers who are frustrated that their favorite topics were not included. However, by this third time around, I think I can accept the fact that this book cannot be all things to all people, as much as I might want it to be. Microsoft SQL Server 2005 is such a huge, complex product that not even with a new multi-volume format can we cover every feature. My hope is that you'll look at the cup as half full instead of half empty and appreciate the volumes of Inside Microsoft SQL Server 2005 for what they do include. As for the topics that aren't included, I hope you'll find the information you need in other sources. The focus of this series, as the name Inside implies, is on the core SQL Server enginein particular, the query processor and the storage engine. This series doesn't talk about client programming interfaces, heterogeneous queries, business intelligence, or replication. In fact, most of the high-availability features are not covered, but a few, such as mirroring, are mentioned at a high level when we discuss database property settings. I don't drill into the details of some internal operations, such as securityI had to draw the line somewhere or else we would need 10 volumes in the series and have no hope of finishing it before the release of the next version of the product! A History of Inside Microsoft SQL Server
The first edition of Inside Microsoft SQL Server, written for version 6.5, did attempt to cover almost all features of the product. Back then, the product was much smaller. Also, few other SQL Server books were available, so the original author, Ron Soukup, couldn't just refer his readers to other sources for information on certain topics. Even so, some topics were not covered in the first edition, including replication and security. Ron also did not cover any details of backing up or restoring a SQL Server database, and he didn't really discuss the use and management of the transaction log. I took over the book for SQL Server 7.0, and I completely rewrote many of the sections describing the internals of the storage engine because the entire storage engine had changed. The structure of pages, index organization, and management of locking resources were all completely different in version 7.0. Inside Microsoft SQL Server 7.0 discussed transactions, stored procedures, and triggers all in one chapter. For the SQL Server 2000 edition, with the new feature of user-defined functions and new trigger capabilities, I split these topics into two chapters. In the 7.0 edition, query processing and tuning were covered in one huge chapter; the SQL Server 2000 edition separated these topics into two chapters, one dealing with the internals of query processing and how the SQL Server optimizer works and the other providing guidance on how to write better-performing queries. Inside Microsoft SQL Server 2000 also included many details about the workings of the transaction log, as well as an in-depth discussion of how the log is used during backup and restore operations. Series Structure
Early in the planning stages for Inside Microsoft SQL Server 2005, I realized that it would be impossible to cover everything I wanted to cover in a single volume. My original thought was to have one volume on the storage engine components and the actual data management and a second volume on using the Transact-SQL (T-SQL) language and optimizing queries. I soon realized that this second topic itself was too big for a single volume, partly because SQL Server 2005 has so many new Transact-SQL features. Adequate coverage of all the new programming constructs would require a volume of its own, so at that point I invited T-SQL guru Itzik Ben-Gan to write a volume on Transact-SQL in SQL Server 2005. Itzik is an extremely prolific writer, and he had over 500 pages written before I completed the planning for my storage engine volume. At that point, he realized that the Transact-SQL language itself was too big for a single volume and that his work would need two volumes to cover everything we felt was necessary. So Inside Microsoft SQL Server 2005 is a work in four volumes. Although one goal of ours was to minimize the amount of overlap between volumes so that readers of the complete series would not have to deal with duplicate content, we also realized that not everyone would start with the same volume. Itzik and I have different approaches to describing SQL Server query processing, index use, and tuning, so when those topics are covered in more than one volume, that duplication is actually a bonus. Inside Microsoft SQL Server 2005: T-SQL Querying
The T-SQL querying volume describes the basic constructs of the Transact-SQL query language and presents a thorough discussion of logical and physical query processing. It also introduces a methodology for query tuning. Itzik provides a detailed discussion of the use and behavior of all the new T-SQL query constructs, including CTEs, the PIVOT and UNPIVOT operators, and ranking functions. He covers enhancements to the TOP clause and provides examples of many new and useful ways to incorporate aggregation into your queries. New capabilities of data modification operations (INSERT, UPDATE, and DELTE) are also described in depth. Inside Microsoft SQL Server 2005: T-SQL Programming
The T-SQL programming volume focuses on the programmability features of the T-SQL language and covers the planning and use of transactions, stored procedures, functions, and triggers in your SQL Server applications. Itzik compares set-based and cursor programming techniques and describes how to determine which technique is appropriate, and he covers CLR versus relational programming, again describing which model is appropriate for which activities. The book covers the use of temporary objects and explores the new error-handling functionality in SQL Server 2005. Itzik discusses issues with working with various datatypes, including XML data and user-defined CLR datatypes. Finally, there is a chapter on SQL Server Service Broker, which allows controlled asynchronous processing in database applications. Inside Microsoft SQL Server 2005: The Storage Engine
The volume you are holding covers the SQL Server 2005 storage engine. I started working on this volume by taking the chapters from Inside Microsoft SQL Server 2000 that dealt with storage issues and then determining which new features were appropriate to cover. I soon realized that some reorganization was necessary, and I ended up with a full chapter on the architecture of the SQL Server 2005 engine and a whole chapter on the transaction log. As in all previous editions, I go into great depth on the actual physical storage of both data and indexes in the data files, and I describe the way that the file space is allocated and managed. Undocumented trace flags and DBCC commands are introduced where appropriate to illustrate certain features and to allow you to confirm your understanding of SQL Server's behavior. New features in SQL Server 2005 are pointed out as I discuss them; here are some of the most important new features covered in detail in this volume. Note that other new features are mentioned but that not all are covered in depth.
Inside Microsoft SQL Server 2005: Query Tuning and Optimization
The final volume in the series will be based on real-world observations of the way SQL Server 2005 performs and will explain how to get the most out of the product in real applications. This will be a multi-author work, with each author covering the areas of his or her greatest expertise. Covered topics will include:
Examples and Scripts
Many of the features and behaviors described in this volume are illustrated using T-SQL code. Some of the code is just a few lines long, but other examples require very complex coding, including multi-way joins of some of the dynamic management views, all of which have impossibly long and difficult-to-type names. All code samples longer than a couple of lines are available for download from the companion Web site at www.InsideSQLServer.com/companion. Topics Not Covered
As I mentioned, even in four volumes, certain features and aspects of the product cannot be covered. Also keep in mind that the books in the series are not intended to be how-to books for database administrators or for database application programmers. They are intended to explain how SQL Server works behind the scenes, so you will have a solid foundation on which to build and troubleshoot your applications and will understand why SQL Server behaves the way it does. In addition to business intelligence (Analysis Services, Integration Services, and Reporting Services) and high availability (replication, database mirroring, log shipping, and clustering), other topics that are beyond the scope of this book include:
Caveats and Disclaimers
To illustrate some of the behaviors of SQL Server, this volume discusses some undocumented features of the product or undocumented objects, such as internal tables. Some of these are potentially "discoverable" on your own, usually by looking at the definition of the supported functions, procedures, or views. In those cases, I am simply saving you time by providing information that you could have eventually discovered on your own. Another category of undocumented features is undocumented DBCC commands or trace flags, which I introduce only for the purpose of allowing deeper analysis or more thorough observation of certain product behavior. For the most part, these are not discoverable unless someone tells you about them. Please keep in mind that undocumented means unsupported. This means that if you have additional questions about an undocumented feature that I describe, you cannot call Customer Support Services at Microsoft and expect the representative on the phone to answer your questions. There is also no guarantee that an undocumented feature will continue to behave in the same way in the next version of SQL Server. In some cases, undocumented features can change behavior in a service pack, and Microsoft will not be obligated to tell you about this change in a readme file or a Knowledge Base article. Throughout this volume, I will let you know when I refer to features or tools that are undocumented, and in some cases, I will also reiterate that Microsoft provides no support for them. However, consider this a global caveat for all such undocumented features. How to Get Support
Every effort has been made to ensure the accuracy of this book's content. If you run into problems, you can refer to one of the following sources. Companion Web Site
Despite my best intentions, as well as review by members of the SQL Server team at Microsoft, this book is not perfect, as no book is. Updates and corrections will be posted on the companion Web site at www.InsideSQLServer.com/companion. In addition, if you find anything that you think is incorrect, feel free to use the feedback form on that site to inform me of the problem. Microsoft Learning
Microsoft provides corrections for books at the following Web address: http://www.microsoft.com/learning/support To connect directly with the Microsoft Learning Knowledge Base and enter a query regarding an issue you have encountered, you can go to http://www.microsoft.com/learning/support/search.asp. In addition to sending feedback to the author, you can send comments or questions to Microsoft by using either of the following methods: Postal Mail: Microsoft LearningAttn: Inside Microsoft SQL Server 2005 Editor One Microsoft Way Redmond, WA 98052-6399 E-mail: mspinput@microsoft.com Please note that product support is not offered through the preceding addresses. For SQL Server support, go to www.microsoft.com/sql. You can also call Standard Support at 425-635-7011 weekdays between 6 A.M. and 6 P.M. Pacific time, or you can search Microsoft's Support Online at www.support.microsoft.com/support. I hope you find value in this book even if I haven't covered every single SQL Server topic that you're interested in. You can let me know what you'd like to learn more about, and I can perhaps refer you to other books or white papers. Or maybe I'll write an article for SQL Server Magazine. You can contact me via my Web site at www.InsideSQLServer.com. |