BN.com Gift Guide

SQL Server 2005 Practical Troubleshooting: The Database Engine

Multimedia Set (Print)
Used and New from Other Sellers
Used and New from Other Sellers
from $6.39
Usually ships in 1-2 business days
(Save 89%)
Other sellers (Multimedia Set)
  • All (10) from $6.39   
  • New (5) from $33.19   
  • Used (5) from $6.39   

Overview

Never-Before-Published Insiders’ Information for Troubleshooting SQL Server 2005.

This is the definitive guide to troubleshooting the Microsoft SQL Server 2005 database engine, direct from the people who know it most intimately: the people who wrote it, designed it, and support it. SQL Server expert Ken Henderson, author of the best-selling Guru’s Guides to SQL Server, has assembled a “dream team” of SQL Server developers and support engineers to provide in-depth troubleshooting and diagnostic information that has never been documented before: information that would be impossible to get without access to Microsoft’s own source code.

From caching to clustering, query processing to Service Broker, this book will help you address even the toughest problems with database engine operations. Each chapter begins with a brief architectural overview of a key SQL Server component, then drills down into the most common problems users encounter, offering specific guidance on investigating and resolving them. You’ll find comprehensive, in-depth chapters on

• Waiting and blocking

• Data corruption and recovery

• Memory

• Procedure cache issues

• Query processing

• Server crashes and other critical failures

• Service Broker

• SQLOS and scheduling

• tempdb

• Clustering

This is the indispensable resource for everyone who must keep SQL Server running smoothly: DBAs, database application developers, API programmers, and Web developers alike.

 

Contents

About the Authors   ix

Preface   xii

Acknowledgments   xiv

1   Waiting and Blocking Issues   1

2   Data Corruption and Recovery Issues   47

3    Memory Issues   137

4   Procedure Cache Issues   183

5   Query Processor Issues   225

6   Server Crashes and Other Critical Failures   273

7   Service Broker Issues   331

8   SQLOS and Scheduling Issues   369

9   Tempdb Issues   411

10   Clustering Issues   425

The Aging Champion   441

Index   445

Read More Show Less

Product Details

  • ISBN-13: 9780321447746
  • Publisher: Prentice Hall Professional Technical Reference
  • Publication date: 12/25/2006
  • Series: SQL Server Series
  • Pages: 480
  • Product dimensions: 6.99 (w) x 9.11 (h) x 1.01 (d)

Meet the Author

The authoring team is a mix of developers from the SQL Server development team and support professionals from Microsoft’s Customer Support Services organization. Seven developers from the SQL Server development team and three support professionals from Microsoft CSS contributed to this book.

SQL Server Development Team

 

August Hill has been a developer for more than 30 years. For the past six years he has been a member of the SQL Server Service Broker team. He’s made a number of contributions to the product in the area of supportability. When he’s not developing software he can be found playing guitar or tasting Washington wines. He can be reached at august.hill@microsoft.com.

 

Cesar Galindo-Legaria is the manager of the Query Optimizer group in SQL Server. He received a Ph.D. in computer science (databases) from Harvard University in 1992. After working for a graphics company in the Boston area, he went back to databases, doing post-doctoral visits in European research centers. In 1995 he joined Microsoft to work on a new relational query processor, first shipped with SQL Server 7.0, which introduced a fully cost-based query optimizer, a rich set of execution algorithms, and a number of auto-administration features. He has been working on query processing for SQL Server ever since. He holds several patents on query processing and optimization, and has published a number of research papers in that area.

 

Ken Henderson has been a developer for more than 25 years. He has worked with SQL Server since 1990 and has built software for a number of firms throughout his career, including H&R Block, the Central Intelligence Agency,the U.S. Navy, the U.S. Air Force, Borland International, JP Morgan, and various others. He joined Microsoft in 2001 and is currently a developer in the Manageability Platform group within the SQL Server development team. He is the creator of SQL Server 2005’s SQLDiag facility and spends his days working on SQL Server management tools and related technologies. He is the author of eight books on a variety of computing topics, including the popular Guru’s Guide series of SQL Server books available from Addison-Wesley. He lives with his family in the Dallas area and may be reached via email at khen@khen.com.

 

