Benjamin Nevarez Rotating Header Image

High Performance SQL Server: Second Edition


High Performance SQL Server

I am very excited to announce that the second edition of my book, High Performance SQL Server, is now available on Amazon and, in this post, I include the Introduction of the book, which covers what this book is about and describes the content of each of its eleven chapters. In summary, if you are familiar with the first edition of the book, this second edition has more than 200 new pages and includes two new chapters, the first one covering SQL Server on Linux and the second one Intelligent Query Processing. All the other chapters have been updated and expanded, to cover both SQL Server 2017 and SQL Server 2019 new features and enhancements.

I’ve been blogging and presenting about query tuning and optimization for years. I even wrote a couple of books about this topic: Inside the SQL Server Query Optimizer and Microsoft SQL Server 2014 Query Tuning & Optimization. Query tuning and optimization are extremely important for the performance of your databases and applications.

Equally important is having a well-designed and configured system in the first place. SQL Server default configuration can work fine for some applications, but mission-critical and high performance applications demand a thoughtful design and configuration. Well-written and tuned queries will not shine if a system is not properly configured. For example, queries will not use processor resources in an optimal way if a maximum degree of parallelism setting is not configured correctly. Database performance will suffer if a database is using the default file autogrowth settings or if the storage is not properly configured. A misconfigured tempdb database may show contention on many busy systems. Even the query optimizer will struggle with a bad database design or badly written queries. These are just some common configuration problems out there in real production systems.

In addition, even when a well-designed application goes to production, performance tuning does not end there. Monitoring and troubleshooting are an extremely important part of an application and database life cycle since performance problems eventually
will arise. Workloads may change, hopefully for the better (e.g., an application having to deal with an unexpected increase on business transactions). Sometimes, those changes will require a redesign, changes, and perhaps new configurations.

So this is, in fact, an iterative process, consisting of design and configuration, followed by implementation, monitoring, and troubleshooting, which again may lead to new designs or configurations, monitoring, and so on. In addition, collecting performance data, creating a baseline, and performing trend analysis are an important part of a production implementation, not only to troubleshoot problems but also to anticipate issues or understand future growth and additional requirements. It is essential to estimate and trend those changes proactively instead of waking up to a system suddenly having trouble in handling changing workloads or, even worse, to face a downtime that could have been avoided. There are several tools to help with this process, including the Query Store, a tool which was introduced with SQL Server 2016.

I spend a good part of my daily job working on all these items, so I decided to write a book about them. I wanted to cover everything you need to know about performance in SQL Server that does not require you to know about query tuning, work with execution plans, or “fight” the query optimizer. There are so many areas to cover, and more are being added as new features and technologies appear on SQL Server such as In-Memory OLTP, columnstore indexes, and the aforementioned Query Store.

This book covers all currently supported versions of SQL Server with a major focus on SQL Server 2019. Although this is a performance book from the practical point of view, understanding SQL Server internals is very important too. The best way to troubleshoot something is to know how it works and why things happen. Consequently, I focus on database engine internals when required.

Finally, this book complements my query tuning and optimization books. If you are a database developer or a SQL Server professional who cares about query performance, you could benefit from reading both these books as well. If you are a database administrator, a database architect, or a system administrator, and you want to improve the performance of your system without query tuning, you can read only this book.

Since I mentioned that implementing a high performance database server is an iterative process, consisting of design and configuration, followed by implementation, monitoring, and performance troubleshooting, I have decided to separate the chapters
of the book in four parts: SQL Server Internals, Design and Configuration, Monitoring, and Performance Tuning and Troubleshooting. I tried to separate the chapters on these areas, but I am sure some chapters may overlap with more than one of those categories.

As mentioned earlier, understanding SQL Server internals is important to better optimize a system and troubleshoot database problems, so this book starts explaining how the SQL Server database engine works and covers everything happening in the
system from the moment a connection is made to a database until a query is executed and the results are returned to the client. Chapter 1 includes topics such as the Tabular Data Stream (TDS) and network protocols used by SQL Server, SQLOS, and the work
performed by the SQL Server relational engine, focusing on query processing and the most common query operators. A new chapter on this second edition, Chapter 2, covers SQL Server on Linux. Starting with SQL Server 2017, SQL Server is now available on
Linux and can also be run on Docker containers. This chapter covers the SQL Server on Linux architecture and how the database engine works on this operating system.

