Learn T-SQL Querying: A guide to developing efficient and elegant T-SQL code

✍ Scribed by Pedro Lopes, Pam Lahoud

• Publisher: Packt Publishing
• 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

1. Understanding Query Processing
2. Mechanics of the Query Optimizer
3. Exploring Query Execution Plans
4. Indexing for T-SQL Performance
5. Writing Elegant T-SQL Queries
6. Discovering T-SQL Anti-patterns in Depth
7. Building diagnostic queries using DMVs and DMFs
8. Building XEvent profiler traces
9. Comparative analysis of Query Plans
10. Tracking performance history with Query Store
11. Troubleshooting Live Queries
12. Managing optimizer changes with the Query Tuning Assistant

✦ Table of Contents

Cover
Title Page
Copyright and Credits
Foreword
Contributors
Table of Contents
Preface
Part 1: Query Processing Fundamentals
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 9: Comparative Analysis of Query Plans
Technical requirements
Query plan analyzer
Summary
Chapter 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
Chapter 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
Chapter 12: Managing Optimizer Changes
Technical requirements
Understanding where QTA and CE feedback are needed
Understanding QTA fundamentals
Exploring the QTA workflow
Summary
Index
Other Books You May Enjoy