Sameer Tejani, originally from Arusha, Tanzania, has spent the past 10 years working at Microsoft in the SQL Server group. His work has exposed him to different areas of the SQL Server Engine, including the T-SQL execution framework, Open Data Services (ODS), connection management, User Mode Scheduler (UMS), and other areas. He is solely responsible for the infamous “non-yielding scheduler” error messages that support professionals have come to abhor! He is currently a software development lead in the SQL Server Security team. In his spare time, Sameer enjoys being outdoors and going on long bike rides. He lives with his wife Farhat in the Seattle area.

Santeri Voutilainen, better known as Santtu, has been a software design engineer in SQL Server storage engine team since 1999. He has worked closely on page allocation, latches, and the lock manager. A graduate of Harvard University, he is in the final stages of a master’s degree in computer science from the University of Washington. Although he calls Seattle home, Santeri was born in Finland and spent most of his young life in Nepal. He is an avid traveler and outdoorsman and spends his free time exploring the Pacific Northwest with his wife and one-year-old son. Santtu can be reached at sqlsanttu@vode.net.

 

Slava Oks is a software architect for the Storage Engine and Infrastructure team in SQL Server. He has been with Microsoft for more than nine years. During the SQL Server 2005 development, project he worked on architecture and implementation of SQLOS. He’s made a number of contributions to the product in the area of performance, scalability, supportability, and testability. He is also the author of a popular SQL Server’s blog located at blogs.msdn.com/slavao. When he’s not developing software he can be found playing sports or having fun with friends and family.

 

Wei Xiao worked on the design of the SQL Server Storage Engine in Microsoft from 1996 to April 2006. His main areas of focus are access methods, concurrency control, space management, logging, and recovery. He also worked on SQL Server performance monitoring and troubleshooting. He has spoken at several industry conferences, including Microsoft Tech Ed and SQL PASS. He is currently working on a Microsoft internal data storage project.

Microsoft Customer Support Services

 

Bart Duncan has worked with SQL Server and related technologies for about 10 years. He is currently an escalation engineer in the SQL Server product support group. Bart lives in Dallas, Texas, where he is fortunate to share a home with his wonderful wife, Dr. Andrea Freeman Duncan.

 

Bob Ward is a senior escalation engineer in Microsoft Customer Service and Support (CSS) based in the Microsoft Regional Support Center in Irving, Texas. He has worked with Microsoft for 13 years and has now supported every release of Microsoft SQL Server from 1.1 for OS/2 to SQL Server 2005. His background in the computer industry spans 20 years and includes database development projects with companies like General Dynamics, Harris Hospital, and American Airlines. Bob graduated with a bachelor’s degree in computer science from Baylor University in 1986. He currently lives in North Richland Hills, Texas, with his wife Ginger and two sons, Troy and Ryan. Bob spends his spare time coaching youth sports, cheering for the local professional sports teams, and sharpening his golf game for a dream of playing on the PGA Legends Tour.

Cindy Gross has been a member of the Texas Microsoft PSS support team for SQL Server and Analysis Services since 2000. Cindy has taken on many roles during this time, including support engineer, content lead, and Yukon readiness lead. Before joining Microsoft, Cindy was a SQL Server DBA for seven years, working on SQL Server versions 1.11 and later. She is an avid reader of science fiction and fantasy, with a special love for books starring women as fighters. Her favorite non-technical author is Sheri S. Tepper. Cindy spends many weekends racing her dirt bike–currently a 2004 Honda CRF250X. You may contact Cindy from her website http://cindygross.spaces.live.com/.

Read More Show Less

Read an Excerpt

PrefacePreface

I originally conceived this book as a means of getting Microsoft support engineers to write down the many things they had learned from supporting SQL Server over the years. When I joined Microsoft, I was surprised to learn that much of the practical knowledge related to supporting the product (what those in epistemology refer to as "domain knowledge") that had been acquired by the support people was not written down anywhere. It was often communicated verbally and passed down from person to person via oral traditions.

