Learn T-SQL Querying电子书

About Packt

Why subscribe?

Packt.com

Foreword

Contributors

About the authors

About the reviewers

Packt is searching for authors like you

Preface

Who this book is for

What this book covers

To get the most out of this book

Download the example code files

Download the color images

Conventions used

Get in touch

Reviews

Section 1: Query Processing Fundamentals

Anatomy of a Query

Building blocks of a T-SQL statement

SELECT

DISTINCT

TOP

FROM

INNER JOIN

OUTER JOIN

CROSS JOIN

APPLY

WHERE

ORDER BY

GROUP BY

HAVING

Logical statement processing flow

Summary

Understanding Query Processing

Query compilation essentials

Query optimization essentials

Query execution essentials

Plan caching and reuse

Stored procedures

Ad hoc plan caching

Parameterization

Simple parameterization

Forced 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

Mechanics of the Query Optimizer

Introducing the Cardinality Estimator

Understanding the query optimization workflow

The Trivial Plan stage

The Exploration stage

Transaction Processing

Quick plan

Full optimization

Knobs for query optimization

Summary

Section 2: Dos and Donts of T-SQL

Exploring Query Execution Plans

Accessing a query plan

Navigating a query plan

Query plan operators of interest

Blocking versus non-blocking operators

Data-access operators

Table Scan

Clustered Index Scan

NonClustered Index Scan

NonClustered Index Seek

Clustered Index Seek

Lookups

RID Lookups

Key Lookups

Columnstore Index Scan

Joins

Nested Loops joins

Merge Joins

Hash Match joins

Adaptive Joins

Spools

Sorts and aggregation

Sorts

Stream aggregation

Hash aggregation

Query plan properties of interest

Plan-level properties

Cardinality estimation model version

Degree of Parallelism*

Memory Grant*

MemoryGrantInfo

Optimization Level

OptimizerHardwareDependentProperties

OptimizerStatsUsage

QueryPlanHash

QueryHash

Set options

Statement

TraceFlags

WaitStats

QueryTimeStats*

MissingIndexes

Parameter List

Warnings*

PlanAffectingConvert

WaitForMemoryGrant*

MemoryGrantWarning*

SpatialGuess*

UnmatchedIndexes*

FullUpdateForOnlineIndexBuild

Operator-level properties

RunTimeCountersPerThread

Actual I/O Statistics*

Actual Number of Rows

Actual Time Statistics

Estimated rows

EstimateRowsWithoutRowGoal

Warnings*

Columns With No Statistics*

Spill To TempDb

No Join Predicate

Summary

Writing Elegant T-SQL Queries

Understanding predicate SARGability

Basic index guidelines

Clustered indexes

Non-clustered indexes

INCLUDE columns

Filtered indexes

Unique versus non-unique

Columnstore indexes

Indexing strategy

Data structure considerations

Database usage considerations

Query considerations

Best practices for T-SQL querying

Referencing objects

Joining tables

Using NOLOCK

Using cursors

Summary

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

Implicit conversions

Avoiding unnecessary sort operations

UNION ALL versus UNION

SELECT DISTINCT

SELECT TOP 1 with ORDER BY

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

Summary

Section 3: Assemble Your Query Troubleshooting Toolbox

Building Diagnostic Queries Using DMVs and DMFs

Introducing Dynamic Management Views

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

Summary

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

Query Plan Comparison

Query Plan Analyzer

Summary

Tracking Performance History with Query Store

The Query Store

Inner workings of the Query Store

Configuring the Query Store

Tracking expensive queries

Fixing regressed queries

Summary

Troubleshooting Live Queries

Using Live Query Statistics

Understanding the need for Lightweight Profiling

Diagnostics available with Lightweight Profiling

The query_thread_profile XEvent

The query_plan_profile XEvent

The query_post_execution_plan_profile XEvent

The sys.dm_exec_query_statistics_xml DMF

The sys.dm_exec_query_plan_stats DMF

Activity Monitor gets a new life

Summary

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

Leave a review - let other readers know what you think