Preface
This book covers query tuning and optimization in SQL Server and provides you with the tools and knowledge necessary to get peak performance from your queries and applications. We mostly relate query optimization with the work performed by the query optimizer in which an efficient execution plan is produced for a query. However, sometimes we may not be happy with the query execution performance and may try to improve it by performing additional changes—or what we call query tuning. But even more important, we need to understand that the results we originally get from the query optimizer will greatly depend on all the information we feed it—for example, our database design, the defined indexes, and even some database and server configuration settings.
There are many ways in which we can impact the work performed by the query processor, which is why it is extremely important to understand how we can help this SQL Server component do a superior job. Providing quality information to the query processor will most likely result in high-quality execution plans, which will also improve the performance of your databases. But no query processor is perfect, and it is important to understand the reasons why sometimes we may not, in fact, get an efficient execution plan or good query performance and to know what possible solutions are still available.
Finally, this book covers all the supported versions of SQL Server and has been updated up to SQL Server 2022.
Who this book is for
This book is for SQL Server developers who are struggling with slow query execution, database administrators who are tasked with troubleshooting slow application performance, and database architects who design SQL Server databases in support of line-of-business and data warehousing applications.
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.
Download the example code files
We also have other code bundles from our rich catalog of books and videos available at https://github.com/PacktPublishing/. Check them out!
Download the color images
We also provide a PDF file that has color images of the screenshots and diagrams used in this book. You can download it here: https://packt.link/06NGV.
Conventions used
There are a number of text conventions used throughout this book.
Code in text: Indicates code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles. Here is an example: “Then, it updates any outdated statistics, except in cases where the AUTO_UPDATE_STATISTICS_ASYNC configuration option is used.”
A block of code is set as follows:
SELECT map_key, map_value FROM sys.dm_xe_map_values
WHERE name = 'statement_recompile_cause'
When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:
CREATE OR ALTER PROCEDURE test (@pid int)
AS
SELECT * FROM Sales.SalesOrderDetail
WHERE ProductID = @pid
Bold: Indicates a new term, an important word, or words that you see onscreen. For instance, words in menus or dialog boxes appear in bold. Here is an example: “You can find the following entry close to the end of the XML plan (or the Parameter List property in a graphical plan).”
Tips or Important Notes
Appear like this.
Get in touch
Feedback from our readers is always welcome.
General feedback: If you have questions about any aspect of this book, email us at [email protected] and mention the book title in the subject of your message.
Errata: Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you have found a mistake in this book, we would be grateful if you would report this to us. Please visit www.packtpub.com/support/errata and fill in the form.
Piracy: If you come across any illegal copies of our works in any form on the internet, we would be grateful if you would provide us with the location address or website name. Please contact us at [email protected] with a link to the material.
If you are interested in becoming an author: If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, please visit authors.packtpub.com.
Share Your Thoughts
Once you’ve read SQL Server Query Tuning and Optimization, we’d love to hear your thoughts! Please click here to go straight to the Amazon review page for this book and share your feedback.
Your review is important to us and the tech community and will help us make sure we’re delivering excellent quality content.