This, of course, led to people not knowing how to do their jobs unless someone was kind enough to show them the way. It was also extremely error prone. And it led to some of the most important knowledge about supporting the product being concentrated among just a handful of individuals—which worked out nicely for them, but not so well for the rest of the support group.

I had been a fulltime software developer for over twenty years before joining Microsoft. Much to my surprise, I discovered that the upper ranks of the support organization consisted mostly of people who had at one time been developers themselves. Often, they had something in the neighborhood of three to five years of experience as developers before becoming support engineers. As a career developer, it was difficult for me to imagine finding support work truly fulfilling on a long-term basis. Support work, it seemed to me, was something akin to the janitor's job of the software development world. Someone had to clean up after all those messy developers, and that task often fell to the support staff. Although I knew it was important work, I could notpersonally envision being really happy pursuing support work as a career. Nevertheless, here were several former developers who evidently were. This mystified me.

I began to think about how to level the playing field and make the knowledge possessed by the upper echelon of the support group available to everyone. It seemed to me that the deep understanding of how to support the product, the domain knowledge so prized by the folks on Mount Olympus, should be available to everyone in the organization. Everyone who supported the product should have equal access to it.

My initial thought was that I would document how to troubleshoot the product in the book I was working on at the time, The Guru's Guide to SQL Server Architecture and Internals. However, it soon became evident to me that developing software or understanding it from a developer's perspective differs substantially from troubleshooting it. They are really two different disciplines. Although some overlap certainly exists, there is enough that is specific to troubleshooting the product that it warrants exploration and coverage of its own.

When I finally finished my architecture book, I returned to this idea of somehow documenting the many low-level details and insights the support group had learned from years of troubleshooting the product—details not so much about how the product worked, but about how to solve tough problems relating to it. I began to discuss the idea with some of the support engineers to gauge their interest in it. I suggested that we do a multi-author project wherein they could codify their hard-won troubleshooting knowledge in print—not only for their fellow support engineers, but also for their customers. Many had never been published before, and I felt that potentially seeing their words in print might motivate some of them to finally put down in writing what had thus far only been divulged to a select few within the company.

Responses ranged from tepid to enthusiastic, depending on the support engineer. After running down the roster of who was and was not interested in working on the project, it became obvious that I needed more authors to round out the book. There simply were not enough in the support organization who were willing and able to join the project.

I could have gone outside the support group to Microsoft Consulting Services or completely outside the company to MVPs and the like, but I really wanted to limit the author corps to those who had seen the SQL Server source code. There is no substitute for having access to the source code and being able to step through it under a debugger. By studying the product's source, you can come to understand the SQL Server technology at a depth and to a degree that would otherwise be impossible.

So, still in need of additional authors, I decided to extend an invitation to some of the top developers on the product team. Although completely distinct from the support group within Microsoft and focused on developing products, not supporting them, I knew many of these people personally and knew that they had spent a fair amount of their time debugging and troubleshooting complex problems with the product, particularly the parts they had built. You cannot write complex code without having to debug it occasionally, and I felt confident that they would augment the book's coverage of practical troubleshooting in novel and interesting ways.

The response from my colleagues in the product group was roundly enthusiastic, and several of the top developers from the SQL Server team agreed to join the project. I was able to enlist the talents of the very people who wrote the code to talk about how to troubleshoot both complex and commonplace problems with it. You will not find this in any other book, and I am excited to have been a part of making it available to you.

My architecture book tells you how SQL Server works. This book tells you what to do when something goes wrong with it. The former is generally applicable, regardless of what you might be doing with the server. The latter is, hopefully, an edge case (because the product does not break that often), but is something that can make the difference between a SQL Server application meeting customer needs and it going down in flames. Hopefully, you won't have problems with SQL Server. If you do, this book is a good place to begin your troubleshooting expedition.

