Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Book Overview & Buying SQL Server Query Tuning and Optimization
  • Table Of Contents Toc
SQL Server Query Tuning and Optimization

SQL Server Query Tuning and Optimization

By : Benjamin Nevarez
4.9 (13)
Buy this Book
close
close
SQL Server Query Tuning and Optimization

SQL Server Query Tuning and Optimization

4.9 (13)
By: Benjamin Nevarez
Buy this Book

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.
Table of Contents (14 chapters)
close
close
close
Preface
Icon
Preface
Icon
Who this book is for
Icon
What this book covers
Icon
Download the example code files
Icon
Download the color images
Icon
Conventions used
Icon
Get in touch
Icon
Share Your Thoughts
1
Chapter 1: An Introduction to Query Tuning and Optimization
Icon
Chapter 1: An Introduction to Query Tuning and Optimization
Icon
Query Processor Architecture
Icon
Analyzing execution plans
Icon
Getting plans from a trace or the plan cache
Icon
SET STATISTICS TIME and IO statements
Icon
Summary
Lock Free Chapter
2
Chapter 2: Troubleshooting Queries
Icon
Chapter 2: Troubleshooting Queries
Icon
DMVs and DMFs
Icon
SQL Trace
Icon
Extended events
Icon
The Data Collector
Icon
Summary
3
Chapter 3: The Query Optimizer
Icon
Chapter 3: The Query Optimizer
Icon
Query optimization research
Icon
Introduction to query processing
Icon
The sys.dm_exec_query_optimizer_info DMV
Icon
Parsing and binding
Icon
Simplification
Icon
Trivial plan optimization
Icon
Joins
Icon
Transformation rules
Icon
The Memo
Icon
Statistics
Icon
Full optimization
Icon
Summary
4
Chapter 4: The Execution Engine
Icon
Chapter 4: The Execution Engine
Icon
Data access operators
Icon
Aggregations
Icon
Joins
Icon
Parallelism
Icon
Updates
Icon
Summary
5
Chapter 5: Working with Indexes
Icon
Chapter 5: Working with Indexes
Icon
Introduction to indexes
Icon
Creating indexes
Icon
Understanding index operations
Icon
The Database Engine Tuning Advisor
Icon
Missing indexes
Icon
Index fragmentation
Icon
Unused indexes
Icon
Summary
6
Chapter 6: Understanding Statistics
Icon
Chapter 6: Understanding Statistics
Icon
Exploring statistics
Icon
Histograms
Icon
A new cardinality estimator
Icon
Cardinality estimation errors
Icon
Incremental statistics
Icon
Statistics on computed columns
Icon
Filtered statistics
Icon
Statistics on ascending keys
Icon
UPDATE STATISTICS with ROWCOUNT and PAGECOUNT
Icon
Statistics maintenance
Icon
Cost estimation
Icon
Summary
7
Chapter 7: In-Memory OLTP
Icon
Chapter 7: In-Memory OLTP
Icon
In-memory OLTP architecture
Icon
Tables and indexes
Icon
Natively compiled stored procedures
Icon
Limitations and later enhancements
Icon
Summary
8
Chapter 8: Understanding Plan Caching
chevron up
Icon
Chapter 8: Understanding Plan Caching
Icon
Batch compilation and recompilation
Icon
Exploring the plan cache
Icon
Understanding parameterization
Icon
Parameter sniffing
Icon
Parameter-sensitive plan optimization
Icon
Summary
9
Chapter 9: The Query Store
Icon
Chapter 9: The Query Store
Icon
Using the Query Store
Icon
Querying the Query Store
Icon
Summary
10
Chapter 10: Intelligent Query Processing
Icon
Chapter 10: Intelligent Query Processing
Icon
Overview of intelligent query processing
Icon
Memory grant feedback
Icon
Cardinality estimation feedback
Icon
Degree of parallelism feedback
Icon
Interleaved execution
Icon
Table variable deferred compilation
Icon
Adaptive joins
Icon
Summary
11
Chapter 11: An Introduction to Data Warehouses
Icon
Chapter 11: An Introduction to Data Warehouses
Icon
Data warehouses
Icon
Star join query optimization
Icon
Columnstore indexes
Icon
Summary
12
Chapter 12: Understanding Query Hints
Icon
Chapter 12: Understanding Query Hints
Icon
Breaking down complex queries
Icon
Hints
Icon
Joins
Icon
Aggregations
Icon
FORCE ORDER
Icon
The INDEX, FORCESCAN, and FORCESEEK hints
Icon
FAST N
Icon
The NOEXPAND and EXPAND VIEWS hints
Icon
Plan guides
Icon
USE PLAN
Icon
Summary
Icon
Why subscribe?
13
Other Books You May Enjoy
Icon
Other Books You May Enjoy
Icon
Packt is searching for authors like you
Icon
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...
CONTINUE READING
83
Tech Concepts
36
Programming languages
73
Tech Tools
Icon Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.
Icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Icon 50+ new titles added per month and exclusive early access to books as they are being written.
SQL Server Query Tuning and Optimization
End of Section 8
Next Section
notes
bookmark Notes and Bookmarks search Search in title playlist Add to playlist font-size Font size

Change the font size

margin-width Margin width

Change margin width

day-mode Day/Sepia/Night Modes

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Confirmation

Modal Close icon
claim successful
Order Page Read Now

Buy this book with your credits?

Modal Close icon
Are you sure you want to buy this book with one of your credits?
Close
YES, BUY

Submit Your Feedback

Modal Close icon
Modal Close icon
Modal Close icon