Book Image

Learn T-SQL Querying

By : Pedro Lopes, Pam Lahoud
Book Image

Learn T-SQL Querying

By: Pedro Lopes, Pam Lahoud

Overview of this book

Transact-SQL (T-SQL) is Microsoft's proprietary extension to the SQL language used with Microsoft SQL Server and Azure SQL Database. This book will be a usefu to learning the art of writing efficient T-SQL code in modern SQL Server versions as well as the Azure SQL Database. The book will get you started with query processing fundamentals to help you write powerful, performant T-SQL queries. You will then focus on query execution plans and leverage them for troubleshooting. In later chapters, you will explain how to identify various T-SQL patterns and anti-patterns. This will help you analyze execution plans to gain insights into current performance, and determine whether or not a query is scalable. You will also build diagnostic queries using dynamic management views (DMVs) and dynamic management functions (DMFs) to address various challenges in T-SQL execution. Next, you will work with the built-in tools of SQL Server to shorten the time taken to address query performance and scalability issues. In the concluding chapters, this will guide you through implementing various features, such as Extended Events, Query Store, and Query Tuning Assistant, using hands-on examples. By the end of the book, you will have developed the skills to determine query performance bottlenecks, avoid pitfalls, and discover the anti-patterns in use.

Related Content you might be interested in

Current Title:

Small book image
Learn T-SQL Querying
Pedro Lopes | Pam Lahoud
May, 2019 | 484 pages
Small book image
SQL Server Query Tuning and Optimization
Benjamin Nevarez
Aug, 2022 | 446 pages
Small book image
SQL Server 2017 Administrator's Guide
Vladimir Muzny | Marek Chmel
Dec, 2017 | 434 pages
Small book image
SQL Server 2019 Administrator's Guide
Vladimir Muzny | Marek Chmel
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
Get in touch
Free Chapter
1
Section 1: Query Processing Fundamentals
Section 1: Query Processing Fundamentals
2
Anatomy of a Query
Anatomy of a Query
Building blocks of a T-SQL statement
Logical statement processing flow
Summary
3
Understanding Query Processing
Understanding Query Processing
Query compilation essentials
Query optimization essentials
Query execution essentials
Plan caching and reuse
The importance of parameters
Summary
4
Mechanics of the Query Optimizer
Mechanics of the Query Optimizer
Introducing the Cardinality Estimator
Understanding the query optimization workflow
Summary
5
Section 2: Dos and Donts of T-SQL
Section 2: Dos and Donts of T-SQL
6
Exploring Query Execution Plans
Exploring Query Execution Plans
Accessing a query plan
Navigating a query plan
Query plan operators of interest
Query plan properties of interest
Summary
7
Writing Elegant T-SQL Queries
Writing Elegant T-SQL Queries
Understanding predicate SARGability
Basic index guidelines
Best practices for T-SQL querying
Summary
8
Easily-Identified T-SQL Anti-Patterns
Easily-Identified T-SQL Anti-Patterns
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
9
Discovering T-SQL Anti-Patterns in Depth
Discovering T-SQL Anti-Patterns in Depth
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
10
Section 3: Assemble Your Query Troubleshooting Toolbox
Section 3: Assemble Your Query Troubleshooting Toolbox
11
Building Diagnostic Queries Using DMVs and DMFs
Building Diagnostic Queries Using DMVs and DMFs
Introducing Dynamic Management Views
Exploring query execution DMVs
Exploring query plan cache DMVs
Troubleshooting common scenarios with DMV queries
Summary
12
Building XEvent Profiler Traces
Building XEvent Profiler Traces
Introducing Extended Events
SQL Server Profiler – deprecated but not forgotten
Getting up and running with XEvent Profiler
Remote collection with PSSDiag and SQLDiag
Analyzing traces with RML Utilities
Summary
13
Comparative Analysis of Query Plans
Comparative Analysis of Query Plans
Query Plan Comparison
Query Plan Analyzer
Summary
14
Tracking Performance History with Query Store
Tracking Performance History with Query Store
The Query Store
Tracking expensive queries
Fixing regressed queries
Summary
15
Troubleshooting Live Queries
Troubleshooting Live Queries
Using Live Query Statistics
Understanding the need for Lightweight Profiling
Activity Monitor gets a new life
Summary
16
Managing Optimizer Changes with the Query Tuning Assistant
Managing Optimizer Changes with the Query Tuning Assistant
Understanding where QTA is needed
Understanding QTA fundamentals
Exploring the QTA workflow
Summary
17
Other Books You May Enjoy
Other Books You May Enjoy
Leave a review - let other readers know what you think

Understanding QTA fundamentals

While guiding us through the recommended process, QTA doesn't follow it exactly. The very last step 5 will not have the same outcome as we saw in the previous section: instead of providing options to revert to a last known good plan, QTA helps to find a new state that is not the pre-CE upgrade or post-CE upgrade plan, but a new plan that will hopefully outperform both of the previous plans.

The following diagram summarizes the recommended steps to minimize risk with CE upgrades using QTA, which replaces the very last step of the process described in the Understanding where QTA is needed section:

How does QTA find a better query plan for regressed queries? Starting with the same data that's available in Query Store's Regressed Queries report, QTA will look for query patterns that may be affected by changes in CE, specifically from...

Previous Section
End of Section 3
Next Section