My thanks go out to my fellow developers on the SQL Server product team who wrote portions of this book: August Hill, Cesar Galindo-Legaria, Sameer Tejani, Santeri (Santtu) Voutilainen, Slava Oks, and Wei Xiao. I also want to thank the support engineers who lent their voices to the project: Bart Duncan, Bob Ward, and Cindy Gross. These people all have their own unique ways of thinking (and writing!), but you could not ask for a better cast of characters to accompany you as you tackle the practicalities of SQL Server 2005 troubleshooting.

Ken Henderson
August 21, 2006

© Copyright Pearson Education. All rights reserved.

Read More Show Less

Table of Contents

Preface

Chapter 1 Waiting and Blocking Issues

Chapter 2 Data Corruption and Recovery Issues

Chapter 3 Memory Issues

Chapter 4 Procedure Cache Issues

Chapter 5 Query Processor Issues

Chapter 6 Server Crashes and Other Critical Failures

Chapter 7 Service Broker Issues

Chapter 8 SQLOS and Scheduling Issues

Chapter 9 Tempdb Issues

Chapter 10   Clustering Issues

Index

Read More Show Less

Preface

Preface

I originally conceived this book as a means of getting Microsoft support engineers to write down the many things they had learned from supporting SQL Server over the years. When I joined Microsoft, I was surprised to learn that much of the practical knowledge related to supporting the product (what those in epistemology refer to as "domain knowledge") that had been acquired by the support people was not written down anywhere. It was often communicated verbally and passed down from person to person via oral traditions.

This, of course, led to people not knowing how to do their jobs unless someone was kind enough to show them the way. It was also extremely error prone. And it led to some of the most important knowledge about supporting the product being concentrated among just a handful of individuals—which worked out nicely for them, but not so well for the rest of the support group.

I had been a fulltime software developer for over twenty years before joining Microsoft. Much to my surprise, I discovered that the upper ranks of the support organization consisted mostly of people who had at one time been developers themselves. Often, they had something in the neighborhood of three to five years of experience as developers before becoming support engineers. As a career developer, it was difficult for me to imagine finding support work truly fulfilling on a long-term basis. Support work, it seemed to me, was something akin to the janitor's job of the software development world. Someone had to clean up after all those messy developers, and that task often fell to the support staff. Although I knew it was important work, I could not personally envision being really happy pursuing support work as a career. Nevertheless, here were several former developers who evidently were. This mystified me.

I began to think about how to level the playing field and make the knowledge possessed by the upper echelon of the support group available to everyone. It seemed to me that the deep understanding of how to support the product, the domain knowledge so prized by the folks on Mount Olympus, should be available to everyone in the organization. Everyone who supported the product should have equal access to it.

My initial thought was that I would document how to troubleshoot the product in the book I was working on at the time, The Guru's Guide to SQL Server Architecture and Internals. However, it soon became evident to me that developing software or understanding it from a developer's perspective differs substantially from troubleshooting it. They are really two different disciplines. Although some overlap certainly exists, there is enough that is specific to troubleshooting the product that it warrants exploration and coverage of its own.

When I finally finished my architecture book, I returned to this idea of somehow documenting the many low-level details and insights the support group had learned from years of troubleshooting the product—details not so much about how the product worked, but about how to solve tough problems relating to it. I began to discuss the idea with some of the support engineers to gauge their interest in it. I suggested that we do a multi-author project wherein they could codify their hard-won troubleshooting knowledge in print—not only for their fellow support engineers, but also for their customers. Many had never been published before, and I felt that potentially seeing their words in print might motivate some of them to finally put down in writing what had thus far only been divulged to a select few within the company.

Responses ranged from tepid to enthusiastic, depending on the support engineer. After running down the roster of who was and was not interested in working on the project, it became obvious that I needed more authors to round out the book. There simply were not enough in the support organization who were willing and able to join the project.

I could have gone outside the support group to Microsoft Consulting Services or completely outside the company to MVPs and the like, but I really wanted to limit the author corps to those who had seen the SQL Server source code. There is no substitute for having access to the source code and being able to step through it under a debugger. By studying the product's source, you can come to understand the SQL Server technology at a depth and to a degree that would otherwise be impossible.

