Learn T-SQL Querying E-Book

Pedro Lopes

• Herausgeber: Packt Publishing
• Kategorie: Lebensstil
• Sprache: Englisch

Seitenzahl: 529

Veröffentlichungsjahr: 2024

Beschreibung

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

Leseprobe

Learn T-SQL Querying

A guide to developing efficient and elegant T-SQL code

Pedro Lopes and Pam Lahoud

Learn T-SQL Querying

Copyright © 2024 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the authors, nor Packt Publishing or its dealers and distributors, will be held liable for any damages caused or alleged to have been caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

Group Product Manager: Kaustubh Manglurkar

Publishing Product Manager: Heramb Bhavsar

Book Project Manager: Hemangi Lotlikar

Content Development Editor: Joseph Sunil

Technical Editor: Rahul Limbachiya

Copy Editor: Safis Editing

Proofreader: Safis Editing

Indexer: Tejal Daruwale Soni

Production Designer: Prafulla Nikalje

DevRel Marketing Executive: Nivedita Singh

First published: May 2019

Second edition: February 2024

Production reference: 2010324

Published by Packt Publishing Ltd.

Grosvenor House

11 St Paul’s Square

Birmingham

B3 1RB, UK

ISBN 978-1-83763-899-4

www.packtpub.com

To my wife and life partner, Sandra, and to my esteemed friends, mentors, and former colleagues in Azure Data who develop the SQL Database Engine and keep pushing the boundaries of excellence – sorry, I can’t list you all here! To the unique people I had the privilege of working with – Amit Banerjee, Bob Ward, Conor Cunningham, Hanuma Kodavalla, and Slava Oks – for inspiring me to always move forward and do better, and to everyone who keeps developing and supporting applications on this most-scalable RDBMS.

– Pedro Lopes

To Andrew and Linus, for spending countless nights and weekends without me. To the entire #SQLFamily, who continue to inspire me, support me (and each other), and drive me to be better every day. To my computer-illiterate friends, Jodie, Liza, and Erin, who I know will proudly display this book on their shelves despite having no idea what any of this means. And to my mom, who bought me my first computer when I was 8 years old and said “Sure!” when I decided that adding computer science as a second major in my junior year of college seemed like a good idea.

– Pam Lahoud

Foreword

When I first met Pedro Lopes and Pam Lahoud, I already knew that they had both achieved recognition as experts in SQL Server, especially in areas such as query processing and performance. As I started working with them, I quickly realized that not only was the reputation warranted but I also came to see their characteristics of professionalism, thoroughness, and presentation skills.

All these traits come out in this book, and you gain all the benefits. I love how this book is organized. If I want to read the entire book end to end, I will first learn the fundamentals and mechanics of the optimizer from the perspective of writing T-SQL queries. Then, I will get practical advice on how to write effective queries for maximum performance on topics such as indexing. And then, I’m able to dive deep into detailed query troubleshooting techniques using the full capabilities of SQL Server. This organization of the book also allows me to jump to any section aligning with my skills and knowledge. This powerful story is now brought to life in the second edition of this book, bringing in enhancements from SQL Server 2019, SQL Server 2022, and Azure SQL designed to make your applications faster with no code changes.

Even if you believe that you understand query processing with SQL Server, you will benefit from this book. Using visual flows and examples, the first part of the book gives you a great perspective on how queries in SQL Server are compiled, executed, and cached. This part also includes key details of query processing such as cardinality estimation, optimization phases, and methods to control query optimization.

The second part is the crown jewel of the book. Pedro and Pam pour in their years of experience to give you the advice you need on topics such as analyzing query plans, proper indexing, best practices for crafting T-SQL queries, and the often-overlooked area of anti-pattern queries. These chapters are full of rich advice and examples for you to try out yourself.

Finish off the book by learning how to get faster to tune and troubleshoot query performance using powerful tools such as Query Profiling, Query Store, and Extended Events. The power of the T-SQL language comes to life as you learn how to write queries to debug the queries from your application. As readers, you get the benefit of unique information throughout the book because the authors have directly worked on these parts of the product.

SQL Server and Azure SQL have evolved over the years to provide more automation and simplify the requirements to build and manage successful database applications. However, understanding how to use the power of T-SQL is critical to achieving maximum performance and efficiency. Furthermore, to take your game to the next level, you need to understand the nuances and mechanics of the query optimizer and query execution with T-SQL in the engine. This book provides it all in a manner that you can easily understand, with all the latest updates, and in a format that you can use as a reference for years to come.

– Bob Ward

Principal architect, Microsoft

Contributors

About the authors

Pedro Lopes is a senior director of engineering at Salesforce, based in WA, USA, leading the organization responsible for the Marketing Cloud’s DB Management Plane. Previously, he was a principal architect in Microsoft Azure Data, leading the SQL Server 2022 release until its public preview. He has 20+ years of industry experience and was with Microsoft for 12+ years. He has extensive experience with query performance troubleshooting and has been a speaker at numerous conferences, such as SQLBits, PASS Summit, SQLIntersection, Microsoft Ignite, and Microsoft Build. He has written multiple technical articles about SQL that are currently in the product documentation, including several on Engine internals at https://aka.ms/sqlserverguides.

I want to thank the people who have been close to me and supported me throughout, especially my wife, Sandra. And special thanks to my writing partner and good friend Pam Lahoud, who is both a technical powerhouse and an outstanding human being, without whom this project would not have been nearly as rewarding to complete.

Pam Lahoud is a principal PM manager in Microsoft Azure Data, based in Seattle, WA, USA. She has been with Microsoft since 2006 and currently leads the SQL Server in Azure Virtual Machines product manager team. She is passionate about SQL Server performance and has focused on performance tuning and optimization, particularly from the developers’ perspective, throughout her career. She is a SQL 2008 Microsoft Certified Master with over 20 years of experience working with SQL Server and has been a speaker at several global events such as the PASS Summit, SQLBits, Microsoft Build, and Microsoft Ignite.

A big thank you to my partner, Andrew, and our son, Linus, for putting up with countless Sundays at home with Mama locked up in the office. Thank you to my writing partner, Pedro; we are the classic example of better together, and this is a life achievement I never would have reached without you! Forever your work-spouse, no matter which companies we’re at :-)

About the reviewer

Sergey Ten has been working in the database space for over 20 years, primarily on the Microsoft SQL Server and Oracle database servers. His main areas of expertise are data processing, programming language design, and high availability.

Sergey currently works at Microsoft as a principal software engineer, working in the SQL Server team in the areas of query processing and high availability. Prior to that, he worked at Quest Software developing various Oracle management solutions, and at Guidance Software working on computer forensics and eDiscovery products.

Table of Contents

Preface

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

Other Books You May Enjoy

Part 1: Query Processing Fundamentals

To understand how to write solid, performant T-SQL queries, users should know how SQL Server runs the T-SQL syntax to deliver the intended result sets in a scalable fashion. This part introduces you to concepts that are used throughout the remaining parts of the book to explain most patterns and anti-patterns, as well as mitigation strategies.

This part has the following chapters:

Chapter 1, Understanding Query ProcessingChapter 2, Mechanics of the Query Optimizer