Book Image

SQL Server Query Tuning and Optimization

By : Benjamin Nevarez
Book Image

SQL Server Query Tuning and Optimization

By: Benjamin Nevarez

Overview of this book

SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications. This book starts by describing the inner workings of the query optimizer, and will enable you to use this knowledge to write better queries and provide the query engine with all the information it needs to produce efficient execution plans. As you progress, you’ll get practical query optimization tips for troubleshooting underperforming queries. The book will also guide you through intelligent query processing and what is new in SQL Server 2022. Query performance topics such as the Query Store, In-Memory OLTP and columnstore indexes are covered as well. By the end of this book, you’ll be able to get the best possible performance for your queries and applications.

Related Content you might be interested in

Current Title:

Small book image
SQL Server Query Tuning and Optimization
Benjamin Nevarez
Aug, 2022 | 446 pages
Small book image
Learn T-SQL Querying
Pam Lahoud | Pedro Lopes
Feb, 2024 | 456 pages
Small book image
Learn T-SQL Querying
Pam Lahoud | Pedro Lopes
May, 2019 | 484 pages
Small book image
SQL Server 2019 Administrator's Guide
Marek Chmel | Vladimir Muzny
Sep, 2020 | 522 pages
Table of Contents (14 chapters)
Preface
Preface
Who this book is for
What this book covers
Download the example code files
Download the color images
Conventions used
Get in touch
Share Your Thoughts
1
Chapter 1: An Introduction to Query Tuning and Optimization
Chapter 1: An Introduction to Query Tuning and Optimization
Query Processor Architecture
Analyzing execution plans
Getting plans from a trace or the plan cache
SET STATISTICS TIME and IO statements
Summary
Free Chapter
2
Chapter 2: Troubleshooting Queries
Chapter 2: Troubleshooting Queries
DMVs and DMFs
SQL Trace
Extended events
The Data Collector
Summary
3
Chapter 3: The Query Optimizer
Chapter 3: The Query Optimizer
Query optimization research
Introduction to query processing
The sys.dm_exec_query_optimizer_info DMV
Parsing and binding
Simplification
Trivial plan optimization
Joins
Transformation rules
The Memo
Statistics
Full optimization
Summary
4
Chapter 4: The Execution Engine
Chapter 4: The Execution Engine
Data access operators
Aggregations
Joins
Parallelism
Updates
Summary
5
Chapter 5: Working with Indexes
Chapter 5: Working with Indexes
Introduction to indexes
Creating indexes
Understanding index operations
The Database Engine Tuning Advisor
Missing indexes
Index fragmentation
Unused indexes
Summary
6
Chapter 6: Understanding Statistics
Chapter 6: Understanding Statistics
Exploring statistics
Histograms
A new cardinality estimator
Cardinality estimation errors
Incremental statistics
Statistics on computed columns
Filtered statistics
Statistics on ascending keys
UPDATE STATISTICS with ROWCOUNT and PAGECOUNT
Statistics maintenance
Cost estimation
Summary
7
Chapter 7: In-Memory OLTP
Chapter 7: In-Memory OLTP
In-memory OLTP architecture
Tables and indexes
Natively compiled stored procedures
Limitations and later enhancements
Summary
8
Chapter 8: Understanding Plan Caching
Chapter 8: Understanding Plan Caching
Batch compilation and recompilation
Exploring the plan cache
Understanding parameterization
Parameter sniffing
Parameter-sensitive plan optimization
Summary
9
Chapter 9: The Query Store
Chapter 9: The Query Store
Using the Query Store
Querying the Query Store
Summary
10
Chapter 10: Intelligent Query Processing
Chapter 10: Intelligent Query Processing
Overview of intelligent query processing
Memory grant feedback
Cardinality estimation feedback
Degree of parallelism feedback
Interleaved execution
Table variable deferred compilation
Adaptive joins
Summary
11
Chapter 11: An Introduction to Data Warehouses
Chapter 11: An Introduction to Data Warehouses
Data warehouses
Star join query optimization
Columnstore indexes
Summary
12
Chapter 12: Understanding Query Hints
Chapter 12: Understanding Query Hints
Breaking down complex queries
Hints
Joins
Aggregations
FORCE ORDER
The INDEX, FORCESCAN, and FORCESEEK hints
FAST N
The NOEXPAND and EXPAND VIEWS hints
Plan guides
USE PLAN
Summary
Why subscribe?
13
Other Books You May Enjoy
Other Books You May Enjoy
Packt is searching for authors like you
Share Your Thoughts

Parameter-sensitive plan optimization

One of the most exciting features of SQL Server 2022 is, without a doubt, parameter-sensitive plan optimization, which is part of the intelligent query processing family of features. Parameter-sensitive query optimization intends to help with performance problems related to parameter-sensitive queries. So, in this section, we will cover how it works and how to use it.

Let’s explore and learn how the technology works; we will explain the details along the way. If you have followed all the examples in this book so far, after many changes, perhaps this could be a good moment to restore a fresh copy of AdventureWorks2019. You may notice that the database has a compatibility level of 140 or SQL Server 2017 (even when the database was given a 2019 name). As mentioned previously, this is a new SQL Server feature and it is only available under database compatibility level 160, so you need to run the following statement:

ALTER DATABASE AdventureWorks2019...
Previous Section
End of Section 6
Next Section