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

Chapter 2: Troubleshooting Queries

In Chapter 1, An Introduction to Query Tuning and Optimization, we introduced you to reading execution plans as the primary tool we’ll use to interact with the SQL Server query processor. We also checked out the SET STATISTICS TIME and SET STATISTICS IO statements, which can provide you with additional performance information about your queries. In this chapter, we continue where we left off in the previous chapter. We will look into additional tuning tools and techniques you can use to find out how many server resources your queries are using and how to find the most expensive queries on your system.

Dynamic management views (DMVs) were introduced with SQL Server 2005 as a great tool to diagnose problems, tune performance, and monitor the health of a SQL Server instance. There are many DMVs available, and the first section in this chapter focuses on sys.dm_exec_requests, sys.dm_exec_sessions, and sys.dm_exec_query_stats, which you can use to find out the server resources, such as CPU and I/O, that are used by queries running on the system. Many more DMVs will be introduced in other chapters of this book, including later in this chapter, when we cover extended events.

Although SQL Trace has been deprecated as of SQL Server 2012, it’s still widely used and will be available in some of the next versions of SQL Server. SQL Trace is usually related to SQL Server Profiler because using this tool is the easiest way to define and run a trace, and it is also the tool of choice for scripting and creating a server trace, which is used in some scenarios where running Profiler directly may be expensive. In this chapter, we’ll cover some of the trace events we would be more interested in when tracing query execution for performance problems.

Following on from the same concept as SQL Trace, in the next section, we will explore extended events. All the basic concepts and definitions will be explained first, including events, predicates, actions, targets, and sessions. Then, we will create some sessions to obtain performance information about queries. Because most SQL Server professionals are already familiar with SQL Trace or Profiler, we will also learn how to map the old trace events to the new extended events.

Finally, the Data Collector, a feature introduced with SQL Server 2008, will be shown as a tool that can help you proactively collect performance data that can be used to troubleshoot performance problems when they occur.

In this chapter, we will cover the following topics:

  • DMVs and DMFs
  • SQL Trace
  • Extended events
  • The Data Collector
Previous Section
End of Section 1
Next Section