|High Performance SQL Server, 2nd Ed (Apress)|
Page 1 of 3
Author: Benjamin Nevarez
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:
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).
|Last Updated ( Tuesday, 30 March 2021 )|