Learn T-SQL Querying: A guide to developing efficient and elegant T-SQL code, 2nd Edition

✍ Scribed by Pedro Lopes and Pam Lahoud

• Publisher: Packt Publishing Pvt. Ltd.
• Year: 2024
• Tongue: English
• Leaves: 457
• Edition: 2
• Category: Library

✦ Synopsis

Troubleshoot query performance issues, identify anti-patterns in your code, and write efficient T-SQL queries with this guide for T-SQL developers
Key Features

A definitive guide to mastering the techniques of writing efficient T-SQL code
Learn query optimization fundamentals, query analysis, and how query structure impacts performance
Discover insightful solutions to detect, analyze, and tune query performance issues
Purchase of the print or Kindle book includes a free PDF eBook

Book Description

Data professionals seeking to excel in Transact-SQL for Microsoft SQL Server and Azure SQL Database often lack comprehensive resources. Learn T-SQL Querying second edition focuses on indexing queries and crafting elegant T-SQL code enabling data professionals gain mastery in modern SQL Server versions (2022) and Azure SQL Database. The book covers new topics like logical statement processing flow, data access using indexes, and best practices for tuning T-SQL queries.

Starting with query processing fundamentals, the book lays a foundation for writing performant T-SQL queries. You’ll explore the mechanics of the Query Optimizer and Query Execution Plans, learning to analyze execution plans for insights into current performance and scalability. Using dynamic management views (DMVs) and dynamic management functions (DMFs), you’ll build diagnostic queries. The book covers indexing and delves into SQL Server’s built-in tools to expedite resolution of T-SQL query performance and scalability issues. Hands-on examples will guide you to avoid UDF pitfalls and understand features like 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 avoid pitfalls
What you will learn

Identify opportunities to write well-formed T-SQL statements
Familiarize yourself with the Cardinality Estimator for query optimization
Create efficient indexes for your existing workloads
Implement best practices for T-SQL querying
Explore Query Execution Dynamic Management Views
Utilize the latest performance optimization features in SQL Server 2017, 2019, and 2022
Safeguard query performance during upgrades to newer versions of SQL Server

Who this book is for

This book is for database administrators, database developers, data analysts, data scientists and T-SQL practitioners who want to master the art of writing efficient T-SQL code and troubleshooting query performance issues through practical examples. A basic understanding of T-SQL syntax, writing queries in SQL Server, and using the SQL Server Management Studio tool will be helpful to get started

✦ Table of Contents

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