Part 2, Design and Configuration, includes Chapters 3 and 4. Chapter 3 explains a number of instance-level configuration settings that can greatly impact the performance of your SQL Server implementation. As an interesting fact, it shows how some trace flags originally introduced to solve a particular problem are now implemented as SQL Server configuration defaults.

Chapter 4 covers tempdb configuration, which is especially important as such a database is shared between all the user and system databases on a SQL Server instance. Focus in the chapter is given to tempdb latch contention of allocation pages and tempdb disk spilling, a performance issue that occurs when not enough memory is available for some query processor operations.

Part 3, which focuses on monitoring SQL Server, covers analyzing wait statistics and the Query Store. Waits happen in a SQL Server instance all the time. Chapter 5 introduces the waits performance methodology, which can be used to troubleshoot performance problems, especially when other methods are not able to pinpoint a performance issue.

Chapter 6 covers the Query Store, a very promising query performance feature introduced with SQL Server 2016. The Query Store can help you to collect query and plan information along with their runtime statistics, which you can use to easily identify query performance–related problems and even force an existing execution plan. The chapter closes by mentioning some related new features such as the Live Query Statistics and the SQL Server Management Studio plan comparison tool.

Part 4, Performance Tuning and Troubleshooting, includes the five remaining chapters of the book and covers topics such as in-memory technologies, indexing, intelligent query processing, and disk and storage.

In-memory technologies are introduced in Chapter 7 and include In-Memory OLTP and columnstore indexes. Both features suffered severe limitations with their original releases, so this chapter covers how these technologies work and what their current improvements are. The chapter ends with operational analytics, which combines both technologies to allow analytical queries to be executed in real time in an OLTP system. In-memory technologies promise to be the future in relational database technologies.

Chapter 8 shows how proactively collecting and persisting performance information could be extremely beneficial to understand how a specific system works, to create a baseline, and to understand when performance is deviating from a desirable or expected behavior. The chapter also covers the most critical performance counters, dynamic management objects, and events, along with some of the tools used to display and collect such data. This chapter surely overlaps with Part 3, Monitoring.

Indexing, a required topic for database performance, is covered in Chapter 9, which explains how indexes work and why they are important on both OLTP and Data Warehouse environments. The chapter provides emphasis on using SQL Server tools to help create indexes such as the missing indexes feature and the more sophisticated Database Engine Tuning Advisor.

Another new chapter in this second edition of the book, Chapter 10, provides an overview to intelligent query processing, a collection of features introduced with SQL Server 2017 designed to improve the performance of your queries. The intelligent query processing features provide performance enhancements with no application changes needed and little or no effort required.

Finally, SQL Server storage is explained in Chapter 11. Disk has traditionally been the slowest part of a database system, but newer technologies such as flash-based storage offer great performance improvements and are becoming a de facto enterprise standard
as their cost continues to decline. The chapter also indicates that storage optimization is not only about using the fastest disk possible but also minimizing its usage by implementing the methods covered in several chapters of the book, such as proper indexing or some query-tuning techniques

 

About the author

Benjamin Nevarez Benjamin Nevarez is a database professional based in Los Angeles, California who specializes in SQL Server query tuning and optimization. He is the author of three books, “High Performance SQL Server”, “SQL Server 2014 Query Tuning & Optimization” and “Inside the SQL Server Query Optimizer” and has also coauthored other books including “SQL Server 2012 Internals”. Benjamin has also been a speaker at many SQL Server conferences and events around the world including the PASS Summit, SQL Server Connections and SQLBits. His blog can be found at http://www.benjaminnevarez.com and he can also be reached on twitter at @BenjaminNevarez.

One Comment

  1. Rubén Garrigós says:

    Congrats! Every Benjamin’s book is a must have. Even for experienced SQL Server tuners which usually find nothing new/unknown in most SQL Server tuning books.

Leave a Reply

Your email address will not be published. Required fields are marked *