Author: Dmitri Korotkevitch
Publisher: O'Reilly
Pages: 497
ISBN: 978-1098101923
Print:1098101928
Kindle: B0B197NYD7
Audience: DBAs & database devs
Rating: 5
Reviewer: Ian Stirk

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: 

• tempdb configuration – details are provided on the optimal number of tempdb data files

• Initial File Initialization – enabling this allows faster data file growth

• Trace flags – some common ones that improve performance are outlined 

Some changes to Server Options are discussed, including:  

• Optimize for Adhoc Workloads – enable this to save plan cache memory

• Max server memory – how much of the server’s memory is given to the instance 

• Parallelism – Maximum Degree of Parallelism (MAXDOP) and Cost Threshold for Parallelism, the defaults are often not optimal, recommendations are provided 

Following on, we look at recommended database settings, including: 

• autoshrink – best to leave disabled and manage file space manually

• page verify – set to CHECKSUM

• compatibility level – best use latest level but test for regression 

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:

• pending – waiting for worker, often quick, else doing too much

• running – executing on scheduler

• runnable – waiting for scheduler

• suspended – waiting on external event or resource (waiting for page from disk)

• spinloop – processing a spinlock, often very quick

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:

• sys.dm_os_wait_stats - the waits for the instance
• sys.dm_exec_session_wait_stats - from SQL Server 2016, similar to sys.dm_os_wait_stats but for a given session
• sys.dm_os_waiting_tasks - shows tasks waiting on suspended queue, especially useful when system overloaded or has blocking
• sys.dm_exec_requests - show’s what SQL code is running now

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.