SQL Server 2005 Practical Troubleshooting: The Database Engine [Multiple-component retail product]

About the Authors		ix
Preface		xii
Acknowledgments		xiv
Waiting and Blocking Issues		1	(46)
Wait Types		1	(2)
Troubleshooting Blocking		3	(1)
Identifying Blocking		3	(6)
Identifying Blocking Using sys.dm_os_waiting_tasks		3	(3)
Statistically Identifying Blocking		6	(3)
Identifying the Cause of Blocking		9	(4)
Current Statements and Plans		10	(1)
Blocking Patterns		10	(1)
Blocking Chains		11	(2)
Resource Type Specifics		13	(27)
Latches		13	(9)
Locks		22	(14)
External Wait Types		36	(1)
Timer and Queue Wait Types		36	(2)
IO Wait Types		38	(1)
Other Wait Types		39	(1)
Deadlocks		40	(1)
Monitoring Blocking		41	(3)
Wait Statistics		41	(1)
Current Wait Information		42	(2)
Conclusion		44	(1)
Other Resources		45	(2)
Data Corruption and Recovery Issues		47	(90)
Fundamentals		49	(1)
SQL Server 2005 Storage Internals		49	(19)
Database and File States		49	(2)
Resource Database		51	(1)
Catalog Views and Base System Tables		52	(10)
Allocation Structures		62	(4)
Database Checksum		66	(1)
Fast Recovery		66	(1)
Deferred Transactions		67	(1)
Read-Only Compressed Databases		67	(1)
SQL Server 2005 Enhancements		68	(4)
Backup Enhancements		68	(1)
Restore Enhancements		69	(1)
DBCC Checkdb Enhancements		70	(2)
Data Recovery Best Practices		72	(4)
Backup/Restore Best Practices		72	(2)
Database and Transaction Log Best Practices		74	(1)
DBCC Checkdb Best Practices		75	(1)
Data Recovery Troubleshooting Scenarios		76	(11)
System Database Recovery		76	(8)
Recovering the Resource Database		84	(2)
Failure to Create tempdb		86	(1)
Reinstalling the Operating System		86	(1)
User Database Inaccessible		87	(13)
Database Marked Recovery_Pending		87	(9)
Handling Deferred Transactions		96	(1)
Database Marked Suspect		97	(2)
Attach Database Failures		99	(1)
Backup/Restore Failures		100	(7)
Backup Failures		100	(5)
Restore Failures		105	(2)
Database Consistency Errors		107	(30)
Handling Database Consistency Runtime Errors		107	(5)
Handling DBCC Checkdb Errors		112	(12)
Repair Versus Restore		124	(1)
What Does Each Error Mean?		125	(1)
Explanation		125	(1)
User Action		125	(1)
What Does Repair_Allow_Data_Loss Really Mean?		126	(1)
Root Cause Analysis Before Recovering		127	(1)
What if Repair Doesn't Work?		127	(1)
Copying Data Versus Repair		128	(1)
Find the Root Cause of Corruption: The Checklist		128	(9)
Memory Issues		137	(46)
Introduction to Windows Memory Management		137	(10)
Internal Virtual Memory---Virtual Address Space		138	(3)
External Virtual Memory		141	(1)
Internal Physical Memory		141	(2)
External Physical Memory		143	(1)
Memory Pressure		144	(2)
NUMA Support		146	(1)
SQLOS and SQL Server Memory Management		147	(36)
Memory Node		148	(1)
Memory Clerks		149	(1)
Memory Objects		149	(1)
Memory Caches		150	(3)
Buffer Pool		153	(6)
Troubleshooting		159	(24)
Procedure Cache Issues		183	(42)
Procedure Cache Architecture		184	(18)
Types of Cached Objects		184	(5)
Structure of the Procedure Cache		189	(1)
Procedure Cache and Memory		190	(3)
Non-Cached Plans and Zero Cost Plans		193	(1)
Plan Sharing		193	(1)
Recompilation		194	(1)
Parameterization		195	(4)
How Cache Lookups Work		199	(2)
Cached Plan Reuse		201	(1)
Flushing the Procedure Cache		202	(1)
Common Cache-Related Problems and Solutions		202	(22)
Using the Procedure Cache to Identify Expensive Queries		202	(3)
Parameter Sniffing		205	(11)
High Compile Time Due to Poor Plan Reuse		216	(5)
High CPU Due to Excessive Cache Lookup Time		221	(1)
Memory Pressure Caused by Procedure Cache		222	(2)
Conclusion		224	(1)
Query Processor Issues		225	(48)
Query Processor Basics		225	(12)
Compilation-Execution Sequence		226	(1)
Execution Plans		227	(4)
Query Compilation and Plan Selection		231	(2)
Special Optimizations and Scenarios		233	(4)
Common Issues		237	(10)
Compilation Time and Parameterization		238	(4)
Indexing		242	(3)
Cardinality and Cost Estimation		245	(2)
Troubleshooting		247	(21)
Diagnostics		247	(7)
Control		254	(14)
Best Practices		268	(2)
Use Set-Oriented Programming		268	(1)
Provide Constraints and Statistics Information		268	(1)
Watch Complex Constructs		269	(1)
Avoid Dynamic Language Features When Possible		269	(1)
Further Reading		270	(3)
Server Crashes and Other Critical Failures		273	(58)
Fundamentals		274	(11)
SQL Server 2005 Server Recovery Internals		274	(6)
SQL Server 2005 Enhancements		280	(5)
Critical Errors and Server Recovery Troubleshooting		285	(46)
Troubleshooting Server Startup Failures		285	(6)
Troubleshooting Server-Critical Errors		291	(35)
Troubleshooting a Server Hang		326	(5)
Service Broker Issues		331	(38)
Broker Overview		332	(2)
Why Service Broker?		332	(1)
Service Broker Objects and Terms		333	(1)
Internal Architecture		333	(1)
Primary Diagnostic Tools and Methods		334	(5)
The Transmission Queue View		334	(1)
The SQL Profiler---Service Broker Trace Events		335	(4)
Error Log and NT Event Log		339	(1)
Broker Troubleshooting Walkthrough		339	(9)
Walkthrough of a Simple/Secure Broker Application		339	(9)
Other Service Broker Diagnostic Tools		348	(20)
Views		348	(16)
Perfmon		364	(3)
DBCC Checkdb		367	(1)
Further Reading		368	(1)
SQLOS and Scheduling Issues		369	(42)
SQLOS Architecture		370	(5)
Memory and CPU Nodes		371	(3)
Schedulers		374	(1)
Tasks and Workers		374	(1)
SQL Server and SQLOS		375	(1)
Configuration and Troubleshooting		375	(34)
Node Configuration		375	(1)
Network Connection Affinity		376	(3)
Scheduler		379	(3)
Tasks and Workers		382	(1)
Load-Balancing Tasks Between Schedulers		383	(1)
Max Worker Threads Configuration		383	(2)
Lightweight Pooling Configuration		385	(1)
Affinity Mask Configuration		386	(2)
Disk I/O Completion Processing		388	(1)
Preemptive I/O Completion Processing		389	(1)
Scheduler Monitor		390	(14)
Hardware Configuration		404	(2)
Dedicated Admin Connection		406	(3)
Further Reading		409	(2)
Tempdb Issues		411	(14)
What Has Improved in SQL Server 2005?		413	(3)
How Is Tempdb Space Consumed?		416	(3)
What Is a User Object?		416	(1)
What Is an Internal Object?		417	(1)
What Is a Version Store Object?		418	(1)
Practical Troubleshooting		419	(4)
What to Do If You Run Out of Space in Tempdb		419	(2)
What Is Tempdb Page Latch Contention?		421	(2)
Conclusion		423	(2)
Clustering Issues		425	(16)
Example		426	(3)
Tools		429	(1)
Get Performance to an Acceptable Level		430	(9)
Add a Node		432	(4)
Why Did My Clustered SQL Server Instance Fail Over?		436	(1)
Why Does It Take So Long to Fail Over?		437	(1)
After Failover No One Can Connect		438	(1)
Add Disks		438	(1)
Replace a Disk		439	(1)
Move a Database		439	(1)
Conclusion		439	(2)
The Aging Champion		441	(4)
Index		445

The authoring team is a mix of developers from the SQL Server development team and support professionals from Microsofts 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. Hes made a number of contributions to the product in the area of supportability. When hes 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 2005s 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 Gurus 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 masters 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. Hes 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 Servers blog located at blogs.msdn.com/slavao. When hes 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 bachelors 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 bikecurrently a 2004 Honda CRF250X. You may contact Cindy from her website http://cindygross.spaces.live.com/.