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 on ascending keys

Statistics on ascending keys presents a cardinality estimation problem that has been present in all versions of SQL Server since version 7. Using trace flags 2389 and 2390 has been the best solution to this problem. Starting with SQL Server 2014, you can also use the new cardinality estimator to obtain the same estimation, but without having to use these trace flags.

But first, let’s explain what the problem is. As we saw earlier, SQL Server builds a histogram on the first column of each statistics object. With statistics on ascending or descending key columns, such as IDENTITY and real-time timestamp columns, newly inserted values usually fall outside the range of values covered by the histogram. In addition, the number of records added might be too small to trigger an automatic update of statistics. Because recently added rows are not covered in the histogram when a significant number of rows should be included, running a query using those values...

Previous Section
End of Section 9
Next Section