Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Advanced Search Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Learn T-SQL Querying
  • Toc
  • feedback
Learn T-SQL Querying

Learn T-SQL Querying

By : Pedro Lopes, Lahoud
4.2 (9)
close
Learn T-SQL Querying

Learn T-SQL Querying

4.2 (9)
By: Pedro Lopes, 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.
Table of Contents (18 chapters)
close
close
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
chevron up
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

Section 2: Dos and Donts of T-SQL

This section serves as an introduction to query execution plans and how to leverage them for query troubleshooting, basic guidelines for writing efficient queries, and common T-SQL query patterns and anti-patterns.

The following chapters are included in this section:

  • Chapter 4, Exploring Query Execution Plans
  • Chapter 5, Writing Elegant T-SQL Query
  • Chapter 6, Easily Identified T-SQL Anti-Patterns
  • Chapter 7, Discovering T-SQL Anti-Patterns in Depth
End of Chapter 5
Next Chapter
bookmark search playlist download
font-size

Change the font size

margin-width

Change margin width

day-mode

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Delete Bookmark

Modal Close icon
Are you sure you want to delete it?
Cancel
Yes, Delete