Professional SQL Server 2008 Internals and Troubleshooting

by Bolton, Christian; Langford, Justin; Ozar, Brent; Rowland-Jones, James; Kehayias, Jonathan; Gross, Cindy; Wort, Steven

• ISBN13: 9780470484289
• ISBN10: 0470484284
• Edition: 1st
• Format: Paperback
• Copyright: 2010-01-07
• Publisher: Wrox

Summary

The book shows how to approach troubleshooting SQL Server 2008 from a thorough understanding of both the SQL Server database architecture and internals as well as Windows OS internals. It shows how to master the use of specific troubleshooting tools, and how to interpret their output to quickly identify and resolve any performance issue on any server running SQL Server. Part 1 discusses how to develop a methodology for troubleshooting based on an understanding of SQL Server and Windows internals. It covers the core technical topics required to understand how SQL Server and Windows should be working. Best practices are discussed so you know how to pro-actively monitor and avoid problems.Part 2 deals with the "tools of the trade" detailing all the free and proprietary tools that professionals use to very quickly gather, analyze and pin down the source of a system wide performance issue. The book uniquely covers both SQL Server and Windows technologies that are necessary for ensuring effective performance of SQL Server processing.

Author Biography

CHRISTIAN BOLTON is the Technical Director for Coeo Ltd., a leading provider of SQL Server consulting and managed support services in the UK and Europe. Prior to this Christian worked for 5 years at Microsoft, leading the SQL Server Premier Field Engineering team in the UK. He is a Microsoft Certifi ed Architect, Master and MVP for SQL Server, and co-author of Professional SQL Server 2005 Performance Tuning. He works out of London and lives in the south of England with his wife and children. He can be contacted at [e-mail]. Christian authored chapters 1, 2, 7, 13 and the online chapter 16 in addition to lending his authoring expertise where needed on other chapters and functioned as the lead author for the entire project.

JUSTIN LANGFORD leads the Managed Support team for Coeo Ltd, delivering outsourced 24x7 operations for mission-critical SQL Server platforms. Before joining Coeo, Justin worked for Microsoft in the Premier Field Engineering team and has worked with some of Microsoft’s largest fi nance and government customers in Europe. Justin co-authored Wrox Professional SQL Server 2005 Performance Tuning and lives in London with his girlfriend, Claire. Outside work he enjoys sailing and has a keen interest in classic British sports cars. Justin can be contacted at [e-mail]. Justin authored chapters 9, 11, 12, and 15.

BRENT OZAR is a SQL Server Expert for Quest Software. He has over a decade of broad IT experience, performing SQL Server database administration, systems administration, SAN administration, virtualization administration, and project management. In his current role, Brent trains DBAs on performance tuning, disaster recovery, and virtualization. He has spoken around the globe at events for PASS, SQLBits, SSWUG, and other organizations. Brent founded the Virtualization Virtual Chapter for the Professional Association for SQL Server (PASS), and serves as the Editor-in-Chief at SQLServerPedia.com. Brent blogs at www.BrentOzar.com and discusses diverse topics at http://twitter.com/BrentO. When he’s not talking SQL Server, he enjoys traveling, working with social media, snorkeling, and sampling new restaurants. He is the author of chapters 4 and 14.

JAMES ROWLAND-JONES works for EMC Consulting EMEA as an Advisory Consultant. His principle focus is the delivery of large, scalable, data warehousing, and business intelligence projects. Within this fi eld James specializes in data integration, database architecture, and performance tuning. He is very active in the technical community and is one of the organizers for SQLBits, Europe’s largest SQL Server community conference. James has received the Microsoft MVP award for 2009 and 2010. You can fi nd him online at http://consultingblogs.emc.com/jamesrowlandjones, twitter (@jrowlandjones), linkedin http://www.linkedin.com/in/jrowlandjones, or even using an old fashioned email, [e-mail]. James authored chapters 6 and 10.

JONATHAN KEHAYIAS is a SQL Server MVP, MCITP Database Administrator and Developer, who got started in SQL Server in 2004 as a database developer and report writer in the natural gas industry. After spending two and a half years working in T-SQL, in late 2006, he transitioned to the role of Database Administrator. He has experience in upgrading and consolidating SQL environments, and has experience in running SQL Server in Virtual Environments on VMWare ESX 3.5+. He is a member of the Tampa SQL Server User Group and a regular speaker about SQL Server at events. Jonathan authored chapter 3.

CINDY GROSS started her SQL Server life as a DBA with a hospital and health plan company in 1993, and moved to Microsoft in 2000 where she has worked ever since. Her roles at Microsoft have included PSS Product Support Engineer, SQL Content Lead, Yukon Readiness Lead, and most recently Dedicated Support Engineer (DSE), all for SQL Server. Cindy enjoys delivering training throughout the United States as well as in Europe and India, including presentations at SQL PASS. In 2008 she started the Boise SQL Server User Group, an affi liate of the SQLCommunity.org site (where she is a worldwide SQL Server Community Leader) to share SQL Server wisdom in the Idaho SQL Server community. Most recently she obtained the Microsoft Certifi ed Master: SQL Server Qualifi cation, which was a hard-fought prize. Over the years Cindy has learned from many wonderful friends and colleagues and they all deserve a word of thanks for contributing to her success. Cindy authored chapter 8.

STEVEN WORT has been working with SQL Server since the early days of SQL Server way back in 1992-93. He is currently a developer in the Windows Division at Microsoft, where he works on performance and scalability issues on large database systems for the Windows Telemetry team. Steven has been at Microsoft since 2000. Prior to working in the Windows Division, Steven spent 2 years working in the SQL Server group, working on performance and scalability. Steven’s fi rst 3 years at Microsoft were spent working in support as an escalation engineer on the SIE team. During this time, Steven was able to travel the world working with some of Microsoft’s customers on their performance and scalability issues. Before coming to Microsoft, Steven spent 20 years working in the United Kingdom as a freelance consultant, specializing in database application development. When Steven isn’t busy working, he can be found spending time with his family and enjoying many fi tness activities in the outdoors of the Pacifi c Northwest. Steven authored chapter 5.

Table of Contents

INTRODUCTION.

CHAPTER 1 SQL Server Architecture.

CHAPTER 2 Understanding Memory.

CHAPTER 3 SQL Server Waits and Extended Events.

CHAPTER 4 Working with Storage.

CHAPTER 5 CPU and Query Processing.

CHAPTER 6 Locking and Latches.

CHAPTER 7 Knowing Tempdb.

CHAPTER 8 Defi ning Your Approach To Troubleshooting.

CHAPTER 9 Viewing Server Performance with PerfMon and the PAL Tool.

CHAPTER 10 Tracing SQL Server with SQL Trace and Profiler.

CHAPTER 11 Consolidating Data Collection with SQLDiag and the PerfStats Script.

CHAPTER 12 Introducing RML Utilities for Stress Testing and Trace File Analysis.CHAPTER 13 Bringing It All Together with SQL Nexus.

CHAPTER 14 Using Management Studio Reports and the Performance Dashboard.

CHAPTER 15 Using SQL Server Management Data Warehouse.

CHAPTER 16 Shortcuts to Effi cient Data Collection and Quick Analysis.

INDEX.