Author: Benjamin Nevarez
Publisher: Apress
Pages: 420
ISBN: 978-1484264904
Print: 1484264908
Kindle: B08TQR3NMF
Audience: SQL Server DBAs and Devs
Rating: 4.5
Reviewer: Ian Stirk

This book aims to improve the performance of your SQL Server, how does it fare?

Benjamin Nevarez concentrates on improving the performance of your SQL server largely from a configuration perspective, rather than looking at how to improve specific queries. The premise being that if you improve your system settings, your queries should run faster.

The book is aimed primarily at database administrators, architects, and system administrators. It assumes you already have a good understanding of SQL Server. 

Although the book primarily focuses on SQL Server 2019, much of it is applicable to earlier versions of SQL Server.

Below is a chapter-by-chapter exploration of the topics covered.

Chapter 1​ How SQL Server Works 

Understanding how SQL Server works internally, is the key to making configuration changes to improve performance. While the default configuration settings may be acceptable for many systems, to get the most out of SQL Server, certain settings may need changing.

The book opens with a look at how a connection is made (using Tabular Data Stream [TDS], and a network protocol). Next, we look at how work is undertaken, using SQLOS (manages operating system resources), schedulers (manage thread scheduling), and workers (running the SQL). Next, the higher-level session, connection, and request Dynamic Management Views [DMVs] are briefly discussed.

Aspect of query optimization are then examined, with a look at parsing and binding (syntax and object checking), query optimization (criteria for selecting the ‘best’ query plan), and plan caching (stored to facilitate plan reuse). Some common operators (these perform the actual work, e.g. get a row) are then briefly discussed, including: data access, aggregations, and joins.

The chapter then takes a brief look at memory grants, here queries need be allocated memory to run, and sometimes the memory estimation process goes awry, resulting in poor performance. The chapter ends with a brief look at locks and latches, these control object access (and consequently impact throughput/concurrency).

This chapter looks at the underlying work SQL Server does from the moment a connection is made, until the query results are returned. This chapter forms the basis for the rest of the book.

The chapter is generally easy to read, but it is not a book for beginners, for example it assumes you’re familiar with DMVs, and the more you already know about SQL Server the easier  it is to understand. It contains useful links for further information, helpful diagrams, inter-chapter references, and useful code snippets. Plenty of detail and tips are given, often in passing. A useful chapter summary is given. These traits apply throughout  the book.

Chapter 2​ SQL Server on Linux 

This chapter outlines the background of how SQL Server came to run on Linux.

Ironically, the early version of ‘SQL Server’, as Sybase, ran under Linux. When Microsoft took ownership, it became a Windows database.

Fortuitously, since SQL Server has different demands from many other applications (e.g. for memory), the SQL team created the SQLOS (SQL OS) layer, giving a degree of abstraction between SQL Server and the Windows operating system. This would later be built upon.

Project Helsinki considered porting SQL Server to Linux, however its complexity together with SQL Server’s on-going enhancements proved too cumbersome. Next, project Drawbridge looked at virtualization, handling various windows calls and provided a degree of abstraction. 

Combining SQLOS and Project Drawbridge provided much of what was needed to create a Platform Abstraction Layer (PAL), enabling the same SQL Server code to run on Windows and Linux (i.e. no porting of code), using SQLPAL.

This chapter provided an interesting history lesson in how SQL Server came to run under Linux. It has helpful descriptions of approaches that didn’t work and things that did, and how luckily the SQLOS could be reused. There’s a helpful diagram of the SQL Server running on Linux architecture.

Chapter 3​ SQL Server Configuration 

This chapter covers a miscellany of SQL Server settings that can improve the performance of your queries. The areas examined include:  

• Statistics Update – enables the optimizer to create better execution plans

• tempdb Configuration – improves concurrency

• Max Degree of Parallelism – improves query performance

• Cost Threshold for Parallelism - improves query performance

• Instant File Initialization – faster file growth  

• Optimize for Ad Hoc Workloads – reduces cached plan bloat 

• Memory Configuration – how much memory to give to SQL Server

• Lock Pages in Memory – prevents swapping to disk

• Query Governor Cost Limit - allows resources to be shared fairly  

In each case, the configuration setting is described, together with reasons why you might want to change it. There’s a useful recommendation to use Ola Hallengren’s scripts for various maintenance tasks.

The chapter continues with a section on additional configuring settings that apply only to SQL Server running on Linux. Examples of configuring settings using the mssql-conf utility are given, together with a helpful table of configuration settings and their meanings. Configuration in Linux is also possible using Environmental Variables, and a useful table of these is provided. The Linux section ends with a look at performance best practices, including: kernel settings and avoiding memory problems.

This chapter discusses a wide range of configuration settings that can improve performance. The great thing is that optimising these settings can typically improve the performance of all query running on the server instance/database. Helpfully the author suggests the configuration settings should be based ultimately on the testing of your own query workload (i.e. a query or batch of queries).