SQL Server 2022 Query performance tuning : troubleshoot and optimize Query performance / Grant Fritchey.
Fritchey, Grant.
2022
QA76.9.D3
Available Online

Linked e-resources

Linked Resource
Online Access
Concurrent users
Unlimited
Authorized users
Authorized users
Document Delivery Supplied
Can lend chapters, not whole ebooks

Details

Title
SQL Server 2022 Query performance tuning : troubleshoot and optimize Query performance / Grant Fritchey.
Author
Fritchey, Grant.
Edition
6th ed.
ISBN
9781484288917 (electronic bk.)
1484288912 (electronic bk.)
9781484288900
1484288904
DOI
https://doi.org/10.1007/978-1-4842-8891-7
Publication Details
New York : Apress, 2022.
Language
English
Description
1 online resource (xxv, 720 pages) : illustrations
Item Number
10.1007/978-1-4842-8891-7 doi
Call Number
QA76.9.D3
Dewey Decimal Classification
005.75/85
Summary
Troubleshoot slow-performing queries and make them run faster. Database administrators and SQL developers are constantly under pressure to provide more speed. This new edition has been redesigned and rewritten from scratch based on the last 15 years of learning, knowledge, and experience accumulated by the author. The book Includes expanded information on using extended events, automatic execution plan correction, and other advanced features now available in SQL Server. These modern features are covered while still providing the necessary fundamentals to better understand how statistics and indexes affect query performance. The book gives you knowledge and tools to help you identify poorly performing queries and understand the possible causes of that poor performance. The book also provides mechanisms for resolving the issues identified, whether on-premises, in containers, or on cloud platform providers. You'll learn about key fundamentals, such as statistics, data distribution, cardinality, and parameter sniffing. You'll learn to analyze and design your indexes and your queries using best practices that ward off performance problems before they occur. You'll also learn to use important modern features, such as Query Store to manage and control execution plans, the automated performance tuning feature set, and memory-optimized OLTP tables and procedures. You will be able to troubleshoot in a systematic way. Query tuning doesn't have to be difficult. This book helps you to make it much easier. What You Will Learn Use Query Store to understand and easily change query performance Recognize and eliminate bottlenecks leading to slow performance Tune queries whether on-premises, in containers, or on cloud platform providers Implement best practices in T-SQL to minimize performance risk Design in the performance that you need through careful query and index design Understand how built-in, automatic tuning can assist your performance enhancement efforts Protect query performance during upgrades to the newer versions of SQL Server Who This Book Is For Developers and database administrators with responsibility for query performance in SQL Server environments, and anyone responsible for writing or creating T-SQL queries and in need of insight into bottlenecks (including how to identify them, understand them, and eliminate them).
Note
Description based upon print version of record.
Includes index.
Comparing Performance with Missing Statistics
Access Note
Access limited to authorized users.
Available in Other Form
SQL Server 2022 Query Performance Tuning
Linked Resources
Online Access
Record Appears in
Online Resources > Ebooks
All Resources
Intro
Table of Contents
About the Author
About the Technical Reviewer
Acknowledgments
Introduction
Chapter 1: Query Performance Tuning
The Query Performance Tuning Process
Performance Issues
A Repetitive Process
Understanding What Defines "Good Enough"
Establishing Comparison Points
Most Likely Performance Issues
Common Performance Issues
Insufficient or Poor Indexes
Inaccurate or Missing Statistics
Bad T-SQL
Problematic Execution Plans
Excessive Blocking
Deadlocks
Non-Set-Based Operations
Incorrect Database Design

Poor Execution Plan Reuse
Frequent Recompilation of Queries
Summary
Chapter 2: Execution Plan Generation and the Query Optimizer
The Query Optimization Process
Optimization Preparation
Parsing
Binding
Optimization
Simplification
Trivial Plan Match
Optimization Phases
Generating Parallel Execution Plans
Execution Plan Caching
Aging of the Execution Plan
Summary
Chapter 3: Methods for Capturing Query Performance Metrics
Methods to Capture Query Performance Metrics
Include Client Statistics
Connection Properties
SET STATISTICS TIME/IO

QueryTimeStats in the Execution Plan
Trace Events (Profiler)
Dynamic Management Views
Actively Executing Queries
Previously Executed Queries
Query Store
Extended Events
Creating an Extended Events Session
Adding and Configuring Events
Adding Global Fields to Events
Using Predicates with Events
Optional Event Fields
Defining Targets
Using the event_file Target
Using the histogram Target
Working with Sessions
Adding Causality Tracking
Scripting Extended Events
Live Data Explorer Window
Filtering Live Data
Aggregating Live Data

General Recommendations for Using Extended Events
Set Max File Size Appropriately
Avoid Debug Events
Avoid the Use of No_Event_Loss
Summary
Chapter 4: Analyzing Query Behavior Using Execution Plans
Estimated vs. Actual Execution Plans
Capturing Execution Plans
SQL Server Management Studio
Dynamic Management Views
Query Store
Extended Events
What Is Inside an Execution Plan
Reading an Execution Plan
What Do You Look for in an Execution Plan?
First Operator
Warnings
Most Costly Operations
Fat Pipes
Extra Operators
Scans

Estimate vs. Actual
After the Guideposts
Tools That Assist You with Execution Plans
SQL Server Management Studio
Find Node
Compare Plans
Live Query Statistics
Third Party
Solar Winds Plan Explorer
Supratimas
Paste The Plan
Summary
Chapter 5: Statistics, Data Distribution, and Cardinality
Statistics in the Query Optimization Process
Statistics on Rowstore Indexed Columns
Benefits of Updated Statistics
Drawbacks of Outdated Statistics
Statistics on Nonindexed Columns
Benefits of Statistics on a Nonindexed Column

Browse Subjects

Show more subjects...

Statistics

from
to
Export