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

Analyzing traces with RML Utilities

Replay Markup Language Utilities, or RML Utilities as it's more commonly known, is a suite of tools that can be used to analyze and replay SQL Server workloads. We first introduced the RML Utilities in Chapter 7, Discovering T-SQL Anti-Patterns in Depth in the Avoiding unnecessary overhead with stored procedures section where we used the ostress tool to simulate a multi-threaded workload on the server. The input to ostress can be a single query or T-SQL script, but ostress can also take a prepared trace file (either SQL Trace or XEvents) as input. This allows you to capture a workload from a production server, and then replay that workload on a test server so that you can experiment with various settings or performance tuning options, or even to test how a new version of SQL Server would perform with the same workload.

The Database Experimentation...
Previous Section
End of Section 6
Next Section