What this book covers

This book covers how to get the best performance from your queries and how to use this knowledge to create high-performing applications. It shows how a better understanding of what the SQL Server query processor does behind the scenes can help database developers, administrators, and architects to write better queries and to provide the query processor with the information it needs to produce efficient execution plans. In the same way, this book shows how you can use your newfound knowledge of the query processor’s inner workings and SQL Server tools to troubleshoot cases when your queries are not performing as expected.

Chapter 1, An Introduction to Query Tuning and Optimization, starts with an overview of the architecture of the SQL Server relational database engine and then continues by looking in great detail at how to use execution plans, the primary tool we will use to interact with the SQL Server query processor.

Chapter 2, Troubleshooting Queries, continues from Chapter 1 and provides you with additional tuning tools and techniques, such as SQL trace, extended events, and DMVs, to find out how your queries are using system resources or to root out performance-related problems. The chapter ends with an introduction to the Data Collector, a feature introduced with SQL Server 2008.

Chapters 3 and 4 go deep into the internals of the query optimizer and the query processor operators. Chapter 3, The Query Optimizer, explains how the query optimizer works and shows why this knowledge can give you a great background to troubleshoot, optimize, and better tune your application queries. Chapter 4, The Execution Engine, tells you about the most used query operators employed in the execution plans created by the query optimizer.

After two chapters talking about the architecture and internals of the query processor, Chapter 5, Working with Indexes, puts your feet back on the ground by covering indexes. Indexing is one of the most important techniques used in query tuning and optimization, and one that can dramatically improve the performance of your queries and databases.

Statistics is another essential topic required for query tuning and optimization and troubleshooting. Statistics are used by the query optimizer to make decisions toward producing an efficient execution plan, and this information is also available to you so you can use it to troubleshoot cardinality estimation problems. Statistics are covered in Chapter 6, Understanding Statistics.

In-memory OLTP, originally also known as Hekaton, was the most important feature introduced with SQL Server 2014, and Chapter 7, In-Memory OLTP, covers how this new technology can help you to develop high-performance applications. In-memory OLTP is, in fact, a new database engine whose main features include optimized tables and indexes for main memory data access, stored procedures compiled to native code, and the elimination of locks and latches.

Query optimization is a relatively expensive operation, so if plans can be cached and reused, this optimization cost can be avoided. How plan caching works and why it is extremely important for the performance of your queries and SQL Server in general are covered in Chapter 8, Understanding Plan Caching.

Chapter 9, The Query Store, showcases the Query Store, a feature introduced with SQL Server 2016, which allows you to troubleshoot queries and execution plan-related issues and monitor performance history. The query store collects information about queries, plans, and runtime statistics, helping you pinpoint performance differences by changes in execution plans.

Intelligent query processing is a family of features whose purpose is to improve the performance of existing queries with no application changes or minimal implementation effort. Introduced with SQL Server 2017, it had more features added with each new release, including six in SQL Server 2022. This family of features is covered in Chapter 10, Intelligent Query Processing.

Chapter 11, An Introduction to Data Warehouses, is, as its name suggests, an introduction to data warehouses, and explains how the SQL Server Query Optimizer can identify fact and dimension tables and can optimize star join queries. The chapter also covers columnstore indexes, a feature introduced with SQL Server 2012, which is based on columnar storage and new batch processing algorithms that can improve the performance of star join queries by several orders of magnitude.

The last chapter of the book, Chapter 12, Understanding Query Hints, discusses the challenges the SQL Server processor still faces today after more than four decades of query optimization research. Recommendations and workarounds for complex queries for which the query optimizer may not be able to provide efficient plans are provided. Finally, hints, which must be used with caution and only as a last resort when no other option is available, are introduced as a way to take explicit control over the execution plan for a given query.