Data professionals seeking to excel in Transact-SQL (T-SQL) for Microsoft SQL Server and Azure SQL Database often lack comprehensive resources. This updated second edition of Learn T-SQL Querying focuses on indexing queries and crafting elegant T-SQL code, catering to all data professionals seeking mastery in modern SQL Server versions and Azure SQL Database.
Starting with query processing fundamentals, this book lays a solid foundation for writing performant T-SQL queries. You'll explore the mechanics of the Query Optimizer and Query Execution Plans, learning how to analyze execution plans for insights into current performance and scalability. Through dynamic management views (DMVs) and dynamic management functions (DMFs), you'll build diagnostic queries. This book thoroughly covers indexing for T-SQL performance and provides insights into SQL Server's built-in tools for expedited resolution of query performance and scalability issues. Further, hands-on examples will guide you through implementing features such as avoiding UDF pitfalls, understanding predicate SARGability, Query Store, and Query Tuning Assistant.
By the end of this book, you'll have developed the ability to identify query performance bottlenecks, recognize anti-patterns, and skillfully avoid such pitfalls.
Author(s): Pedro Lopes and Pam Lahoud
Edition: 2
Publisher: Packt Publishing Pvt. Ltd.
Year: 2024
Language: English
Pages: 572
Learn T-SQL Querying
Foreword
Contributors
About the authors
About the reviewer
Preface
Who this book is for
What this book covers
To get the most out of this book
Download the example code files
Conventions used
Get in touch
Share Your Thoughts
Download a free PDF copy of this book
Part 1: Query Processing Fundamentals
1
Understanding Query Processing
Technical requirements
Logical statement processing flow
Query compilation essentials
Query optimization essentials
Query execution essentials
Plan caching and reuse
Stored procedures
Ad hoc plan caching
Parameterization
The sp_executesql procedure
Prepared statements
How query processing impacts plan reuse
The importance of parameters
Security
Performance
Parameter sniffing
To cache or not to cache
Summary
2
Mechanics of the Query Optimizer
Technical requirements
Introducing the Cardinality Estimator
Understanding the query optimization workflow
The Trivial Plan stage
The Exploration stage
The Transaction Processing phase
The Quick Plan phase
The Full Optimization phase
Knobs for query optimization
Summary
Part 2: Dos and Don’ts of T-SQL
3
Exploring Query Execution Plans
Technical requirements
What is a query plan?
Accessing a query plan
Navigating a query plan
Query plan operators of interest
Blocking versus non-blocking operators
Data access operators
Joins
Spools
Sort and aggregation operators
Query plan properties of interest
Plan-level properties
Operator-level properties
Summary
4
Indexing for T-SQL Performance
Technical requirements
Understanding predicate SARGability
Data access using indexes
Structure of a rowstore index
Data access using rowstore indexes
Inserting and updating data in a rowstore index
Indexing strategy using rowstore indexes
Best practices for clustered indexes
Best practices for non-clustered indexes
Index maintenance
Summary
5
Writing Elegant T-SQL Queries
Technical requirements
Best practices for T-SQL querying
Referencing objects
Joining tables
Using NOLOCK
Using cursors
The perils of SELECT *
Functions in our predicate
Deconstructing table-valued functions
Complex expressions
Optimizing OR logic
NULL means unknown
Fuzzy string matching
Inequality logic
EXECUTE versus sp_executesql
Composable logic
Summary
6
Discovering T-SQL Anti- Patterns in Depth
Technical requirements
Implicit conversions
Avoiding unnecessary sort operations
UNION ALL versus UNION
SELECT DISTINCT
Avoiding UDF pitfalls
Avoiding unnecessary overhead with stored procedures
Pitfalls of complex views
Pitfalls of correlated sub-queries
Properly storing intermediate results
Using table variables and temporary tables
Using Common Table Expressions (CTEs)
Summary
Part 3: Assembling Our Query Troubleshooting Toolbox
7
Building Diagnostic Queries Using DMVs and DMFs
Technical requirements
Introducing DMVs
Exploring query execution DMVs
sys.dm_exec_sessions
sys.dm_exec_requests
sys.dm_exec_sql_text
sys.dm_os_waiting_tasks
Exploring query plan cache DMVs
sys.dm_exec_query_stats
sys.dm_exec_procedure_stats
sys.dm_exec_query_plan
sys.dm_exec_cached_plans
Troubleshooting common scenarios with DMV queries
Investigating blocking
Cached query plan issues
Single-use plans (query fingerprints)
Finding resource-intensive queries
Queries with excessive memory grants
Mining XML query plans
Plans with missing indexes
Plans with warnings
Plans with implicit conversions
Plans with lookups
Summary
8
Building XEvent Profiler Traces
Technical requirements
Introducing XEvents
Getting up and running with XEvent Profiler
Remote collection with SQL LogScout
Analyzing traces with RML Utilities
Summary
9
Comparative Analysis of Query Plans
Technical requirements
Query plan analyzer
Summary
10
Tracking Performance History with Query Store
Technical requirements
Introducing the Query Store
Inner workings of the Query Store
Configuring the Query Store
Tracking expensive queries
Fixing regressed queries
Features that rely on the Query Store
Query Store for readable secondary replicas
Query Store hinting
Parameter Sensitive Plan Optimization
Automatic Plan Correction
Degree of parallelism feedback
Optimized plan forcing
Summary
11
Troubleshooting Live Queries
Technical requirements
Using Live Query Statistics
Understanding the need for lightweight profiling
Diagnostics available with Lightweight Profiling
Activity Monitor gets new life
Summary
12
Managing Optimizer Changes
Technical requirements
Understanding where QTA and CE Feedback are needed
Understanding QTA fundamentals
Exploring the QTA workflow
Summary
Index
Why subscribe?
Other Books You May Enjoy
Packt is searching for authors like you
Share Your Thoughts
Download a free PDF copy of this book
