Sign In Start Free Trial

Learn T-SQL Querying

By : Pedro Lopes, Lahoud

4.2 (9)

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.

Preface

Preface

Who this book is for

What this book covers

To get the most out of this book

Get in touch

Free Chapter

Section 1: Query Processing Fundamentals

Section 1: Query Processing Fundamentals

Anatomy of a Query

Anatomy of a Query

Building blocks of a T-SQL statement

Logical statement processing flow

Summary

Understanding Query Processing

Understanding Query Processing

Query compilation essentials

Query optimization essentials

Query execution essentials

Plan caching and reuse

The importance of parameters

Summary

Mechanics of the Query Optimizer

Mechanics of the Query Optimizer

Introducing the Cardinality Estimator

Understanding the query optimization workflow

Summary

Section 2: Dos and Donts of T-SQL

Section 2: Dos and Donts of T-SQL

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

Writing Elegant T-SQL Queries

Writing Elegant T-SQL Queries

Understanding predicate SARGability

Basic index guidelines

Best practices for T-SQL querying

Summary

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

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

Section 3: Assemble Your Query Troubleshooting Toolbox

Section 3: Assemble Your Query Troubleshooting Toolbox

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

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

Comparative Analysis of Query Plans

Comparative Analysis of Query Plans

Query Plan Comparison

Query Plan Analyzer

Summary

Tracking Performance History with Query Store

Tracking Performance History with Query Store

The Query Store

Tracking expensive queries

Fixing regressed queries

Summary

Troubleshooting Live Queries

Troubleshooting Live Queries

Using Live Query Statistics

Understanding the need for Lightweight Profiling

Activity Monitor gets a new life

Summary

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

Other Books You May Enjoy

Other Books You May Enjoy

Leave a review - let other readers know what you think

Customer Reviews

4.2 (9)

5 star

55.6%

4 star

33.3%

3 star

0

2 star

0

1 star

11.1%

Managing Optimizer Changes with the Query Tuning Assistant

In this chapter, we will discuss a recently introduced feature: the Query Tuning Assistant (QTA). QTA aims to address some of the most common causes of cardinality estimation related performance regressions that may affect our T-SQL queries after an upgrade from an older version of SQL Server to a newer version, namely SQL Server 2016 and above.

At the time this book is being written, SQL Server 2008 and 2008 R2 are months away from completing their 10-year life cycle and reaching the end of their support; SQL Server 2014 reaches end of mainstream support at the same time; and SQL Server 2012 is no longer in mainstream support. This can raise concerns for all those still running applications supported by these legacy SQL Server versions.

But modernizing the database platform, another word for upgrading in this context...

Search

Your notes and bookmarks