|SQL Server Advanced Troubleshooting and Performance Tuning (O'Reilly)|
Page 1 of 5
Author: Dmitri Korotkevitch
This book aims to improve the performance of your SQL Servers, how does it fare?
The book discusses methods and tools, that can be used to identify and fix common SQL Server problems, using a practical problem/solution approach. It’s targeted at various database professionals, but primarily DBAs and database developers. Although the book is largely concerned with SQL Server 2022, 2019, and the cloud, much is applicable to older versions, from SQL Server 2005 onwards.
Below is a chapter-by-chapter exploration of the topics covered.
Chapter 1. SQL Server Setup and Configuration
The book opens with a brief discussion on the need for a holistic approach to identifying and fixing problems, because sometimes the cause of a problem may not be what it initially seems e.g. Input/Output (IO) may be high but this could be because the data is getting flushed from the data buffer pool – since there is insufficient memory.
First, we look at hardware and the operating system (OS). It’s noted that sometimes the hardware may not be able to handle a workload, and an upgrade is needed. There’s a useful discussion of the major components (CPU, Memory, Disk subsystem, Network, OS, virtualization and clouds). In each case, potential problems and solutions are highlighted. There’s an emphasis on getting the latest versions of components were possible, since these typically offer improved performance (e.g. later CPUs often give 10-15% better performance even at the same clock speed). The author suggests adding more memory may be the cheapest way to fix some problems (e.g. inefficient queries) since this can reduce IO – often a limiting factor on many SQL Servers.
Next, we look at configuring SQL Server. Whilst many of the default settings are adequate, some changes are recommended, including:
Some changes to Server Options are discussed, including:
Following on, we look at recommended database settings, including:
Helpful code is provided to analyze the SQL Server Error Log, it provides details of messages around any error messages. I just wish the author had commented the code. Brief details are provided on consolidating instances and databases, this often provides a cost saving, but you need to be aware of any performance impact. The author highlights a problem with monitoring and troubleshooting, this itself can lead to performance problems, especially on systems that are already under pressure.
This chapter introduces many topics that are expanded on in subsequent chapters. In many ways, reading this book is like overhearing a conversation with a wise experienced DBA, with skills gained across many years of battling to make improvements. The book is replete with tips, for example the author notes that going from SQL Server 2012 to 2016 often improves performance by 20-40% without any additional changes. Similarly, the book is packed with very useful template code that is sure to prove useful in tracking down your own performance problems.
Setting the tone for the book, the chapter is well written with a good flow between the topics, with useful discussions, diagrams, code, and links to other chapters, and web-links for further information. Each chapter ends with a short summary, and a VERY useful and instructive troubleshooting checklist. These traits apply to the whole book.
Chapter 2. SQL Server Execution Model and Wait Statistics
When SQL Server executes SQL code, there’s a very useful side-effect that can help determine the main issues with the instance. Code is typically executing on the CPU else it is waiting (e.g. waiting for IO to complete), SQL Server records the types of wait and their duration. Analyzing these waits (called wait stats) to understand the main concerns with the instance is a well-known troubleshooting method.
The chapter explains how SQL queries are executed, and how the various types of waiting can be used to investigate problems with the instance. The chapter opens with a look at the high-level architecture of SQL Server’s major components, including: protocol layer, query processor, storage engine, and the in-memory engine. There’s a useful diagram showing how the components interact to execute a query.
There’s a helpful discussion on how the SQL Operating System (SQLOS) is involved in scheduling, monitoring, exceptions, resource usage etc. The various statuses of the execution of the SQL code are described, these can include:
The Dynamic Management View (DMV) sys.dm_os_wait_stats can be queried to determine the waits on the instance. The author notes these should be cross-checked with other tools (e.g. Performance Monitor, Extended Events [XEs]) where possible – remember the initial symptoms may mask the real problem. Some useful code is provided to decipher these waits (and filter out innocuous wait types).
Next, there’s a look at some other DMVs associated with executing queries, useful code is provided for each. These include:
The chapter ends with a look at the Resource Governor. This feature allows different users or groups to have their resources limited e.g. allow application users to use more CPU than report users. These resources include CPU, memory, IO and MAXDOP.
This chapter might initially seem to be a little removed from performance tuning, however, an understanding of the underlying execution model clarifies the wait stats troubleshooting method. Some very useful code is provided to investigate the underlying concerns of the instance, and what’s currently running on the instance.
|Last Updated ( Wednesday, 24 August 2022 )|