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

Summary

The SQL Server query processor typically selects a good execution plan for your queries, but there may still be cases when, even after extensive troubleshooting, you do not get good performance from a selected plan. Query optimization is an inherently complex problem, and despite several decades of query optimization research, challenges in some fundamental areas are still being addressed today.

This chapter provided recommendations on what to do when SQL Server is not giving you a good execution plan. One such methodology, which you can use in cases when the SQL Server query optimizer can’t produce a good plan for complex queries, is to break these queries down into two or more simpler queries while storing the intermediate results in temporary tables.

Although hints can be used to improve the performance of a query in these cases by directly taking control of the execution plan selection, they should always be used with caution, and only as a last resort. You...

Previous Section
End of Section 12
Next Section