So, still in need of additional authors, I decided to extend an invitation to some of the top developers on the product team. Although completely distinct from the support group within Microsoft and focused on developing products, not supporting them, I knew many of these people personally and knew that they had spent a fair amount of their time debugging and troubleshooting complex problems with the product, particularly the parts they had built. You cannot write complex code without having to debug it occasionally, and I felt confident that they would augment the book's coverage of practical troubleshooting in novel and interesting ways.

The response from my colleagues in the product group was roundly enthusiastic, and several of the top developers from the SQL Server team agreed to join the project. I was able to enlist the talents of the very people who wrote the code to talk about how to troubleshoot both complex and commonplace problems with it. You will not find this in any other book, and I am excited to have been a part of making it available to you.

My architecture book tells you how SQL Server works. This book tells you what to do when something goes wrong with it. The former is generally applicable, regardless of what you might be doing with the server. The latter is, hopefully, an edge case (because the product does not break that often), but is something that can make the difference between a SQL Server application meeting customer needs and it going down in flames. Hopefully, you won't have problems with SQL Server. If you do, this book is a good place to begin your troubleshooting expedition.

My thanks go out to my fellow developers on the SQL Server product team who wrote portions of this book: August Hill, Cesar Galindo-Legaria, Sameer Tejani, Santeri (Santtu) Voutilainen, Slava Oks, and Wei Xiao. I also want to thank the support engineers who lent their voices to the project: Bart Duncan, Bob Ward, and Cindy Gross. These people all have their own unique ways of thinking (and writing!), but you could not ask for a better cast of characters to accompany you as you tackle the practicalities of SQL Server 2005 troubleshooting.

Ken Henderson August 21, 2006

© Copyright Pearson Education. All rights reserved.

Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star

(0)

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 February 9, 2012

    One of the best...

    Of all the SQL Server how-to/troubleshooting manuals out there, this is one of the best. The title says it all...

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

    Posted June 24, 2007

    Good but not great

    This is a decent book. There is a lot of good info in it. I have read it cover-2-cover and really like it. I found a couple problems with it, tho. One is: dispite Ken Henderson's name being on the cover he did not actually write the book. He is just the editor. I am a long-time fan of his and was really looking forward to this book since the updated Guru guide books are not avail. yet. If you buy this book expecting Ken's excellent writing and teaching you will be disappointed. It is just not there. The actual authors seem like beginners by comparison. The only part Ken wrote was a great story at the end of the book. I always look forward to these stories but I wish he had written more of the book. I think they should have made this more clear on the book cover. When I saw Ken's name I thought he wrote the book. Only after I opened it and flipped through it did I learn the truth. The second problem is some of the chapters are too long. They could have used some editing. The server corruption chapter is a great example. It is extremely dull to begin with then keeps on repeating itself. It was just to long. I found myself falling to sleep while I read it-and this was in the middle of the day!! But this is a good book and one worth buying. But if you are waiting on a new 'Ken Henderson' book keep waiting. Hopefully the updated Guru guides will be out soon.

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

    Posted January 11, 2007

    specialised troubleshooting

    As the editor Henderson says in his Preface, this book is perhaps a little more specialised than his 'Guru's Guide to SQL Server Architecture'. That book was generally applicable to anyone coding SQL Server, while the current book is about what troubles might occur. Where these are expected [hopefully] to be fairly infrequent. The chapters are written by different contributors and touch on key aspects of the database. Each chapter can be read in isolation of the others. Of these, the chapter on server crashes and other critical failures might be the most important to some readers. Where maybe it is not so much the efficiency of how your SQL Server operates, but that it must run continuously. The chapter describes how an error log is made, and how to read its contents. As you'd expect, each entry is timestamped. You are walked through the standard startup sequence. Fairly intricate, reflecting the complexity of SQL Server itself. Multiple threads are associated with a normal run, and several databases are read - for the system and for the users. The chapter also warns you to only use the SQL Configuration Manager in order to change the parameters of the SQL services. As contrasted to using an alternative method which can cause errors. Here it seems implicitly that Microsoft erred in permitting that alternative to be even feasible. This warning should not be needed.

    Was this review helpful? Yes  No   Report 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)