INTRODUCTION
IF YOU’RE TROUBLESHOOTING an apparent “SQL Server” issue, you need to be able to troubleshoot the underlying operating system and storage as well as SQL Server. The aim of this book is to bring together and simplify the architectural details of these components before introducing you to the tools and techniques that very successful SQL Server Professionals use every day to get great results.
A fair amount of Windows and hardware internals’ information is available already, but very little of it condenses and filters the right material to be easily consumed by SQL Server professionals. The available material is either too light or too in-depth — with nothing to help bridge the gap.
Combining this need with the need for practical internals’ information on SQL Server and comprehensive introductions to troubleshooting tools available from the SQL Server Community and that come with SQL Server itself, three goals were established for this book:
- To provide in-depth architectural information on SQL Server (and the environment on which it depends) that is easy to consume
- To present a practical introduction to free and included SQL Server troubleshooting tools
- To deliver against both of these goals using real-world examples and anecdotes to help SQL Server professionals efficiently and accurately determine the root cause of issues on systems running SQL Server
WHO THIS BOOK IS FOR
This book is intended for readers who regard themselves as, or who aspire to be, SQL Server professionals in predominantly relational environments. What we mean by SQL Server professionals are those who consider SQL Server to be one of their core product skills and who continually strive to increase their knowledge of the product and how to use it.
Because it is not a beginner’s book, we assume that readers know the basics about installing, configuring, and using SQL Server, and are aware of some of the challenges presented by troubleshooting SQL Server problems using only the native tools provided. However, for readers who are not entirely confident about some of the topics presented, every effort has been made to present enough foundational information to get started.
The book is presented in two parts. The first part covers internals, which provides an in-depth grounding in core concepts and therefore the knowledge necessary to understand the output and positioning of the tools covered in the second part of the book. Readers who are confident with the subject matter presented in Part I will find that they can start reading from Part II, dipping back into Part I as required to clarify any understanding.
WHAT THIS BOOK COVERS
Before launching into a description of the book’s structure and the contents of each chapter, the following sections describe the key drivers and assumptions that originally dictated which topics the book needed to cover.
Understanding Internals
You don’t really need to understand a lot about how SQL Server works to be successful in many SQL Server–based job roles. You can find numerous well-established, prescriptive guidelines and a very active and helpful community. Eventually, however, you will reach a point when that’s just not enough (usually when something serious has gone wrong).
During an unexpected service outage, for example, you need to make quick decisions in order to balance the demands of restoring the service as quickly as possible while gathering enough data to help you diagnose the issue so you can prevent it from happening again. In that situation you cannot depend on external help or goodwill; it won’t arrive fast enough to help you. Understanding SQL Server internals enables you to make quick and effective decisions for resolving problems independently.
Several years ago, a Microsoft customer encountered corruption in a large business-critical database running on SQL Server. The business decided to take the database offline until it was fixed because it held financial trade data, and mistakes would have been disastrous.
They ran DBCC CHECKDB, which can be used in SQL Server to help detect and resolve corruption, but killed it after eight hours in favor of a database restore. The backup was corrupt, however, so they had no option but to run CHECKDB again, which fixed the problem after another 12 hours. This time-consuming disaster ultimately forced the company to pay a large fine for failing to provide a service to the financial markets.
The simple lessons to learn from this example are to test your backups and to know how long CHECKDB takes to run (and to understand that it takes longer when corruption is detected, as it takes another pass with deeper checks). These are best practices that can be followed with little understanding of actual internals.
The main reason for including this example, however, is the information that resulted from the postmortem. The original error message that detected the problem contained details about a corrupt page. Armed with a data page number, the troubleshooting team could have used DBCC PAGE to look at the header and determine to which database object it belonged. In this case it belonged to a nonclustered index that could have been rebuilt without having to take the entire database down to run CHECKDB or restore the entire database. This is why it’s useful to know the “internals”; so you can work things out for yourself and take the best course of action.
This book covers internals’ information for Windows and SQL Server that will help you understand the environment in which your application(s) work; configure your server to optimize for different requirements; and avoid making blind decisions in the heat of the moment because you don’t know why you’re seeing a particular behavior.
Troubleshooting Tools and Lessons from the Field
The second part of this book deals with a range of free troubleshooting tools that you can use together to form a structured, effective troubleshooting strategy. Because the tools can seem overly complicated and difficult to learn to the uninitiated, these chapters form a comprehensive and practical guide that can make your life much easier — both on a daily basis and when faced with a serious problem.
This part of the book also gives you the advantage of looking at topics that are based on real-world experiences, offering concrete examples rather than boilerplate “how it works” advice — this approach received a lot of positive feedback for the previous version of this book, covering SQL Server 2008, so we have retained and expanded on that idea.
HOW THIS BOOK IS STRUCTURED
The first part of the book starts with a high-level overview of SQL Server’s architecture, leading into chapters on the core resources that are important to SQL Server, including hardware, memory, and storage. After these chapters you’ll find material that is critical to understand for effective troubleshooting: query processing and execution, locking and concurrency, latches and spinlocks, and Tempdb.
Part II begins with both a human-oriented and process-driven look at how to approach troubleshooting. Then it jumps into the tools and technologies that work well independently but are brought together into one easy solution for analysis with SQL Nexus.
The next chapters get you started with Extended Events and the new user interface introduced with SQL Server 2012, easing you gently into using PowerShell for troubleshooting, building your own health checks with dynamic management views (DMVs), and meeting the challenges of managing large SQL Server estates.
Finally, you will learn about what server virtualization means for SQL Server and how to identify good candidates to move into a virtual environment.
The following overview of each chapter puts them into context within the book so you can decide where to start reading.
Chapter 1: SQL Server Architecture
This chapter takes you lightly through the life cycle of a query, with enough depth to help you understand fundamental concepts and architectures without getting lost in the complexities of individual components (some of which are looked at closely in later chapters). This chapter will appeal to readers at all levels of skill, whether you’re a developer, a DBA, or a seasoned SQL Server veteran.
Chapter 2: Demystifying Hardware
Having modern, appropriately sized and selected hardware and storage is the absolute foundation of good database server performance and scalability. Unfortunately, many database professionals struggle to keep up with the latest developments in server hardware and I/O subsystems, often relying on someone else (who may not be familiar with SQL Server) to select their hardware and I/O subsystems. This can result in inappropriately sized and matched hardware that does not perform well for different types of SQL Server workloads. This chapter describes the tools and techniques you need to make intelligent decisions regarding database server hardware and sizing.
Chapter 3: Understanding Memory
Memory is an important aspect of troubleshooting SQL Server because problems here can cause problems everywhere else. An understanding of memory is one of the first areas you should master if you want to differentiate yourself as a SQL Server professional.
This chapter looks at fundamental memory concepts common to any application running on Windows, how SQL Server interacts with memory through Windows, and how SQL Server manages memory internally.
Chapter 4: Storage Systems
Understanding I/O has always been a key part of a DBA’s role. However, separation of duties is now very common in many environments and the responsibility for delivering both I/O performance and high availability has become the domain of the SAN administrator. Over time this has led to a frustrating disconnect between these two groups, with each using a separate language to describe its respective requirements and solutions.
However, there is also a “battle royale” currently in play in the storage world. Direct-attached storage (DAS) is regaining popularity — primarily through the advent of solid-state devices (SSDs). SSDs have breathed fresh life into the storage market, delivering exponential performance improvements while significantly reducing device count, energy costs, and data center floor space.
DAS solutions are interesting because they also return the power (and responsibility) back to the owner of the server — and for database platforms that means the DBA. SQL Server 2012 offers some very compelling application-centric availability options that will again give users the opportunity to evaluate DAS as their platform of choice.
This chapter helps readers understand the needs of both the SAN administrator and the DBA. It explores the design options facing a SAN administrator and some of the trade-offs required when provisioning storage in the enterprise. You will see the benefits that a SAN can provide and the functionality and features that are typically available, enabling you to bridge the terminology gap between these two parties. The chapter concludes with a review of the information a SAN administrator requires from a DBA in order to make the right decisions when provisioning storage.
Chapter 5: Query Processing and Execution
Query processing within SQL Server involves many components, and in this chapter you’ll learn about the query optimization framework and how statistics and costing are used to find a good way to execute your code. In this chapter you’ll also discover how to read execution plans, as well as explore code optimization techniques that give SQL Server a better chance of creating a good plan.
Chapter 6: Locking and Concurrency
Transactions are the life source of an RDBMS. A database that cannot handle thousands of transactions per second is quickly derided by the community. However, good throughput is worthless without the assurance of data integrity. High-end database platforms such as SQL Server have very sophisticated mechanisms for not only delivering great throughput but also managing integrity of the data, thereby delivering predictable results.
This chapter demonstrates that a database system is only as good as its transactions. Because a poorly designed or heavy transaction can seriously affect the performance of your SQL Server, this chapter provides a thorough grounding in SQL Server’s mechanisms for managing data integrity through the use of transactions, locking architecture, and enhanced performance by leveraging optimistic concurrency models.
Chapter 7: Latches and Spinlocks
As data volumes continue to rise, DBAs are faced with larger and more demanding systems. Today’s workloads can place tremendous strain on the internals of SQL Server — especially in its default configuration. One of these internal areas that can feel this strain is latches. In a perfect world, a DBA would never need to worry about latches. They exist only to ensure the integrity of the data in memory. However, all too often database resources are left waiting for a latch resource, thereby slowing the whole system down.
This chapter guides you through the fundamentals of latch architecture, explains how to troubleshoot a latch contention issue, and finishes with best practice guidance to minimize and mitigate any risk going forward.
Chapter 8: Knowing Tempdb
Tempdb is used by applications to store temporary objects and by SQL Server to store temporary result sets used internally to process queries. There is only one tempdb for an instance of SQL Server, and its importance has grown significantly since SQL Server 2005 introduced new features such as online indexing and snapshot isolation levels that use tempdb heavily.
In this chapter you’ll find out which features use tempdb and what the performance implications can be for enabling them, as well as how to monitor and tune the database for best performance and availability.
Chapter 9: Troubleshooting Methodology and Practices
This chapter provides a framework for effectively troubleshooting complex problems. It includes content describing how to identify SQL Server problems, when to use the tools discussed in this book, and diagnosis for complex issues. This chapter outlines an approach to tackling SQL Server problems using real-world examples and offering guidance that will enable you to promptly focus on the root cause of a problem.
Chapter 10: Viewing Server Performance with PerfMon and the PAL Tool
Performance Monitor has a been a staple data gathering and reporting tool since Windows NT4, but it has continued to increase in terms of size and scope since those early days.
In this chapter you will learn how to optimize your data collection using Performance Monitor to reduce the impact on the monitored system, and how to load the data straight into SQL Server to run your own T-SQL queries against the results. It also introduces you to the Performance Analysis of Logs tool (PAL), which greatly simplifies the analysis of large data captures.
Chapter 11: Consolidating Data Capture with SQLdiag
SQLdiag, first introduced in SQL Server 2005, is a great tool that helps to coordinate the collection of Performance Monitor logs and SQL traces, as well as gather other system data.
In this chapter you’ll learn how to configure, customize, and run SQLdiag, as well as be introduced to the Performance Statistics script from Microsoft, which adds locking, blocking, and wait stats to the list of collectors that SQLdiag coordinates. This tool is an important secret of the trade for efficient data collection, and this chapter is a must read for anyone not using it extensively already.
Chapter 12: Bringing It All Together with SQL Nexus
SQL Nexus is a freeware tool written by SQL Server escalation engineers at Microsoft, and it is the crown jewel of the troubleshooting tools because it consolidates the analysis and reporting capabilities of all the other tools mentioned previous to this chapter.
Using the consolidated data collection from the Performance Statistics script, SQL Nexus will load into a database and analyze Performance Monitor log data; SQL trace files using ReadTrace, which is embedded into the tool; locking and blocking information, including blocking chains with the actual statements and execution plan details; as well SQL Server waits data, which is also aggregated.
In this chapter you’ll read about how to configure, run, and draw conclusions from the reports created by this tool, which is by far the most useful piece of software in the troubleshooting kit bag of users who have taken the time to learn it.
Chapter 13: Diagnosing SQL Server 2012 Using Extended Events
This chapter describes the Extended Event architecture and how you can use it to take your troubleshooting capabilities to a higher level. Extended Events provides a low-impact, very flexible, and powerful method for capturing troubleshooting information — one that enables you to gain insight into difficult and intermittent problems that were impossibly hard to diagnose using traditional methods.
Chapter 14: Enhancing Your Troubleshooting Toolset with PowerShell
Administrative activities on the Windows platform have traditionally been carried out within GUI applications, such as SQL Server Management Studio. PowerShell has changed the administrative landscape, especially for activities such as troubleshooting and performance tuning.
This chapter demonstrates how PowerShell integrates with Windows, WMI, the Registry, and the file system, and in particular its deep integration with SQL Server. You will then explore how SQL Server troubleshooting can be performed with PowerShell, focusing on identifying which key resources are being used where, and how PowerShell can help address the issues discovered.
The chapter concludes with some proactive performance tuning scripts that you can use to monitor and tune your SQL Server environment.
Chapter 15: Delivering a SQL Server Health Check
Dynamic management views (DMVs) were first added to SQL Server 2005, and they have been enhanced in every version of SQL Server since then. They provide a wealth of extremely valuable information about the configuration, health, and performance of your SQL Server instance, along with useful metrics about individual user databases.
This chapter covers an extensive set of DMV queries that you can use as a diagnostic tool to assess the health and performance of your SQL Server instances and databases. It also provides valuable background information and specific tips you can use to properly interpret the results of each query.
Chapter 16: Delivering Manageability and Performance
This chapter covers the challenges of managing a SQL Server estate. It considers all aspects of manageability, such as configuration management, performance, capacity planning, and automation. Also covered are features within SQL Server such as Policy-Based Management and multi-server management. The chapter provides real-world advice on the benefits and limitations of the out-of-the-box tools, community solutions such as the Enterprise Policy Management Framework, and monitoring tools such as System Center Operations Manager.
Chapter 17: Running SQL Server in a Virtual Environment
This chapter begins by looking at virtualization concepts and the difference between good and bad contention. It then describes how to identify good candidates for virtualization before architecting successful virtualized database platforms, focusing on memory, storage, CPU, and high availability. The chapter concludes by discussing how to monitor the performance of virtualized systems post-implementation.
WHAT YOU NEED TO USE THIS BOOK
The samples in this book were written and tested on SQL Server 2012 Standard, Enterprise, and Developer Editions. Both the Developer Edition and the Evaluation Edition of SQL Server 2012 are very easy to obtain, and they perform identically to the Enterprise Edition.
The source code for the samples is available for download from the Wrox website at:
www.wrox.com/remtitle.cgi?isbn=1118177657
CONVENTIONS
To help you get the most from the text and keep track of what’s happening, we’ve used a number of conventions throughout the book.
As for styles in the text:
- We highlight new terms and important words when we introduce them.
- We show keyboard strokes like this: Ctrl+A.
- We show filenames, URLs, and code within the text like so: persistence.properties.
- We present code in two different ways:
We use a monofont type with no highlighting for most code examples.We use bold to emphasize code that is particularly important in the present context or to show changes from a previous code snippet.SOURCE CODE
As you work through the examples in this book, you may choose either to type in all the code manually, or to use the source code files that accompany the book. All the source code used in this book is available for download at www.wrox.com. Specifically for this book, the code download is on the Download Code tab at:
www.wrox.com/remtitle.cgi?isbn=1118177657
You can also search for the book at www.wrox.com by ISBN (the ISBN for this book is 978-1-118-17765-5) to find the code. A complete list of code downloads for all current Wrox books is available at www.wrox.com/dynamic/books/download.aspx.
At the beginning of each chapter, we’ve provided information on where to download the major code files for the chapter. Throughout each chapter, you’ll also find references to the names of code files as needed in listing titles and text.
Most of the code on www.wrox.com is compressed in a .ZIP, .RAR archive, or similar archive format appropriate to the platform.
Once you download the code, just decompress it with your favorite compression tool. Alternately, you can go to the main Wrox code download page at www.wrox.com/dynamic/books/download.aspx to see the code available for this book and all other Wrox books.
ERRATA
We make every effort to ensure that there are no errors in the text or the code. However, no one is perfect, and mistakes do occur. If you find an error in one of our books, such as a spelling mistake or a faulty piece of code, we would be very grateful for your feedback. By sending in errata, you may save another reader hours of frustration, and at the same time, you will be helping us provide even higher quality information.
To find the errata page for this book, go to www.wrox.com/remtitle.cgi?isbn=1118177657 and click the Errata link. On this page you can view all errata submitted for this book and posted by Wrox editors.
If you don’t spot “your” error on the Book Errata page, go to www.wrox.com/contact/techsupport.shtml and complete the form there to send us the error you have found. We’ll check the information and, if appropriate, post a message to the book’s errata page and fix the problem in subsequent editions of the book.
P2P.WROX.COM
For author and peer discussion, join the P2P forums at http://p2p.wrox.com. The forums are a web-based system for you to post messages relating to Wrox books and related technologies and interact with other readers and technology users. The forums offer a subscription feature to e-mail you topics of interest of your choosing when new posts are made. Wrox authors, editors, other industry experts, and your fellow readers are present on these forums.
At http://p2p.wrox.com, you will find a number of different forums that will help you, not only as you read this book, but also as you develop your own applications. To join the forums, just follow these steps:
Once you join, you can post new messages and respond to messages other users post. You can read messages at any time on the web. If you would like to have new messages from a particular forum e-mailed to you, click the Subscribe to this Forum icon by the forum name in the forum listing.
For more information about how to use the Wrox P2P, be sure to read the P2P FAQs for answers to questions about how the forum software works, as well as many common questions specific to P2P and Wrox books. To read the FAQs, click the FAQ link on any P2P page.