Book Image

Learn T-SQL Querying - Second Edition

By : Pedro Lopes, Pam Lahoud
Book Image

Learn T-SQL Querying - Second Edition

By: Pedro Lopes, Pam Lahoud

Overview of this book

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.

Related Content you might be interested in

Current Title:

Small book image
Learn T-SQL Querying - Second Edition
Pedro Lopes | Pam Lahoud
Feb, 2024 | 456 pages
Small book image
SQL Server Query Tuning and Optimization
Benjamin Nevarez
Aug, 2022 | 446 pages
Small book image
SQL Query Design Patterns and Best Practices
Steve Hughes | Chi Zhang | Dennis Neer | Dr Ram Babu Singh | Leslie Andrews | Shabbir H Mala
Mar, 2023 | 270 pages
Small book image
SQL Server 2019 Administrator's Guide
Marek Chmel | Vladimir Muzny
Sep, 2020 | 522 pages
Table of Contents (18 chapters)
Preface
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
1
Part 1: Query Processing Fundamentals
Part 1: Query Processing Fundamentals
Free Chapter
2
Chapter 1: Understanding Query Processing
Chapter 1: Understanding Query Processing
Technical requirements
Logical statement processing flow
Query compilation essentials
Query optimization essentials
Query execution essentials
Plan caching and reuse
How query processing impacts plan reuse
The importance of parameters
Summary
3
Chapter 2: Mechanics of the Query Optimizer
Chapter 2: Mechanics of the Query Optimizer
Technical requirements
Introducing the Cardinality Estimator
Understanding the query optimization workflow
Knobs for query optimization
Summary
4
Part 2: Dos and Don’ts of T-SQL
Part 2: Dos and Don’ts of T-SQL
5
Chapter 3: Exploring Query Execution Plans
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
Query plan properties of interest
Summary
6
Chapter 4: Indexing for T-SQL Performance
Chapter 4: Indexing for T-SQL Performance
Technical requirements
Understanding predicate SARGability
Data access using indexes
Indexing strategy using rowstore indexes
Index maintenance
Summary
7
Chapter 5: Writing Elegant T-SQL Queries
Chapter 5: Writing Elegant T-SQL Queries
Technical requirements
Best practices for T-SQL querying
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
8
Chapter 6: Discovering T-SQL Anti- Patterns in Depth
Chapter 6: Discovering T-SQL Anti- Patterns in Depth
Technical requirements
Implicit conversions
Avoiding unnecessary sort operations
Avoiding UDF pitfalls
Avoiding unnecessary overhead with stored procedures
Pitfalls of complex views
Pitfalls of correlated sub-queries
Properly storing intermediate results
Summary
9
Part 3: Assembling Our Query Troubleshooting Toolbox
Part 3: Assembling Our Query Troubleshooting Toolbox
10
Chapter 7: Building Diagnostic Queries Using DMVs and DMFs
Chapter 7: Building Diagnostic Queries Using DMVs and DMFs
Technical requirements
Introducing DMVs
Exploring query execution DMVs
Exploring query plan cache DMVs
Troubleshooting common scenarios with DMV queries
Investigating blocking
Cached query plan issues
Mining XML query plans
Summary
11
Chapter 8: Building XEvent Profiler Traces
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
12
Chapter 9: Comparative Analysis of Query Plans
Chapter 9: Comparative Analysis of Query Plans
Technical requirements
Query plan analyzer
Summary
13
Chapter 10: Tracking Performance History with Query Store
Chapter 10: Tracking Performance History with Query Store
Technical requirements
Introducing the Query Store
Tracking expensive queries
Fixing regressed queries
Features that rely on the Query Store
Summary
14
Chapter 11: Troubleshooting Live Queries
Chapter 11: Troubleshooting Live Queries
Technical requirements
Using Live Query Statistics
Understanding the need for lightweight profiling
Activity Monitor gets new life
Summary
15
Chapter 12: Managing Optimizer Changes
Chapter 12: Managing Optimizer Changes
Technical requirements
Understanding where QTA and CE Feedback are needed
Understanding QTA fundamentals
Exploring the QTA workflow
Summary
16
Index
Index
Why subscribe?
17
Other Books You May Enjoy
Other Books You May Enjoy
Packt is searching for authors like you
Share Your Thoughts
Download a free PDF copy of this book

To get the most out of this book

A basic understanding of using the SQL Database Engine and writing T-SQL queries will help get you started with this book. Some familiarity with SQL Server Management Studio or Azure Data Studio is also helpful for running the sample queries and viewing query execution plans.

Software/hardware covered in the book

Operating system requirements

SQL Server (version 2012 or later) and Azure SQL Database

Windows or Linux

SQL Server Management Studio

Windows

Azure Data Studio

Windows, macOS, or Linux

The examples used in this book are designed for use on SQL Server 2022 and Azure SQL Database, but they should work on any version of SQL Server 2012 or later. The Developer Edition of SQL Server is free for development environments and can be used to run all the code samples. There is also a free tier of Azure SQL Database that you can use for testing at https://aka.ms/freedb.

You will need the AdventureWorks2016_EXT (referred to as AdventureWorks) and AdventureWorksDW2016_EXT (referred to as AdventureWorksDW) sample databases, which can be found on GitHub at https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks.

If you are using the digital version of this book, we advise you to type the code yourself or access the code from the book’s GitHub repository (a link is available in the next section). Doing so will help you avoid any potential errors related to the copying and pasting of code.

Note

This book contains many horizontally long screenshots. These have been captured to provide readers with an overview of the execution plans for various SQL queries. As a result, the text in these images may appear small at 100% zoom. Additionally, you will be able to see these plans in more depth in the output in SQL Server as you code along.

Previous Section
Next Section