Learn T-SQL Querying : A Guide to Developing Efficient and Elegant T-SQL Code / Pedro Lopes and Pam Lahoud.

Format:

Book

Author/Creator:

Lopes, Pedro, author.

Lahoud, Pam, author.

Language:

English

Subjects (All):

SQL server.

SQL (Computer program language).

Client/server computing.

Physical Description:

1 online resource (457 pages)

Edition:

Second edition.

Place of Publication:

Birmingham, England : Packt Publishing Ltd., [2024]

Summary:

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.

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

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

Part 2: Dos and Don'ts of T-SQL

Chapter 3: Exploring Query Execution Plans

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

Chapter 4: Indexing for T-SQL Performance

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

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

Chapter 6: Discovering T-SQL Anti- Patterns in Depth

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)

Part 3: Assembling Our Query Troubleshooting Toolbox

Chapter 7: Building Diagnostic Queries Using DMVs and DMFs

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

Chapter 8: Building XEvent Profiler Traces

Introducing XEvents.

Getting up and running with XEvent Profiler

Remote collection with SQL LogScout

Analyzing traces with RML Utilities

Chapter 9: Comparative Analysis of Query Plans

Query plan analyzer

Chapter 10: Tracking Performance History with Query Store

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

Chapter 11: Troubleshooting Live Queries

Using Live Query Statistics

Understanding the need for lightweight profiling

Diagnostics available with Lightweight Profiling

Activity Monitor gets new life

Chapter 12: Managing Optimizer Changes

Understanding where QTA and CE feedback are needed

Understanding QTA fundamentals

Exploring the QTA workflow

Index

Other Books You May Enjoy.

Notes:

Includes index.

Description based on publisher supplied metadata and other sources.

Description based on print version record.

ISBN:

9781837636839

1837636834

OCLC:

1424938286