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

Statistics

To estimate the cost of an execution plan, the query optimizer needs to know, as precisely as possible, the number of records returned by a given query, and to help with this cardinality estimation, SQL Server uses and maintains optimizer statistics. Statistics contain information describing the distribution of values in one or more columns of a table, and will be explained in greater detail in Chapter 6, Understanding Statistics.

As covered in Chapter 1, An Introduction to Query Tuning and Optimization, you can use the OptimizerStatsUsage plan property to identify the statistics used by the Query Optimizer to produce an execution plan. The OptimizerStatsUsage property is available only starting with SQL Server 2017 and SQL Server 2016 SP2. Alternatively, you can use the undocumented 9292 and 9204 trace flags to show similar information about the statistics loaded during the optimization process. Although this method works with all the supported and some older versions...

Previous Section
End of Section 11
Next Section