Benjamin Nevarez - High Performance SQL Server: Consistent Response for Mission-Critical Applications

Any source code or other supplementary material referenced by the author in this book is available to readers on GitHub via the books product page, located at www.apress.com/9781484264904 . For more detailed information, please visit http://www.apress.com/source-code .

Ive 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 , 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.